Topic Last Updated—August 2008

This step-by-step guide contains procedures to guide you through the process of using SQL Reporting Services in Configuration Manager 2007 R2 to create a model-based report.

This example report displays general details for each computer in a specified collection. Additionally, this report links to the Computer information for a specific computer report to provide further information about any computer you select.

For full details about options in SQL Server Business Development Studio and Microsoft Report Builder, see your SQL documentation.

Important
Before you commence this procedure, you must install The SQL Server Business Intelligence Development Studio component of Microsoft SQL Server.
Note
The information in this topic applies only to Configuration Manager 2007 R2 and Configuration Manager 2007 R3.

To create a report model for the report

  1. Open SQL Server Business Intelligence Development Studio.

  2. Click File, New and then click Project.

  3. In the Templates list, select Report Model Project.

  4. In the Name field, type Example Report Model.

  5. Click OK to create the report model project.

  6. In the Solution Explorer pane of SQL Server Business Intelligence Development Studio, right-click Data Sources, and then click Add New Data Source.

  7. On the Welcome to the Data Source Wizard page, click Next.

  8. On the Select how to define the connection page, verify that Create a data source based on an existing or new connection is selected, and then click New.

  9. In the Connection Manager dialog box, specify the following connection properties for the data source:

    • Server name—Type in the name of your Configuration Manager 2007 database server, or select it from the drop-down list. If you are working with a named instance rather than the default instance, type <database server>\<instance name>.

    • Select Use Windows Authentication.

    • In the Select or enter a database name drop-down list, select the name of your Configuration Manager 2007 database.

  10. To verify that the database connection works, click Test Connection.

  11. If the connection succeeds, click OK. If the connection does not succeed, verify that the information you entered is correct, and then click Test Connection again.

  12. On the Select how to define the connection page, verify that Create a data source based on an existing or new connection is selected, verify that the data source you have just specified is selected in the Data connections list box, and then click Next.

  13. In the Data source name box, type Example Report Model and then click Finish.

  14. In Solution Explorer, right-click Data Source Views, and then click Add New Data Source View.

  15. On the Welcome To The Data Source View Wizard page, click Next.

  16. On the Select a Data Source page, verify that Example Report Model is selected in the Relational data sources window and then click Next.

  17. On the Select Tables and Views page, select the tables or views from the Configuration Manager 2007 database that you want to use in this report model. Select the following item:

    • dbo.v_R_System

  18. Click Next to continue.

  19. On the Completing the Wizard page, in the Name field, type Example Report Model then click Finish.

  20. In Solution Explorer, right-click Report Models, then click Add New Report Model.

  21. On the Welcome to the Report Model Wizard page, click Next.

  22. On the Select Data Source Views page, verify that Example Report Model.dsv is selected in the Available data source views list, and then click Next.

  23. On the Select report model generation rules page, accept the default values, and then click Next.

  24. On the Collect Model Statistics page, verify that Update model statistics before generating is selected, and then click Next.

  25. On the Completing the Wizard page, verify that Example Report Model is displayed in the Name field.

  26. To complete the wizard and create the report model, click Run.

  27. To exit the wizard, click Finish.

  28. In Solution Explorer, right-click the Example Report Model project, and then click Deploy. If any errors or warnings are encountered, they are displayed in the Output window.

  29. In Solution Explorer, examine the FullPath property for the Example Report Model.

  30. Copy the files Example Report Model.smdl and Example Report Model.dsv from this folder to c:\sms\adminUI\XmlStorage\Other.

To create a model-based report

  1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services.

  2. Right-click the report server name, and select Create Report. You can also right-click a report folder name to create the report in that folder.

    Important
    Before creating a report using the Create Report Wizard, you must have created at least one report folder on the reporting server. Report folders are created when you use the Copy Reports Wizard or when you configure the report server options.
  3. On the Information page of the Create Report Wizard, select Model-based report, and then click Next.

  4. On the Model Selection page of the Create Report Wizard, select the report model Example Report Model from the Model drop-down list. You can browse the views and entities exposed by this model in the Preview window. Click Next to continue.

  5. On the Summary page of the Create Report Wizard, review information about the report that will be created and then click Next.

  6. After the Progress page of the Create Report Wizard is displayed, review the Wizard Completed page and then click Close to exit the wizard and open Microsoft Report Builder.

  7. In the Microsoft Report Builder design window, click Click to add title and type in Example Report.

  8. From the Fields window, select the following attributes and drag them into the design window in the Drag and drop column fields area. You can order the columns however you want. You can also double-click an attribute in the Fields window to transfer it into the design window.

    • Netbios Name 0

    • CPU Type 0

    • Client Version 0

  9. For more information about the options available in Microsoft Report Builder, see your SQL Server documentation.

  10. Click Run Report to preview how the report will look.

  11. When you have finished creating the report, click File and then click Save.

  12. In the Save As Report dialog box, specify a name for this report and the report folder in which it is to be stored and then click Save.

  13. Close Microsoft Report Builder.

See Also