Step-by-Step Guide to Creating a Report Model in SQL Reporting Services - Simple
Applies To: System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3
You can use the procedures in this topic to create a simple report model that users in your site can use to build ad-hoc model-based reports based on data in a single view of the Configuration Manager 2007 R2 database. You will create a report model that presents information about the client computers in your site to the report author. This information will be taken from the V_R_System view in the Configuration Manager 2007 database.
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.
The information in this topic applies only to Configuration Manager 2007 R2 and Configuration Manager 2007 R3.
To create a report model, you must complete the following tasks:
Create the report model project—The report model project is a container for the information stored in the report model.
Define a data source for the report model—The report model data source specifies details about the Configuration Manager database from which reports will be generated.
Define a data source view for the report model—The data source view is an XML file that contains information about the views being used in the report model. Using data source views, you can create a subset of the views in the Configuration Manager database to present to the report author. You can simplify this structure to present the data to the report author as if it were in a single list.
Create the report model—The Report Model Wizard creates a report model from the data source view you just specified.
Publish the report model—Deploying the report model copies the report to the SQL Reporting Services report server and makes it available for use. After you complete this deployment, you will be able to run the report only from your SQL Reporting Services Web site.
Deploy the report model to the Configuration Manager console—Before you can create reports directly from the Configuration Manager console, you must complete steps to make the required report model available. For more information, see Step-by-Step Guide to Deploying a Report Model to Configuration Manager.
To create the report model project
From the Windows Start menu, click Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.
After SQL Server Business Intelligence Development Studio opens in Microsoft Visual Studio, click File, click New, and then click Project.
In the New Project dialog box, select Report Model Project in the Templates list.
In the Name field, type Simple_Model.
To create the report model project, click OK.
The Simple_Model solution is displayed in Solution Explorer.
If you cannot see the Solution Explorer pane, click View, and then click Solution Explorer.
To define the data source for the report model
In the Solution Explorer pane of SQL Server Business Intelligence Development Studio, right-click Data Sources, and then click Add New Data Source.
On the Welcome to the Data Source Wizard page, click Next.
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.
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 list box, select the name of your Configuration Manager 2007 database.
To verify the database connection, click Test Connection.
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.
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.
In the Data source name field, type Simple_Model, and then click Finish.
The data source Simple_Model.ds will now be displayed in Solution Explorer under the Data Sources node.
To edit the properties of an existing data source, double-click the data source in the Data Sources folder of the Solution Explorer pane to display the data source properties in Data Source Designer.
To define the data source view for the report model
In Solution Explorer, right-click Data Source Views, and then click Add New Data Source View.
On the Welcome to the Data Source View Wizard page, click Next. The Select a Data Source page is displayed.
In the Relational Data Sources window, verify that the Simple_Model data source is selected, and then click Next.
On the Select Tables and Views page, select the following view from the Available objects list to be used in the report model: dbo.v_R_System.
After selecting the view, click > to transfer the object to the Included objects list.
If the Name Matching page appears, accept the default selections, and click Next.
When you have selected the objects you require, click Next, and then type Simple_Model in the Name field.
Click Finish. The Simple_Model.dsv data source view is displayed in the Data Source Views folder of Solution Explorer.
To create the report model
In Solution Explorer, right-click Report Models, and then click Add New Report Model.
On the Welcome to the Report Model Wizard page, click Next.
On the Select Data Source Views page, verify that Simple_Model.dsv is selected in the Available data source views list, and then click Next.
On the Select report model generation rules page, accept the default values, and then click Next.
On the Collect Model Statistics page, verify that Update model statistics before generating is selected, and then click Next.
On the Completing the Wizard page, verify that Simple_Model is displayed in the Name field.
To complete the wizard and create the report model, click Run.
To exit the wizard, click Finish. The report model is shown in the design window.
To publish the report model (for use in SQL Reporting Services)
In Solution Explorer, right-click the Simple_Model.smdl, and then click Deploy.
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.
Click File, click Save All, and then close SQL Server Business Intelligence Development Studio.
To deploy the report model to Configuration Manager
- 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.
How to Create a New Model-Based Report in SQL Reporting Services
How to Create a New SQL-Based Report in SQL Reporting Services
Step-by-Step Guide to Creating a Report Model in SQL Reporting Services - Advanced
Step-by-Step Guide to Deploying a Report Model to Configuration Manager
For additional information, see Configuration Manager 2007 Information and Support.
To contact the documentation team, email SMSdocs@microsoft.com.