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
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wykabryanCommented:
There are a lot of things that go into whether or not to use a store proc.  These would be what are you looking to do with the report, how important is performance of the report, are you doing something that is complex that might require more power.  Most database server are stronger than your RAS servers, so developing a stored proc on the database would run much quicker than in the RAS environment.  We currently have about 15% of our reports based off of stored procs.  It is my opinion the more you can get done in the database the better off the report will be.  One of the reports we run into is checkin threshold amounts based on a summary of measures.  In order to have the report have the ability to prompt the user for a threshold amount, all measure have to be summarized to return the appropriate information.  I would not consider stored proc as either; exception or rule, but rather another vechicle to provide and convert data into information in an effecient manner.
0
dylanyeeCommented:
My personal optionion:

Pro:
- Faster Performance, Stored procedure is a pre-compiled sql object (If you run normal sql instead, sql server will compile your statement at the first time). So the response time from Stored Procedure is faster.
- Flexibility, If you design your report using stored procedure and if you want to change the query in the future, what you need is just modify from SQL Server instead of re-open the CR file and edit.
- Reusability, Many report can share a single Stored Procedure with different parameter pass in and result return.

Con:
- Since reports is depends on stored procedure, deployment of reports to another server have to re-create all stored procedures again. (but 1 script should be able to create all for you)
- Maintenance issue, hard to keep track of which report is using which SP, unless proper documentation or proper naming is done.

dylan
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
This really has to do with personal preference.  Here's my opinion:

1)  I agree with wykabryan regarding doing as much processing as you can on the server.  This should be a formost consideration when designing any report.  That being said, you can push off processing to the database if you have a well written tables-based report with efficient, db-processable record selection criteria.  Everybody has a different reporting environment and, in many, Crystal Report writers are treated as second-class citizens with almost no access to the database.

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.  

3)  I only use Stored Procedures if I need to perform heavy data manipulation or aggregation.  Using a Stored Procedure for a simple query that coudl easily be written into a view gives you very little, if any, performance gain.  Additionally, most stored procedures are generally written for a specific report.  This menas that  you're back to maintaining one object per report, which can be tedious and inefficient.

4)  Stored Procedures as datasources have some limitations that you can avoid by using a view:

>  A stored procedure must be executed and the results must be returned before any additional Crystal Reports Parameters and Criteria can be evaluated.  If you have a designer that is inexperienced, you could actually have degraded performance.  A view is treated like a table.
>  Stored procedures cannot accept multiple value parameters.  If you have a report where the user nees to input an array of parameter values, then they will have to enter the values in a delimited string that you will parse out in the stored procedure.  This complicates the procedure unnecessarily if you can simply use the native Crystal Reports functionality with a view.
> In your version, stored procedures can be linked to other datasources, but you may suffer severe performance degradation.  You won't with a view.
> Stored Procedures can be difficult to work with if you're using subreports, however, I would opine that many subreports could be eliminated with a well written stored procedure.

Stored Procedures can be very powerful and valuable when used with Crystal Reports, however, in your situation I would guess that they are overused.  When I have interviewed candidates for  Crystal Reports positions, some of the questions I've asked are:

1)  What type of datasource have you used when writing Crystal Reports.  If the candidate states that they have only ever used stored procedures then I could probably assume that the candidate doesn't have a solid understanding of basic Crystal Reports functionality, because the candidate has never used that functionality.  Of course, I ask further questions to verify whether or not my assumption is correct.  Conversely, a candidate who has only ever used tables may understand Crystal Reporst functionality, but may not understand how to optimize Crsytal Reports to pass the SQL to the DB and may not even understand SQL as a concept.

2)  Which do you consider to be the best datasource for writing Crystal Reports:  tables, views or stored procedures?  Why?  If the candidate can't elaborate on the type of topics I've mentioned above or if they opine 'always use stored procedures, because they are precompiled and are more efficient' then I know the candidate isn't as experienced as I may like.

~Kurt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

MIKESoftware Solutions ConsultantAuthor Commented:
Excellent points. I see what you are saying.

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.

0
wykabryanCommented:
>>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.  
0
MIKESoftware Solutions ConsultantAuthor Commented:
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
0
MIKESoftware Solutions ConsultantAuthor Commented:
rhinok,

Excellent points...and well taken...I'm taking notes on this..very insightful.

M
0
wykabryanCommented:
:) 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.
0
MIKESoftware Solutions ConsultantAuthor Commented:

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.

0
mlmccCommented:
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
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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.pracid)
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
0
MIKESoftware Solutions ConsultantAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.