November 3, 2004 at 7:59 pm
We use Crystal Reports to report on our SQL Server 2000 database(s). As the sole application developer in the company, I've taken responsibility for the creation of most of our crystal reports, and I can't decide on a data access scheme.
On one hand, I use stored procs to generate the resultset as I need it, then use that one stored proc as the sole "table" in the crystal report. In most cases, the stored proc is parameterized, and crystal is (usually) smart enough to prompt for the params when you refresh the report in crystal. I really like this method of report development, in that it lets me do much more complex joins and subqueries (and temporary tables, temporary indexes, cursors, etc.) than crystal's query engine would allow, to get the data exactly how I want it. Then, in crystal, I usually have no need for a record selection formula, and I can do any special grouping, sorting and page layout. Overall, this process works great...for me.
On the other hand, I could create the reports using the crystal query builder, defining the joins with the Database Expert and choosing my report columns from the source tables. This scheme would be more comfortable to the end-users, as they could go in and modify record selection formulas and such to filter the report as they need on an ad-hoc basis.
As I read what I've written, it would seem that I'm leaning towards the first option :-), but I'd like to hear any opinions on the subject. Maybe I just need to talk to my boss and explain how I will own the reports for the company, and if UserA needs a variant of some kind, contact me and I'll create the new stored proc and the associated report. I've never really like the fact that employees who have zero database concepts and/or programming knowledge, nor any knowledge about our database schema, are asked to create complex reports from those databases (but that's a story for another time).
Thanks!
Gary
November 4, 2004 at 5:17 am
IMHO...
Option A provides you with the ability to control the data access and avoid rogue processes and runaways that could potentially crash your server
Option B would allow people with no concept of ensuring that everything is properly liniked and referenced to run anything they want and cause performance problems.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 4, 2004 at 11:35 am
Keep doing it with option A.
You are asking for trouble, as well as potential performance issues, with option B
Been there, done that!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 4, 2004 at 11:26 pm
November 5, 2004 at 4:16 am
One potential problem of option B is that that Crystal may produce sub-optimal SQL to access the database.
This is a generalisation, but most high-end reporting tools have logic to decide if it is best to send the query to the database as-is, or to split the query and join the data in the tool. Sometimes this is good, but more often it hurts performance because the reporting engine is joining and filtering shedloads of data to get a few report lines.
Most people take the approach of creating views or stored procs in the database, and getting the reporting tool to do little more than a select ... from ONE_OBJECT_ONLY where ... (Odd this... most DB2 DBAs favour the use of views, most SQL Server DBAs favour SPs, so take your choice)
Keeping the SQL interface simple between the repoprting tool and the database enables the DBA to tune the access paths far better than the reporting tool can normaly do.
The one exception to this is if you are going via IBMs Data Joiner technology. This often gives faster access to SQL and Oracle than hand-cranked code can normally do. The IBM optimiser technology is world-beating, and will get access path stats from the target server and insert hints all over the SQL to get the fastest access.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 5, 2004 at 8:07 am
Option A also keeps your logic out of the report, which turns the report into just a formatting tool. This allows other tools, such as excel, to use the same source logic, which enhances reuse and makes reconciliation easier. This will also allow you more easily to switch to a different tool if needed.
Example: Crystal Enterprise is doing away with concurrent licensing starting in 2005, which will make the product much more expensive for some people. Using option A would make it easier for you to switch to a new reporting engine such as microsoft reporting services. I have heard rumors of someone creating a conversion tool to go from from crystal to reporting services. This tool will probably work better if the reports are kept simple by keeping the logic in the sp's.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply