You can use the procedures in this topic to create an advanced report model that users in your site can use to build ad-hoc model-based reports based on data in multiple views of the Configuration Manager 2007 R2 database. You will create a report model that presents information about the client computers and the operating system installed on these computers to the report author. This information will be taken from the following views in the Configuration Manager 2007 database:

Selected items from the preceding views will be consolidated into one list, given friendly names, and then presented to the report author in Report Builder for inclusion in ad-hoc reports.

Note
The information in this topic applies only to Configuration Manager 2007 R2 and Configuration Manager 2007 R3.

On the computer where you will perform these procedures, ensure that you have installed SQL Server Business Intelligence Development Studio and that the computer has network connectivity to the reporting services point server. Refer to your SQL Server documentation for detailed information about SQL Server Business Intelligence Development Studio.

To create a report model, you must complete the following tasks:

To create the report model project

  1. From the Windows Start menu, click Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.

  2. After SQL Server Business Intelligence Development Studio opens in Microsoft Visual Studio 2005, click File, click New, and then click Project.

  3. In the New Project dialog box, select Report Model Project in the Templates list.

  4. In the Name field, type Advanced_Model.

  5. In the Location field, specify a folder in which to store the report model files.

  6. To create the report model project, click OK.

  7. The Advanced_Model solution is created and displayed in Solution Explorer.

    Note
    If you cannot see the Solution Explorer pane, click View, and then click Solution Explorer.

To define the data source for the report model

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

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

  3. 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.

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

    1. 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, use the format <database server>\<instance name>.

    2. Select Use Windows Authentication.

    3. In the Select or enter a database name list, select the name of your Configuration Manager 2007 database.

  5. To verify the database connection, click Test Connection.

  6. If the connection succeeds, click OK to close the Connection Manager dialog box. If the connection does not succeed, verify that the information you entered is correct, and then click Test Connection again.

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

  8. In the Data source name field, type Advanced_Model, and then click Finish. The data source Advanced_Model.ds will now be displayed in Solution Explorer under the Data Sources node.

To define the data source view for the report model

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

  2. On the Welcome to the Data Source View Wizard page, click Next. The Select a Data Source page is displayed.

  3. In the Relational Data Sources window, verify that the Advanced_Model data source is selected, and then click Next.

  4. On the Select Tables and Views page, select the following views from the Available objects list to be used in the report model:

    1. dbo.v_R_System

    2. dbo.v_GS_OPERATING_SYSTEM

    After selecting each view, click > to transfer the object to the Included objects list.

    Note
    To help locate views in the Available objects list, click the Name heading at the top of the list to sort the objects into alphabetical order.

  5. If the Name Matching dialog box appears, accept the default selections, and click Next.

  6. When you have selected the objects you require, click Next, and then type Advanced_Model in the Name field.

  7. Click Finish. The Advanced_Model.dsv data source view is displayed in the Data Source Views folder of Solution Explorer.

To define relationships in the data source view

  1. In Solution Explorer, double-click Advanced_Model.dsv to open the design window.

  2. Right-click the title bar of the v_R_System window, click Replace Table, and then click With New Named Query.

  3. In the Create Named Query dialog box, click the add table icon.

  4. In the Add Table dialog box, click the Views tab, select V_GS_OPERATING_SYSTEM from the list, and then click Add.

  5. Click Close to close the Add Table dialog box.

  6. In the Create Named Query dialog box, specify the following information:

    1. Name: Specify the name, Advanced_Model.

    2. Description: Specify the description, Example Reporting Services report model.

  7. In the v_R_System window, select the following items from the list of objects to display in the report model:

    1. ResourceID

    2. ResourceType

    3. Active0

    4. AD_Domain_Name0

    5. AD_SiteName0

    6. Client0

    7. Client_Type0

    8. Client_Version0

    9. CPUType0

    10. Hardware_ID0

    11. User_Domain0

    12. User_Name0

    13. Netbios_Name0

    14. Operating_System_Name_and0

  8. In the v_GS_OPERATING_SYSTEM box, select the following items from the list of objects to display in the report model:

    1. ResourceID

    2. Caption0

    3. CountryCode0

    4. CSDVersion0

    5. Description0

    6. InstallDate0

    7. LastBootUpTime0

    8. Locale0

    9. Manufacturer0

    10. Version0

    11. WindowsDirectory0

  9. To present the objects in these views as one list to the report author, you must specify a common relationship between the two tables or views by using a join. You can join the two views by using the object ResourceID, which appears in both views.

  10. In the v_R_System window, click and hold the ResourceID object and drag it to the ResourceID object in the v_GS_OPERATING_SYSTEM window.

  11. Click OK.

  12. The Advanced_Model window replaces the v_R_System window and contains all of the necessary objects required for the report model from the v_R_System and the v_GS_OPERATING_SYSTEM views. You can now delete the v_GS_OPERATING_SYSTEM window from the data source view designer. Right-click the title bar of the v_GS_OPERATING_SYSTEM window, and then click Delete Table from DSV. In the Delete Objects dialog box, click OK to confirm this.

  13. Click File, and then click Save All.

To create the report model

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

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

  3. On the Select Data Source View page, verify that Advanced_Model.dsv is selected in the Available data source views list, and then click Next.

  4. On the Select report model generation rules page, do not change the default values, and click Next.

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

  6. On the Completing the Wizard page, verify that Advanced_Model is displayed in the Name field.

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

  8. To exit the wizard, click Finish.

  9. The report model is shown in the design window.

To modify the report model

  1. In the report model design view of SQL Server Business Intelligence Development Studio, right-click any object name, and then click Rename.

  2. Type a new name for the selected object, and then press ENTER. For example, you could rename the object CSD_Version_0 to read Windows Service Pack Version.

  3. When you have finished renaming objects, click File, and then click Save All.

To publish the report model (for use in SQL Reporting Services)

  1. In Solution Explorer, right-click Advanced_Model.smdl, and then click Deploy.

  2. Examine the deployment status at the lower left corner of the SQL Server Business Intelligence Development Studio window. When the deployment has finished, Deploy Succeeded will be displayed. If the deployment fails, the reason for the failure will be shown in the Output window. The new report model will now be available from your SQL Reporting Services Web site.

  3. Click File, click Save All, and then close SQL Server Business Intelligence Development Studio.

To deploy the report model to Configuration Manager

  1. To deploy the report model for use in the Configuration Manager console, see Step-by-Step Guide to Deploying a Report Model to Configuration Manager.

See Also