How to Run Stored Procedure before reporting

[krist]

Hi again,
I have a need to run some process (MSSQL store procedure) to calculate and summarize data from table_A and table_B to save the result into table_C.
Then my Crystal RPT will reports based on table_C.

How can I do that with Crystal Report 8 ??
(suppose I only use Crystal and Not integrating it with any other application)

Thanks for usual help,
Krist.

 

[casman2]

A Stored Procedure functions just like any table when using it within Crystal Reports. Use the Stored Procedure as a table. Use Prompts for the data and then Record Selection for attaching the information to the stored procedure. Let me give you an example.

I had a stored procedure called CaseHistory which required a case number. I would create a parameter called CaseNumber, and would put in the Record Selection Criteria (provided parameter in Stored Procedure was called SPCaseNumber) SPCaseNumber = ?CaseNumber

Hope This helps.

 

[krist]

Hi,

Does it mean that, the only way to use Stored Procedured in Crystal Rpt is when the Stored Procedure Returns ResultSet to be used as datasource to report (like a table) ???

What if the process is much_more_complicated than just a query, where I have multiple query and calculations (must be done on the fly), then save to another table.

Like I said :
Stored Procedure process (table_A+table_B) ==> table_C
Then my RPT simply use table_C as datasource.

Can I invoke that kind of Stored Procedure in Crystal, before reporting with table_C ??

Thanks again,
Krist

 

[philbird]

Try writing the stored procedure that does the calculations to return a True\False\Error value and run it in the Report Header. Depending on the result of that procedure will determine if you run a subreport that runs the other stored procedure or displays a message in the main report of the error in the first procedure.

 

[balves]

You can do all of the calculations and queries that you want in the stored procedure, just make sure you end it with a SELECT statement that generates the resultset for the Crystal Report.

Table C could be a temporary table that you create in the stored procedure and then populate through various statements. The stored procedure should then end up doing a SELECT * FROM #TABLEC. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer
(Currently available for consulting in Chicago)