We help IT Professionals succeed at work.

Fire Stored Proc by Crystal Report

kennysflau
kennysflau used Ask the Experts™
on
Hi Experts,

I have a stored Proc which will be run daily to extract some data to a table which a crystal report will get information from.

Now my users want to get live data instead of a daily update, so is there any way to fire the stored proc from crystal enterprise and get the data when it's done. Or if there is other alternatives?

My crystal enterprise is XI, product 12.1.0

Thanks!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can base the report on the stored procedure or probably better is a command that executes the stored procedure then queries the table

mlmcc

Author

Commented:
Hello mlmcc, would there be any sample or doc for my reference..?
To use the SP itself as the datasource for the report, you would have to change the SP to output the data that the report needs at the end.  If everything has been put in one table, it might be as simple as adding this to the end of the SP:

SELECT * FROM table

 Then you would change the datasource in the report from the table to that SP.

 If the report is doing any "static" filtering on the data, like never including any records before a certain date, you'd want to include that in the SP.

 If the report is doing any "dynamic" filtering on the data, like a parameter that allows the user to select which customer they want to see, then you'd want to add parameters to the SP for those filters (so that it can do the filtering on the server).  Otherwise, the SP will produce all records and send them to the report, and the report will do the filtering locally.


 A slight variation on that approach would be to create a new SP that executes the old SP and then outputs the data for the report.  That way, you don't have to change the old SP.

 Note that with either of these approaches, you may need to turn off row count messages in the SP.  I've had times when CR seemed to have problems because an SP was producing messages (row counts or warnings) before the actual data.


 A CR Command is like a SP, but it's created in CR and stored in the report.  When you select your db in the Database Expert, there should be an "Add Command" option.  When you open that, it will let you create a manual query, which in this case might be something like:

EXEC your_old_sp

SELECT * FROM table_created_by_sp


 If the report needs data from other tables, you'd have to add them to the query, of course.  FWIW, you can go to Database > "Show SQL Query" in your old report to see the query that it's using.  If you are using other tables, you could copy that query and use it as the starting point for your Command (or SP).

 James

Author

Commented:
Thanks James, that looks gd!

For the "Add Command" option, let says if my SP needs 10 mins to finish running, will the select statement wait until then so that it can get the correct data?
Honestly, I can't say for sure, but that's the way things seem to normally work.  For example, I've had SP's that exec'd another SP to build a temp table, and then used the temp table, and they worked, so the main SP was obviously waiting for the exec'd SP to finish.  But I haven't used Commands much, and never used one to run an SP like this, so it's possible that the Command will work differently.  I would make sure that "Perform Query Asynchronously" is unchecked under File > "Report Options".  That might affect the order in which things happen.  Apart from that, all I can suggest is that you try it.


 Note that while CR is waiting for that data, it will probably be unresponsive.  That's what happens when I use an SP directly as a datasource in CR 10.  I'm guessing that it will be the same when running an SP via a Command.  If the SP doesn't take long, that's probably not a big deal, but 10 minutes is a pretty long time, so I wanted to mention it.

 FWIW, using the "Perform Query Asynchronously" option mentioned above can help with that.  With that option on, CR doesn't just "lock up" while it's waiting for data.  But in some cases that seemed to allow some things in an SP to happen in the wrong order, so, as mentioned above, you may need that option off for your report.  If the "lock up" is a problem, you could try turning the option on and see what happens.

 James