Partager via

HowTo: Create a SQL Server 2000 Reporting Services Report from a Stored Procedure

  • Article

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!

Comments

  • Anonymous
    May 10, 2004
    Take Outs for 10 May 2004
  • Anonymous
    June 06, 2004
    THIS IS SIMPLY EXCELLENT
  • Anonymous
    June 24, 2004
    The comment has been removed
  • Anonymous
    July 20, 2004
    For defining report based on ODBC you may follow below steps
    Aim is to call stored procedure
    sp_fun @val as integer

    1. follow the steps as given in the above article if you are using wizard
    2.then in data view tab type
    {call sp_fun(?)}
    3.Select ReportReport Parameters
    Create Parameters corresponding to stored procedure parameter
    I assume name to be P1,TYPE integer
    4. Invoke DataSet Designer dialog , Select parameters tab
    Set the parameter name to ?
    Set the value to P1 BY SELECTING <Expression...> in the combo and then
    selecting
    Parameters -> P1
    Result will come as
    ?=Parameters!P1.Value
    5.Run the report using icon with title !
    6.Type values
  • Anonymous
    August 05, 2004
    Thanks dude. This really helped me out... u r0x0rz!