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!

Comments (5)

  1. Take Outs for 10 May 2004

  2. ZIYAD MOHAMMAD says:

    THIS IS SIMPLY EXCELLENT

  3. Derek T says:

    I am using Oracle OLE DB – and am accessing a procedure to return fields to my report. The field list in RS populates – and I can build the project without getting errors. However, when I attempt to execute or preview the report, I get an error saying:

    An error has occurred during report processing.

    Query execution failed for data set ‘spAcreageData’

    ORA-06550: line 1, column 7:

    PLS-00306: wrong number of types or arguments in call to ‘rums_sp_report_acreage_data’

    ORA-06550: line 1, column 7:

    PL/SQL: Statement ignored

    I have two parameters and two "in" parameters in the Oracle procedure

    I’ve done what you suggest above – but clicking the Run/Execute doesn’t work – it pops up the dialogue asking for values – I enter valid values and get the error above.

    have any suggestions?

  4. nsarovar says:

    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

  5. Greatful1 says:

    Thanks dude. This really helped me out… u r0x0rz!