SSMS 2016
Crystal Reports V 14.0.2.264 RTM
I have a situation where I have a stored procedure that builds a table. I cannot use a view because I need multiple records per job based on 6 different fields in the job master table I have to look at and create a record for each project mgr on the job. So a single job could potentially have 6 different records with 6 different project mgrs. I need the stored procedure to run each time the report is run to refresh the input data.
Can anyone tell me how to do this?
I have tried creating a stored procedure that returns the data, but as far as I can tell from research CR does not support multiple values coming in from a stored proc (I get an error saying no data was loaded). I tried linking the table in CR but I have 6 different field names that I need to have in the same field name for the report output.
There may be some other way to do it that I don't know of and any suggestions are appreciated.
Thank you in advance for your help.
Can you have the stored procedure build a table with the data you need in the report and then use a report query to pull the data from that table instead of trying to have the stored proc return the data?
Yes and that’s what I ended up doing but how do I get CR to run the stored procedure every time the report is run so the data is real time?
Not entirely sure what you mean. If the report is running the stored procedure then it should run every time the report is run. Are you running the stored procedure outside of running the report? Maybe this will help
https://flylib.com/books/en/4.229.1.100/1/
or this
http://www.forumtopics.com/busobj/viewtopic.php?p=693522
Where do I need to put the stored procedure for it to run every time? There are no parameters to put on the report from the stored procedure, it is just rebuilding the table the report is looking at. Should that be done outside of CR?
The links provided above should help you with that. Otherwise I'd refer to Crystal Report documentation / support on how to have a Crystal Report run a stored procedure.