parameters in crystal reports - what should I do?

[JSeidel]

I am currently running CR 8.5 with the plan to move to 9.0 in the near future. I am aware of two methods of creating parameters - 1. embedding SQL parameters and having crystal call the parameters and 2. using the parameter formula editor to create them...

what is better to do? I need to have the reports as easily modifiable for customers as possible..

 

[synapsevampire]

Do you mean the Crystal SQL Designer? I suggest that everyone move away from it. Even Crystal doesn't suggest adding in that layer these days, and it was ill advised in CR 8. It doesn't even ship with CR 9, you have to go download it if they can't convince you to use real SQL tools. I'd get used to an entirely new methodology wherein you create Views and Stored Procedures as data sources, assuring that processing is performed by the database.

If not the SQL Designer (please mention details like that when posting), what are you using?

You can create parameters in Crystal, or if you're using a Stored Procedure with a parameter, then Crystal will recognize it and prompt for it.

Is that what you mean?

If you're using a Stored Procedure, generally the intent is to get a precompiled execution plan, hence sp[eeding the report, and making the data as atomic as is possible.

So if all of your reports are based on SP's, you should embed your parameters in the SP because Crystal will not pass a parameter to an SP which does not have parameters inside of it, hence Crystal will be doing database processing - bad idea.

-k

 

[vidru]

I agree with k. I look at Crystal as merely a formatting tool. I let the server handle as much of the processing as possible (by way of stored procedures).

If what you're really concerned about is maintaining a different set of reports for different clients/users, I feel your pain. I have a default set of reports that everyone gets. When someone asks for a custom modification, I separate the report from the default set into a folder for that client, and rename the procedure and the report so that they are truly unique to that client. I then use a database to store the reports to keep track of revisions. That keeps me from worrying about their 'custom' procedure or report getting overwritten when we issue upgrades.

What exactly does "I need to have the reports as easily modifiable for customers as possible.." mean? If you mean that you want the users to be able to edit their reports, then that's where the stored procedure as data source method causes trouble. I run into that all the time. The best way that I've found to handle that is to always return some columns that might seem superfluous to one client, but could be important for another. So in many cases, even though a field isn't shown on the report, it's available for someone editing the report.

I haven't had experience with Crystal 9 outside of the 30 day demo, but maybe someone can discuss the benefits/limitations of the SQL Command object that is new to 9.0.

-dave

 

[JSeidel]

Hi all:

thanks for your input - greatly appreciated.. Yes, I was talking about using stored procedures to pass parameters vs the crystal parameter component they have within their formula editor. Most of our reports are moving towards stored procedures but I do not want to limit a clients capability to creating the parameters with crystal (which the issue with passing these paramaters into the report when using the stored procedure is now duely noted). What about the crystal sql designer - this (in effect), creates the sql but names the object as .qry. Does anyone know if this performs just as well as a stored procedure? and if I use this method, will I have the capability to pass parameters I write using the crystal front end tool into the .qry extension?

 

[trezlub]

Always use stored procedures over the SQL designer. Stored procedures are very often executed on a server and therefore the SQL will run more quickly here than if it were placed in the designer. Another advantage of using the stored procedure method is that if your report changes for whatever reason you may be able to get away with simply altering something in the stored procedure, and therefore your .rpt file need never be altered. This coudl save tons of time if many users are looking at the report from a central server. Also, if you use the SQL designer then the SQL you enter will only be available for that report. By using stored procedures you can reuse sql for many reports.

Personally I use Stored Procedures ever single time as I have never yet been convinced that there is a better method. They are so versatile, efficient and have minimal impact. I recommend you use Stored Procedures everytime.

 

[Ngolem]

Another BIG limitation of using SQL Designer is that reports using this tool can never be used over the interNet.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[moonknightt]

questions for all..

If you are building your report using parameters embeded in the stored procedures, how doyou get an asp page to pull it.
It was easier using tables to pull the data.

parmid = "P1"
provider = "MSDASQL"
datasource = "FreedomPoint"
username = "itreporting"
password = ""
databasetable = "Proc(spRptAllProspectsDataDownloadSelect;1)"
valuedatabasecolumn = "Name"


This gives me an error...is there a better way