Design qestion - Stored procedure - yes or no.. 1

[jyoti05]

I have to design a report using Crystal 10

For Every quarter I print data for the quarter, Total for Quarter, YTD totals. This will be for 2 years and with % difference.

For e.g.
Supplier Jan204 Jan05 %diff Feb04 Feb05 %diff Mar04 Mar05 %diff, Qtr104, Qtr 05 %diff, YTD04 YTD05 %diff

If it better to do the calculations in a stored procedure and use the table generated as an input or use formulas within the report. I once tried using a stored procedure directly within the report and faced performance issues.

 

[synapsevampire]

Stored Procedures should ALWAYS outrun anything from within Crystal.

If you had performance issues, it was the fault of the person that created your SP.

-k

 

[jyoti05]

Thanks! In the above case, will I be able to pass a parameter to the stored procedure i.e year

 

[synapsevampire]

Sure, however you might consider a default to last year or whatever is appropriate within the SP in case a null or some default value is passed.

This would allow you to schedule the report automatically in the future.

-k

 

[jyoti05]

Thanks. I tried retrieving simple values and it worked. Now my problem is - For suppliers I want to accept multiple values as a parameter in Crystal Report and the stored procedure should only calculate for the selected suppliers. I tried with one parameter but ran into a block for multiple...

 

[synapsevampire]

Stored Procedure parameters don't allow for multiple values, this has nothing to do with Crystal.

There are possible solutions to this, but please keep in mind you've shared very little about your report requirements:

You can pass a comma delimited string to the SP and let the SP parse out the values.

You can switch to using a View, which will allow Crystal Parameters to handle passing multiple values to the View.

-k

 

[jyoti05]

My report requirement is to display the above data for the selected suppliers, for the selected year and previous year and selected quarter. I am familiar with delimiting values passed to a stored procedure. But how do I combine with Crystal Report is my challenge. Let me know if I can provide any more input..Will I have to create a concatenated string with the supplier parameter selections and pass THAT string to the stored procedure as input?

 

[synapsevampire]

Yeah, that's what I meant, and I know, it ain't purdy.

I tend to use Views whenever feasible for this reason.

-k