Setting data source properties for a stored procedure-based report

Before you can produce an SSRS report, you must have a source defined for the data to populate the report fields. The procedure to do this is different, depending on whether you are creating a report based on a stored procedure or on an IDO.

To set data source properties for a stored procedure-based SSRS report:

  1. In Visual Studio, locate the Data Source option in the Report Data panel.
    If the Report Data panel is not displayed, you can view it by selecting View menu > Report Data.
  2. Expand the Data Source option and locate or create the data source for your environment.
    Typically, this data source has the same name as the application. If you see the data source you are looking for, double-click the name of the data source and go on to substep c.

    If you do not see the data source you are looking for, you must create it, using substeps a and b, before going on to substep c:

    1. In the Report Data panel, right-click Data Sources and select Add Data Source.
    2. In the Data Source Properties dialog box, specify a Name for the data source (for example, UserTaskDemo).
    3. In the Type field, specify Microsoft SQL Server.
    4. (Optional, but strongly recommended) On the Credentials tab, specify user login credentials for your application database in the Use this user name and password option.
      Make sure you enter your Mongoose login credentials and not the SQL Server login credentials, because this is for an IDO-based report. This allows you to preview report layouts without having to enter login credentials every time.

      Later, you will change this.

    5. Click OK.
    The Report Data panel should now display the data source you just specified, if it did not already.
  3. Add and assign the dataset:
    1. Right-click the name of your data source and, from the context menu, select Add Dataset.
    2. In the Dataset Properties dialog box, Name field, specify an appropriate name for your dataset (for example, UserTaskDataset).
    3. Verify that the Use a dataset embedded in my report option is selected.
    4. Verify that the data source defined in Step 2 is specified in the Data Source field.
    5. For the Query type option, select Stored Procedure.
    6. In the Select or enter stored procedure name field, specify the name of the stored procedure you created (for example, Rpt_UserTaskSp).
    7. Click Refresh Fields.
      Visual Studio displays a list of automatically created query parameters in the Define Query Parameters dialog box.
    8. Verify that all Parameter Value fields specify <Null>.
    9. Click OK.
    10. In the Dataset Properties dialog box, click OK.
      The Report Data panel displays the new dataset under the Datasets entry.
    11. In the Properties panel field drop-down list (at the top of the panel), select Tablix 1.
    12. In the General > Dataset Name field, specify the dataset you just created.