I have to generate a report which will show the top 5 best selling items for each product category. I have a stored proc in SQL which will produce a SQL cursor which contains the results I need. I also have a few reports whihc would benefit from being able to call a user-defined function I have on my SQL database. Can I call a SQL stored proc from within an RMS .QRP report program?
Calling SQL stored proc from RMS .QRP report
Dec 05, 2006
3 Replies
User defined functions can behave just like tables - they would go in the TablesQueried section. UDFs can also return a scalar value - they could be used in the Formula entry of the Column Definition.
You can execute a Stored Procedure in the PreQuery sections: EXEC MySPName Param1, Param2,...ParamN
Cursors are evil.
Glenn Adams Tiber Creek C> I have to generate a report which will show the top 5 best selling items for
Glenn,
Thanks for the help. I was able to call my stored proc, in which I create and populate a table, then use that table in my report columns.
One last trick: How do I pass my report parameters (in this case, the store name or number, start and end dates, and maybe the sales season, which is an additional piece of information I keep track of in the item.subdescription3 field) as paramters to my stored proc? I really can't use them as a filtering expression on my completed table; I need them for part of a subquery within my stored proc. Right now, I have those values hard-coded in my PreQuery section, but I will need to be able to let the user set them like any other report parameter.
I don't believe that can be done. The filters entered by be user just get added to the Where clause executed when the report runs. If there's a way to access the filter settings, I don't know what it is...
Glenn Adams Tiber Creek C> Glenn,
Join the Discussion
Have something to add? Share your thoughts — no account required.
Didn't find your answer?
Ask the community — no account required