Crystal Reports and Stored Procedures- Exception or the RULE?
Experts:
Just had an interesting discussion regarding Crystal Reports and the use of MS SQL Server 2000 - "STORED PROCEDURES."
This particular business that I was talking to has Crystal Reports v10, but almost EXCLUSIVELY uses Stored Procedures to produce/process ALL of their reporting. I don't know anything about their DATABASE structure. All I know is that they are on CR v10 and SQL Svr 2000 DB's.
Question: Is the use of STORED PROCEDURES the exception or the rule? What are Pros and Cons?
I use STORED PROCEDURES on a very limited basis, because in my opinion, CR XI (currect version I develope on) can handle 99% of my reporting requests. I think we have a very complex set of DB's and do not yet see the need to get heavy into creating or maintaining Stored Procedures in order to process or manipulate my data for reports? Can you give me some insight on the purpose behind Stored Procedures and why a business would opt to be heavily into them while using Crystal Reports as their sole reporting enterprise package?
Thank you for your insight?
M
Just had an interesting discussion regarding Crystal Reports and the use of MS SQL Server 2000 - "STORED PROCEDURES."
This particular business that I was talking to has Crystal Reports v10, but almost EXCLUSIVELY uses Stored Procedures to produce/process ALL of their reporting. I don't know anything about their DATABASE structure. All I know is that they are on CR v10 and SQL Svr 2000 DB's.
Question: Is the use of STORED PROCEDURES the exception or the rule? What are Pros and Cons?
I use STORED PROCEDURES on a very limited basis, because in my opinion, CR XI (currect version I develope on) can handle 99% of my reporting requests. I think we have a very complex set of DB's and do not yet see the need to get heavy into creating or maintaining Stored Procedures in order to process or manipulate my data for reports? Can you give me some insight on the purpose behind Stored Procedures and why a business would opt to be heavily into them while using Crystal Reports as their sole reporting enterprise package?
Thank you for your insight?
M
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
>>My assumption is that this migration to total Stored Procs is an advancement that ALL companies eventually will make. And it is soley based on advanced knowledge of SQL Svr and Stored Procs.
No, not neccessarily. It depends on a lot of variables; but any good developer keeps in performance that the front of any other development issue. If a stored procedure is the best path than it should be used but you have to understand the database structure, the needs of the requestor, and understand the full capability of CR. Once all of that is in line, you then will decide what is the best vechicle to use. In most larger companies, you will be very hard pressed to have the ability to have all of your reports based on stored procs. There are very select few cases that would require every report to be based off a store proc.
No, not neccessarily. It depends on a lot of variables; but any good developer keeps in performance that the front of any other development issue. If a stored procedure is the best path than it should be used but you have to understand the database structure, the needs of the requestor, and understand the full capability of CR. Once all of that is in line, you then will decide what is the best vechicle to use. In most larger companies, you will be very hard pressed to have the ability to have all of your reports based on stored procs. There are very select few cases that would require every report to be based off a store proc.
ASKER
Interesting. This is what initiated my question today. I just spoke with an IT Director regarding their operation. He said that almost 100% of their Crystal Reports are based on Stored Procs. I was inquisitive about this and the main reason centered on getting a report to process in "seconds" versus "minutes" Their comment was that IF a report took longer than 7 seconds,...it is taking way too long. They pride themselves on absolute minimal processing time.
My sumation comes from my experience in many different areas of reporting. In all areas, (report end user, report developer, hardware management/maintenance) all areas seem to benefit from Stored Procs, because....processing time is drastically reduced. It appears... in my opinion that the only hinderance to a company using stored procs would be their inability to create them. In other words the lack of in-depth/advanced knowlege of DBA...
Actually the only thing hindering myself and something that I'm working on exploring in depth here...is my lack of knowlege of how to create stored procs.
Again, I really appreciate all of this expert advice and insight.
Thx
M
My sumation comes from my experience in many different areas of reporting. In all areas, (report end user, report developer, hardware management/maintenance) all areas seem to benefit from Stored Procs, because....processing time is drastically reduced. It appears... in my opinion that the only hinderance to a company using stored procs would be their inability to create them. In other words the lack of in-depth/advanced knowlege of DBA...
Actually the only thing hindering myself and something that I'm working on exploring in depth here...is my lack of knowlege of how to create stored procs.
Again, I really appreciate all of this expert advice and insight.
Thx
M
ASKER
rhinok,
Excellent points...and well taken...I'm taking notes on this..very insightful.
M
Excellent points...and well taken...I'm taking notes on this..very insightful.
M
:) And this would be one of those few cases("Their comment was that IF a report took longer than 7 seconds,...it is taking way too long."). Hopefully it works out for the best.
ASKER
rhinok, this comment intrigued me.
2) My personal preference is to use SQL views for the majority of my reports. I can take a view that is written to return a recordset that will be used for a suite of reports and customize the SQL WHERE clause in each individual report through the use of the record selection criteria. All of the complex joins, functions and subqueries that I might not be able to do through table joins are handled by the db within the view and I pass the WHERE clause in to the DB for processing, too. This means that I can have one single DB object that is reusable amongst multiple reports with similar content. This is very desirable from a maintenance perspective.
So when you say that when you are "unable to do in Crystal with Joins" etc....does that mean that Crystal has a limitation or flaw, that is filled-in or compensated for by Stored Procs?
Again, I'm leaning toward the belief that stored procs are a benefit that all companies need to migrate towards... but only those that have DBA's or Developers that are able to write Stored Procs...are the companies that are able to get the benefit.
It seems that Stored Procs are the destiny that all report writers would hope to acheive...., but not all are able to without DBA's ...etc.
If you can write a view, then you can write a stored procedure.
A stored procedure can do anything that a view can do. The difference is that the stred procedure has much more power in the ability to accept parameters, do complex calculations.
I also use views for the most part. Mainly because i generally just need to retrieve the data but also it gives more control than using the tables directly. I use stored procedures when parameters are needed or complex data manipulations are desired.
I think the above discussion is an excellent presentation.
mlmcc
A stored procedure can do anything that a view can do. The difference is that the stred procedure has much more power in the ability to accept parameters, do complex calculations.
I also use views for the most part. Mainly because i generally just need to retrieve the data but also it gives more control than using the tables directly. I use stored procedures when parameters are needed or complex data manipulations are desired.
I think the above discussion is an excellent presentation.
mlmcc
Hi Mike,
>>So when you say that when you are "unable to do in Crystal with Joins" etc....does that mean that Crystal has a limitation or flaw, that is filled-in or compensated for by Stored Procs?
As a simple example, I have a report with two three tables: Practitioner, Offices and Specialties. Each practitioner may have multiple offices and/or specialties, but doesn't have to have any. I've been tasked with creating a report that lists all practitioners with their primary office only, if they have an office and their primary speciality if they have a specialty. If there's no primary office or primary specialty, return NULLS. So, my dataset should look something like t his:
PRACID PRIMARY OFFICE NAME PRIMARY SPECIALTY
--------------------------
0123 Springville Office Pediatrics
2345 Geriatrics
3456 Spanish Fork Office Podiatrist
4567
etc....
Since I want all records from Practitioner and I want potential matches from both Offices and Specialties, I should left outer join each of those tables to Practitioner as the primary table. The problem is that as soon as I apply a filter to both Offices and Specialties so that only the primary record from each table is returned I've implicitly turned the left outer joins into inner joins (since you can't have NULLs returned after you've applied a filter to the recordsetl). Within Crystal Reports, there are several ways you could get around this:
1) In your record selection criteria you could do something like this:
(IsNULL({offices.pracid)
or {offices.primary} = 'Y')
and
(IsNULL({specialties.praci
or {specialties.primary} = 'Y')
The problem is that this isn't efficient and Crystal typically doesn't like OR statements
2) Use subreports, which is what 90% of most Crystal Report writers would do. This is also inefficient
3) Create a SQL Expression field for the Office Name and the Specialty. The SQL Expression field in this example is essentially a correlated subquery. This is efficient, but if you have access to the db, unnecessary.
4) Write a view upon which to base the report:
SELECT
P.pracid,
O.officedesc,
S.specdesc
FROM
practitioners P
LEFT JOIN offices O ON P.pracid = O.pracid
AND O.primary = 'Y'
LEFT JOIN specialties S ON P.pracid = S.pracid
AND S.primary = 'Y'
Notice there is no WHERE clause. This is because the filtering is done within the join, which is something you can't do in the Data Links window. The integrity of the left outer join is maintained and the filters are applied. Furthermore, if I wanted to I could apply additional filters in the Crystal Reports record selection formula that will be passed to the DB as the WHERE clause for processing. This gives me the flexibility of using true SQL and Crystal Reports functionality. I could just as easily create a stored procedure that does exactly the same thing, however, it generally wouldn't be more efficient (because the query is so simple) AND if I were to add additional Crystal Reports filters, it would slow down the report instead of speeding it up... Instead, I would need to modify the stored procedure by adding parameters, which now makes the stored procedure specific to only this report (whereas the view could be used by any number of reports that use that same base recordset - the WHERE clause will be built into each report and passed to the db for processing).
~Kurt
>>So when you say that when you are "unable to do in Crystal with Joins" etc....does that mean that Crystal has a limitation or flaw, that is filled-in or compensated for by Stored Procs?
As a simple example, I have a report with two three tables: Practitioner, Offices and Specialties. Each practitioner may have multiple offices and/or specialties, but doesn't have to have any. I've been tasked with creating a report that lists all practitioners with their primary office only, if they have an office and their primary speciality if they have a specialty. If there's no primary office or primary specialty, return NULLS. So, my dataset should look something like t his:
PRACID PRIMARY OFFICE NAME PRIMARY SPECIALTY
--------------------------
0123 Springville Office Pediatrics
2345 Geriatrics
3456 Spanish Fork Office Podiatrist
4567
etc....
Since I want all records from Practitioner and I want potential matches from both Offices and Specialties, I should left outer join each of those tables to Practitioner as the primary table. The problem is that as soon as I apply a filter to both Offices and Specialties so that only the primary record from each table is returned I've implicitly turned the left outer joins into inner joins (since you can't have NULLs returned after you've applied a filter to the recordsetl). Within Crystal Reports, there are several ways you could get around this:
1) In your record selection criteria you could do something like this:
(IsNULL({offices.pracid)
or {offices.primary} = 'Y')
and
(IsNULL({specialties.praci
or {specialties.primary} = 'Y')
The problem is that this isn't efficient and Crystal typically doesn't like OR statements
2) Use subreports, which is what 90% of most Crystal Report writers would do. This is also inefficient
3) Create a SQL Expression field for the Office Name and the Specialty. The SQL Expression field in this example is essentially a correlated subquery. This is efficient, but if you have access to the db, unnecessary.
4) Write a view upon which to base the report:
SELECT
P.pracid,
O.officedesc,
S.specdesc
FROM
practitioners P
LEFT JOIN offices O ON P.pracid = O.pracid
AND O.primary = 'Y'
LEFT JOIN specialties S ON P.pracid = S.pracid
AND S.primary = 'Y'
Notice there is no WHERE clause. This is because the filtering is done within the join, which is something you can't do in the Data Links window. The integrity of the left outer join is maintained and the filters are applied. Furthermore, if I wanted to I could apply additional filters in the Crystal Reports record selection formula that will be passed to the DB as the WHERE clause for processing. This gives me the flexibility of using true SQL and Crystal Reports functionality. I could just as easily create a stored procedure that does exactly the same thing, however, it generally wouldn't be more efficient (because the query is so simple) AND if I were to add additional Crystal Reports filters, it would slow down the report instead of speeding it up... Instead, I would need to modify the stored procedure by adding parameters, which now makes the stored procedure specific to only this report (whereas the view could be used by any number of reports that use that same base recordset - the WHERE clause will be built into each report and passed to the db for processing).
~Kurt
ASKER
GOT IT.. good stuff Kurt.
I appreciate the extra input....very good info and easy to understand.
....By chance you haven't thought of writing a Crystal Reporting Book have you? Personally, I would think that a book that contains this kind of "MIGRATION info" from INTERMEDIATE user to POWER user...would find a large market?
Just a thought.
Anyway, thanks.
M
I appreciate the extra input....very good info and easy to understand.
....By chance you haven't thought of writing a Crystal Reporting Book have you? Personally, I would think that a book that contains this kind of "MIGRATION info" from INTERMEDIATE user to POWER user...would find a large market?
Just a thought.
Anyway, thanks.
M
ASKER
My assumption is that this migration to total Stored Procs is an advancement that ALL companies eventually will make. And it is soley based on advanced knowledge of SQL Svr and Stored Procs.
Personally, I cannot see a reason to avoid them as all most all aspects of reporting is improved in most cases.