Setting data source properties for an IDO-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 an IDO-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 the name of the IDO.
      Below the Type field, you should see a field labeled Connection string. The default value in that field might be blank or it might be: [@pConnectionString]
    4. Change the default value in the Connection string field, temporarily, to use this format:
      Data Source=serverName;Initial Catalog=applicationDatabaseName
      Note: You can use the Edit button to open a dialog box that simplifies the creation of this string.

      You are changing the value of this string only temporarily, until you are done testing your report. Remember to change this string value back when you are done testing!

      You will change this later to reference the pConnectionStringIDO parameter you created earlier. This allows it to work in Windows client.

    5. (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.

    6. 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 Text.
    6. Click Query Designer.
      In the Query Designer dialog box, verify that the IDO Name, Properties, and other fields are displayed. If not, click Edit as Text to toggle the view for these fields.
    7. From the IDO Name drop-down list, select the name of the IDO you are basing your report on (for example, UserTasks.
    8. From the Properties list, select the properties you want to include the data for in your report.
    9. In the Filter field, enter this or similar code:
      Username BETWEEN ISNULL(@UsernameStarting,Username) AND
      ISNULL(@UsernameEnding, Username)
      AND TaskName BETWEEN ISNULL(@TaskNameStarting,TaskName) AND
      ISNULL(@TaskNameEnding,TaskName)
      AND ISNULL(RemindDateTime, '1753-01-01') BETWEEN
      ISNULL(@RemindDateTimeStarting,'1753-01-01') AND
      ISNULL(@RemindDateTimeEnding,'9999-12-31')
    10. Optionally, use the OrderBy field to specify what properties should be used to order and present the data in the report.
    11. To view your query in text mode, click Edit as Text.
    12. To execute and verify the query, click the Run ( ! ) button.
      Visual Studio displays a list of the selected query parameters in the Define Query Parameters dialog box.
    13. Verify that all Parameter Value fields specify <Null>, changing them if necessary.
    14. Click OK.
    15. In the Query Designer dialog box, click OK.
    16. In the Dataset Properties dialog box, click OK.
      The Report Data panel displays the new dataset under the Datasets entry.
      Note: Verify that the dataset you specified is the only one in the project. If there are others, delete them.
    17. In the Properties panel field drop-down list (at the top of the panel), select Tablix 1.
      Note: The “Tablix” area is where the data populates the report when generated. For more information about a tablix and other report elements, see the Visual Studio 2008 documentation.
    18. In the General > Dataset Name field, specify the dataset you just created.
    19. Save the project.