[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Crystal Reports and Stored Procedures- Exception or the RULE?

Posted on 2006-03-23
12
Medium Priority
?
905 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:MIKE
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 16

Assisted Solution

by:wykabryan
wykabryan earned 600 total points
ID: 16270929
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
 
LVL 8

Assisted Solution

by:dylanyee
dylanyee earned 600 total points
ID: 16271239
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
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 800 total points
ID: 16271319
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 17

Author Comment

by:MIKE
ID: 16271427
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16271497
>>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
 
LVL 17

Author Comment

by:MIKE
ID: 16271611
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
 
LVL 17

Author Comment

by:MIKE
ID: 16271640
rhinok,

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

M
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16271690
:) 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
 
LVL 17

Author Comment

by:MIKE
ID: 16271833

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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16273287
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 16275491
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
 
LVL 17

Author Comment

by:MIKE
ID: 16275842
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question