HowTo: Create a SQL Server 2000 Reporting Services Report from a Stored Procedure
Several folks at my sessions have asked how to create a report in SQL Server 2000 Reporting Services Report Designer based on a stored procedure. Keep in mind, there are some tricky steps if you are using the report wizard. You don't get the opportunity in the wizard to tell it you are using a stored procedure.
If your stored proc has no parameters, life's a bit easier here. Let's say that I have a stored procedure called GetSalesByRegion that returns a single set of data (which is the only kind supported, BTW). Using the report wizard, I would enter EXEC GetSalesByRegion as the query string. This will return the set of data for the report wizard to do its magic.
However, what if there's a parameter for this stored proc, perhaps something like @RegionName? When creating the report with the wizard, if this parameter has no default value, you'll have to do something like this: EXEC GetSalesByRegion 'North America' in order to get through the wizard. But now you're stuck with a hard coded parameter, or forced to use the default parameter. How do you change it later?
The answer lies on the Data tab. When you go to that tab after the wizard has run, you'll see your EXEC statement. You want to change the query type from a text command (as is represented by the presence of the word EXEC) to a stored procedure. You do this by clicking on the button to switch to the Generic Query Designer view. Once there, you'll see a drop down on the right that says Command Type . It will say Text. Change it to StoredProcedure and remove the word EXEC and any hard-coded parameter values following the SP name. Now, if you click the Execute (!) button, you should see a window prompting you to enter parameter values. Try a few values in here to make sure it works.
The last step you have to do is connect Report parameters to Query parameters. But guess what? The above step already does this for you. If you go to the Report menu and choose Report Parameters, you'll see the parameter already in there! Now, if you like, you can connect the value choices for this paramenter to a static list or another dataset.
Incidentally, if you had created a report without the wizard, you can (at the point you are adding the dataset to the report) select Command Type of StoredProcedure. But we all love wizards, don't we?
Happy reporting!
