Epicor 9 Crystal vs SSRS reports

Some observations on SQL database schema... this is based on a test
install of 9.05 with the Epicor demonstration database on a SQL 2008
server and me just poking around, trying to figure out what's possible
with reports - I'm not a expert here.



I didn't see any primary or foreign keys defined on the SQL tables I
looked at. I assume they're hiding in that Progress schema database.



When creating a simple data set in the SSRS report builder (that Vic
linked to earlier - thanks) with the Part and PartBin tables it didn't
know how to join the tables. Defaulted to a cross join. It did warn
about the missing relationship, and let me modify the select statement
to add it. Okay for somebody comfortable with SQL, but not easy if
you're not.



In comparison, a BAQ knew the relationship and built it for me. I
actually copied the where clause from the BAQ into the JOIN ON... part
of the SQL select statement above



Brian.



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Wednesday, October 20, 2010 11:49 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Epicor 9 Crystal vs SSRS reports





The MSSQL database in Vantage 8 does not have *any* PKs defined in the
schema, much less table relationships. Everything is defined in the
Progress schema holder db.

In Vantage BAQs you can use the Filter Tables option to show you,
based on a selected table, which tables have relationships defined in
the Vantage metadata (which I assume are stored in the z* set of tables
(zrelation, zlookupfield, etc.) Similarly when you add a related table
to a BAQ, the table relations are pre-populated from the metadata.

I would expect something similar in the SSRS environment to enable
rapid query building. Otherwise you'd have to know a heck of lot about
how the database is structured to get anywhere.

-bws

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of vicdrecchio
Sent: Wednesday, October 20, 2010 11:26 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: Epicor 9 Crystal vs SSRS reports

Brian, not sure. In the examples I've seen, they have all been on a SQL
platform. SQL identifies PK's and I do know, like Crystal, there is
some small degree of built-in linking intelligence.

But even with 8.03.xxx versions when you're making BAQs, the only
helpful linking you have are the Linked Tables.

For the ad-hoc end-user reports I believe it is more "wizard" driven and
you can make certain tables candidates for choosing so not to overwhelm
the users with 1050 tables to choose from.

I sat through a few demos @ Perspectives. For those of us currently on
SQL 2005 you can download SSRS tools for free from MS. I didn't know
that. Although, they're not as robust in 2005.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Brian W. Spolarich " <bspolarich@...>
wrote:
>
> So does SSRS with E9 come with the data models, etc. that know about
> table relationships, FKs, etc. so that building reports gives you the
> 'natural' relationships inherent in the data?
>



[Non-text portions of this message have been removed]
We are currently on Vantage 6.1 and planning an upgrade to 9.05. One of
our issues is the large number of reports in 6 we've customized, either
modifying the provided Crystal reports or creating new SSRS reports
(against a copy of the data in SQL server). I notice in the 9
documentation (Install Guide appendix E) that using SSRS reports is an
option, but am unclear on the overall picture. Can somebody either help
me with the questions below, or point me to some general documentation?
We'll be using SQL server 2008 and installing Epicor to use a SQL
database.



- If I install the SSRS reports, do they replace the standard
Crystal reports, or are these additional reports?

- Are the SSRS reports integrated into the 9 menus & screens,
or are they run separately via an internet browser (like 6)?

- Can the SSRS reports be modified? Do they pull data directly
from tables, or from a temporary file the way the Crystal reports do?
How hard is it to add additional fields (eg from a linked table) onto
the report?







Thanks,



Brian Roberts

Business Systems Analyst

Ground Effects Ltd





[Non-text portions of this message have been removed]
Brian, SSRS rocks. I'm on 8.03.x and have been using ASP to deliver my reports to the users for years. Crystal Reports is wonderful for the very, very complex reports.



In E9 you can still use both; SSRS and Crystal Reports.



Once you get a taste of SSRS you're going to want to migrate your reports that way. 2008 SQL comes with a license for a SSRS dev tool similar to VB but very simple to use.



To answer your questions:



- If I install the SSRS reports, do they replace the standard
Crystal reports, or are these additional reports?

---------Additional reports. I believe it's Epicor's goal to eventually dump Crystal Reports completely. Why continue to support a competitor and pay licensing to SAP for Crystal Reports when SQL2008 comes with it for free. For the Progress customers out there, you can still use SSRS. I'm not sure how they'll handle that moving forward, though.





- Are the SSRS reports integrated into the 9 menus & screens,
or are they run separately via an internet browser (like 6)?

-------- All of the above. You can launch into a browser or you have the ability in E9 to incorporate into a Vantage screen. The GREAT thing about using SSRS is that it circumvents Epicor's licensing. Meaning, you can set up an entire intranet with links to various dashboards and reports without the need to install the E9 client on that PC. Great for execs and people who just want to see data. You're not burning an E9 client license nor paying annual maintenance on it.





- Can the SSRS reports be modified? Do they pull data directly
from tables, or from a temporary file the way the Crystal reports do?
How hard is it to add additional fields (eg from a linked table) onto
the report?

--------- Absolutely modifiable. They pull directly from tables or custom views that you can set up. Adding additional fields is simpler than it is in Crystal Reports today.





The other GREAT thing about SSRS are the ad-hoc reports for your users. Even our tech illiterate users could potentially assemble a simple report in a matter of minutes. This is always something that is a selling point but I think in the "real world" IT will always be the report developers with the exception of a few power users.



Here's some good reading:



http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx

http://alexduggleby.com/2008/07/18/sql-server-reporting-services-2008-tutorial-in-5-minutes-or-how-to-send-my-boss-a-report-from-a-view-quickly/

http://www.mssqltips.com/tip.asp?tip=1690







Vic

--- In vantage@yahoogroups.com, "Brian Roberts" <broberts@...> wrote:
>
> We are currently on Vantage 6.1 and planning an upgrade to 9.05. One of
> our issues is the large number of reports in 6 we've customized, either
> modifying the provided Crystal reports or creating new SSRS reports
> (against a copy of the data in SQL server). I notice in the 9
> documentation (Install Guide appendix E) that using SSRS reports is an
> option, but am unclear on the overall picture. Can somebody either help
> me with the questions below, or point me to some general documentation?
> We'll be using SQL server 2008 and installing Epicor to use a SQL
> database.
>
>
>
> - If I install the SSRS reports, do they replace the standard
> Crystal reports, or are these additional reports?
>
> - Are the SSRS reports integrated into the 9 menus & screens,
> or are they run separately via an internet browser (like 6)?
>
> - Can the SSRS reports be modified? Do they pull data directly
> from tables, or from a temporary file the way the Crystal reports do?
> How hard is it to add additional fields (eg from a linked table) onto
> the report?
>
>
>
>
>
>
>
> Thanks,
>
>
>
> Brian Roberts
>
> Business Systems Analyst
>
> Ground Effects Ltd
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
So does SSRS with E9 come with the data models, etc. that know about
table relationships, FKs, etc. so that building reports gives you the
'natural' relationships inherent in the data?

-bws

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of vicdrecchio
Sent: Wednesday, October 20, 2010 10:40 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Epicor 9 Crystal vs SSRS reports

Brian, SSRS rocks. I'm on 8.03.x and have been using ASP to deliver my
reports to the users for years. Crystal Reports is wonderful for the
very, very complex reports.



In E9 you can still use both; SSRS and Crystal Reports.



Once you get a taste of SSRS you're going to want to migrate your
reports that way. 2008 SQL comes with a license for a SSRS dev tool
similar to VB but very simple to use.



To answer your questions:



- If I install the SSRS reports, do they replace the standard
Crystal reports, or are these additional reports?

---------Additional reports. I believe it's Epicor's goal to eventually
dump Crystal Reports completely. Why continue to support a competitor
and pay licensing to SAP for Crystal Reports when SQL2008 comes with it
for free. For the Progress customers out there, you can still use SSRS.
I'm not sure how they'll handle that moving forward, though.





- Are the SSRS reports integrated into the 9 menus & screens,
or are they run separately via an internet browser (like 6)?

-------- All of the above. You can launch into a browser or you have
the ability in E9 to incorporate into a Vantage screen. The GREAT thing
about using SSRS is that it circumvents Epicor's licensing. Meaning,
you can set up an entire intranet with links to various dashboards and
reports without the need to install the E9 client on that PC. Great for
execs and people who just want to see data. You're not burning an E9
client license nor paying annual maintenance on it.





- Can the SSRS reports be modified? Do they pull data directly
from tables, or from a temporary file the way the Crystal reports do?
How hard is it to add additional fields (eg from a linked table) onto
the report?

--------- Absolutely modifiable. They pull directly from tables or
custom views that you can set up. Adding additional fields is simpler
than it is in Crystal Reports today.





The other GREAT thing about SSRS are the ad-hoc reports for your users.
Even our tech illiterate users could potentially assemble a simple
report in a matter of minutes. This is always something that is a
selling point but I think in the "real world" IT will always be the
report developers with the exception of a few power users.



Here's some good reading:



http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx

http://alexduggleby.com/2008/07/18/sql-server-reporting-services-2008-tu
torial-in-5-minutes-or-how-to-send-my-boss-a-report-from-a-view-quickly/

http://www.mssqltips.com/tip.asp?tip=1690







Vic

--- In vantage@yahoogroups.com, "Brian Roberts" <broberts@...> wrote:
>
> We are currently on Vantage 6.1 and planning an upgrade to 9.05. One
of
> our issues is the large number of reports in 6 we've customized,
either
> modifying the provided Crystal reports or creating new SSRS reports
> (against a copy of the data in SQL server). I notice in the 9
> documentation (Install Guide appendix E) that using SSRS reports is an
> option, but am unclear on the overall picture. Can somebody either
help
> me with the questions below, or point me to some general
documentation?
> We'll be using SQL server 2008 and installing Epicor to use a SQL
> database.
>
>
>
> - If I install the SSRS reports, do they replace the standard
> Crystal reports, or are these additional reports?
>
> - Are the SSRS reports integrated into the 9 menus & screens,
> or are they run separately via an internet browser (like 6)?
>
> - Can the SSRS reports be modified? Do they pull data
directly
> from tables, or from a temporary file the way the Crystal reports do?
> How hard is it to add additional fields (eg from a linked table) onto
> the report?
>
>
>
>
>
>
>
> Thanks,
>
>
>
> Brian Roberts
>
> Business Systems Analyst
>
> Ground Effects Ltd
>
>
>
>
>
> [Non-text portions of this message have been removed]
>




------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
Brian, not sure. In the examples I've seen, they have all been on a SQL platform. SQL identifies PK's and I do know, like Crystal, there is some small degree of built-in linking intelligence.

But even with 8.03.xxx versions when you're making BAQs, the only helpful linking you have are the Linked Tables.

For the ad-hoc end-user reports I believe it is more "wizard" driven and you can make certain tables candidates for choosing so not to overwhelm the users with 1050 tables to choose from.

I sat through a few demos @ Perspectives. For those of us currently on SQL 2005 you can download SSRS tools for free from MS. I didn't know that. Although, they're not as robust in 2005.




--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...> wrote:
>
> So does SSRS with E9 come with the data models, etc. that know about
> table relationships, FKs, etc. so that building reports gives you the
> 'natural' relationships inherent in the data?
>
> -bws
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of vicdrecchio
> Sent: Wednesday, October 20, 2010 10:40 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Epicor 9 Crystal vs SSRS reports
>
> Brian, SSRS rocks. I'm on 8.03.x and have been using ASP to deliver my
> reports to the users for years. Crystal Reports is wonderful for the
> very, very complex reports.
>
>
>
> In E9 you can still use both; SSRS and Crystal Reports.
>
>
>
> Once you get a taste of SSRS you're going to want to migrate your
> reports that way. 2008 SQL comes with a license for a SSRS dev tool
> similar to VB but very simple to use.
>
>
>
> To answer your questions:
>
>
>
> - If I install the SSRS reports, do they replace the standard
> Crystal reports, or are these additional reports?
>
> ---------Additional reports. I believe it's Epicor's goal to eventually
> dump Crystal Reports completely. Why continue to support a competitor
> and pay licensing to SAP for Crystal Reports when SQL2008 comes with it
> for free. For the Progress customers out there, you can still use SSRS.
> I'm not sure how they'll handle that moving forward, though.
>
>
>
>
>
> - Are the SSRS reports integrated into the 9 menus & screens,
> or are they run separately via an internet browser (like 6)?
>
> -------- All of the above. You can launch into a browser or you have
> the ability in E9 to incorporate into a Vantage screen. The GREAT thing
> about using SSRS is that it circumvents Epicor's licensing. Meaning,
> you can set up an entire intranet with links to various dashboards and
> reports without the need to install the E9 client on that PC. Great for
> execs and people who just want to see data. You're not burning an E9
> client license nor paying annual maintenance on it.
>
>
>
>
>
> - Can the SSRS reports be modified? Do they pull data directly
> from tables, or from a temporary file the way the Crystal reports do?
> How hard is it to add additional fields (eg from a linked table) onto
> the report?
>
> --------- Absolutely modifiable. They pull directly from tables or
> custom views that you can set up. Adding additional fields is simpler
> than it is in Crystal Reports today.
>
>
>
>
>
> The other GREAT thing about SSRS are the ad-hoc reports for your users.
> Even our tech illiterate users could potentially assemble a simple
> report in a matter of minutes. This is always something that is a
> selling point but I think in the "real world" IT will always be the
> report developers with the exception of a few power users.
>
>
>
> Here's some good reading:
>
>
>
> http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx
>
> http://alexduggleby.com/2008/07/18/sql-server-reporting-services-2008-tu
> torial-in-5-minutes-or-how-to-send-my-boss-a-report-from-a-view-quickly/
>
> http://www.mssqltips.com/tip.asp?tip=1690
>
>
>
>
>
>
>
> Vic
>
> --- In vantage@yahoogroups.com, "Brian Roberts" <broberts@> wrote:
> >
> > We are currently on Vantage 6.1 and planning an upgrade to 9.05. One
> of
> > our issues is the large number of reports in 6 we've customized,
> either
> > modifying the provided Crystal reports or creating new SSRS reports
> > (against a copy of the data in SQL server). I notice in the 9
> > documentation (Install Guide appendix E) that using SSRS reports is an
> > option, but am unclear on the overall picture. Can somebody either
> help
> > me with the questions below, or point me to some general
> documentation?
> > We'll be using SQL server 2008 and installing Epicor to use a SQL
> > database.
> >
> >
> >
> > - If I install the SSRS reports, do they replace the standard
> > Crystal reports, or are these additional reports?
> >
> > - Are the SSRS reports integrated into the 9 menus & screens,
> > or are they run separately via an internet browser (like 6)?
> >
> > - Can the SSRS reports be modified? Do they pull data
> directly
> > from tables, or from a temporary file the way the Crystal reports do?
> > How hard is it to add additional fields (eg from a linked table) onto
> > the report?
> >
> >
> >
> >
> >
> >
> >
> > Thanks,
> >
> >
> >
> > Brian Roberts
> >
> > Business Systems Analyst
> >
> > Ground Effects Ltd
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> ------------------------------------
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
> have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder
> and Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
>
The MSSQL database in Vantage 8 does not have *any* PKs defined in the
schema, much less table relationships. Everything is defined in the
Progress schema holder db.

In Vantage BAQs you can use the Filter Tables option to show you,
based on a selected table, which tables have relationships defined in
the Vantage metadata (which I assume are stored in the z* set of tables
(zrelation, zlookupfield, etc.) Similarly when you add a related table
to a BAQ, the table relations are pre-populated from the metadata.

I would expect something similar in the SSRS environment to enable
rapid query building. Otherwise you'd have to know a heck of lot about
how the database is structured to get anywhere.

-bws

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of vicdrecchio
Sent: Wednesday, October 20, 2010 11:26 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Epicor 9 Crystal vs SSRS reports


Brian, not sure. In the examples I've seen, they have all been on a SQL
platform. SQL identifies PK's and I do know, like Crystal, there is
some small degree of built-in linking intelligence.

But even with 8.03.xxx versions when you're making BAQs, the only
helpful linking you have are the Linked Tables.

For the ad-hoc end-user reports I believe it is more "wizard" driven and
you can make certain tables candidates for choosing so not to overwhelm
the users with 1050 tables to choose from.

I sat through a few demos @ Perspectives. For those of us currently on
SQL 2005 you can download SSRS tools for free from MS. I didn't know
that. Although, they're not as robust in 2005.




--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...>
wrote:
>
> So does SSRS with E9 come with the data models, etc. that know about
> table relationships, FKs, etc. so that building reports gives you the
> 'natural' relationships inherent in the data?
>
I'm not on E9 SQL, but I thought I heard from Ben Nixon that they *did* define PK's in the E9 SQL DB.

But you're right, in 8.x there are zero keys. SQL is just a data-storage-bin with indexing.


--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...> wrote:
>
> The MSSQL database in Vantage 8 does not have *any* PKs defined in the
> schema, much less table relationships. Everything is defined in the
> Progress schema holder db.
>
> In Vantage BAQs you can use the Filter Tables option to show you,
> based on a selected table, which tables have relationships defined in
> the Vantage metadata (which I assume are stored in the z* set of tables
> (zrelation, zlookupfield, etc.) Similarly when you add a related table
> to a BAQ, the table relations are pre-populated from the metadata.
>
> I would expect something similar in the SSRS environment to enable
> rapid query building. Otherwise you'd have to know a heck of lot about
> how the database is structured to get anywhere.
>
> -bws
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of vicdrecchio
> Sent: Wednesday, October 20, 2010 11:26 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Epicor 9 Crystal vs SSRS reports
>
>
> Brian, not sure. In the examples I've seen, they have all been on a SQL
> platform. SQL identifies PK's and I do know, like Crystal, there is
> some small degree of built-in linking intelligence.
>
> But even with 8.03.xxx versions when you're making BAQs, the only
> helpful linking you have are the Linked Tables.
>
> For the ad-hoc end-user reports I believe it is more "wizard" driven and
> you can make certain tables candidates for choosing so not to overwhelm
> the users with 1050 tables to choose from.
>
> I sat through a few demos @ Perspectives. For those of us currently on
> SQL 2005 you can download SSRS tools for free from MS. I didn't know
> that. Although, they're not as robust in 2005.
>
>
>
>
> --- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@>
> wrote:
> >
> > So does SSRS with E9 come with the data models, etc. that know about
> > table relationships, FKs, etc. so that building reports gives you the
> > 'natural' relationships inherent in the data?
> >
>
I was told that there are PK's on some of the tables. I didn't hear
anything beyond that.



I'd like to point out that the same data definitions within Epicor 9 are
used to feed both CR and SSRS.



9.05 has very few SSRS reports ready but an effort is beginning to move
both the legacy Progess reports and CR to SSRS.



I agree that SSRS is definitely where the future is. The message was
consistent at Perspectives.



John



[Non-text portions of this message have been removed]