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:
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:
>[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?
>