April 24, 2008 at 6:04 am
First off... SQL 2005 newbie here... advanced apologies for anything stupid. 🙂
I am modifying a report from my vendor in Reporting Services. We are allowed to do this. What we are discouraged from doing is modifying the underlying SQL. So.. all I need to do is retrieve 1 additional value from the database to allow me to select differing logos/etc. My thought was to create a stored procedure in SQL and call that from RS. I created the stored procedure to return commission code information. This stored procedure tests fine on its own and returns data correctly from SQL Server Manager. The question is, how do I call this from a report, and how do I pass data from the report as the stored procedure. To explain further, the report will be say 1000 pages long. Each page is a differing customer ID, which will return different information from the stored procedure. This information will let me determine which logo/address info/etc to display on that report page. So I need to pass customer ID on each page to the stored procedure. Can this be done? Any searching Ive done mentions "report parameters", but these seem to be global parameters used during initial run of the report. Any help is appreciated! I'm sure Im missing something simple...
.... I hope.
Thanks in advance!
Ray
April 25, 2008 at 12:25 pm
I know to use a stored procedure in the report, you just create a new dataset with a command type of stored procedure. Then, in the query text, you type the name of the procedure.
However, I do not know how to pass report data to a procedure and I need to know this, also.
Now, I looked again and I've just realized that for the parameters of a dataset, you can use an expression. Maybe this is how it's done.
I hope someone out there responds with an answer:w00t:
April 25, 2008 at 1:45 pm
How are you going to change the logo? Are you going to put in a switch statement that loads an image from a file?
The only way I know to pass a parameter to an SP in SSRS when the main data changes is to create a subreport with customer id as a parameter in the stored procedure which will be the main dataset for the subreport. The issue with doing that is that your stored procedure and subreport will be called once for every customer which means 1000 times each time the report is run.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2008 at 5:18 am
Its more than a logo actually. I need to customize various objects on the report depending on the result. Logo, address, terms & condition, etc. If I can get a value I would just use the visible property to turn items on and off. The problem with a sub-report is that I don't think you can grab a piece of field data from the subreport for use elsewhere in the main report. I wound up getting this to work by using a user defined function and changing the vendors SQL to grab the information in the query. I was trying to avoid this for obvious reasons (don't tell the vendor). It would still be nice to find a method to circumvent this. I've only been using SSRS for a few weeks... but it seems to be full of more limitations than an application like this should be. I feel like I'm developing in Access on Win 3.1! Quite a jump back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply