Report Model Samples

5/28/2010


With SQL Server Reporting Services, you can build semantic models based on SQL Server databases or Oracle databases

running version 9.2.0.3 or later by running Report Model Designer within Business Intelligence Development Studio. After

you deploy the model to the report server, you can assign role-based security permissions. Then, your Report Builder users

can use the model to build ad hoc reports in Report Builder.

Important:
SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. Both can be downloaded from Microsoft SQL Server Community Projects & Samples Web site. | The samples are installed by default at: <drive>:\Program Files\Microsoft SQL Server\100\Samples Note Business Intelligence (BI) Development Studio is not supported on Itanium-based computers. However, support for BI Development Studio is available for x64-based computers. If the SQL Server sample databases have been deployed on an Itanium-based computer, use BI Development Studio on either an x86-based or x64-based computer to modify and run the samples. Scenario The purpose of this sample is to show SQL Server Reporting Services Model Designer/Report Builder users how report models are structured and how report models are used in Report Builder. Languages Semantic Model Definition Language (SMDL) Features The Adventure Works 2008 R2 model report sample uses the following features of Reporting Services.
Application Area	Features
Reporting Services	Model Designer
Reporting Services	Report Builder
Reporting Services	Report Manager

Prerequisites

Before running this sample, make sure the following software is installed:

• SQL Server, including the following components:
• Reporting Services
• Business Intelligence Development Studio
• One sample database: AdventureWorks2008R2
• Microsoft .NET Framework 2.0 on the computer(s) that you are using to run Model Designer and Report Builder.
• Visual Studio. In addition, make sure that you have permissions to:
• Retrieve data from the AdventureWorks2008R2 database.
• Publish to the report server.
• Assign role-based security permissions to catalog items.

Opening the Adventure Works 2008 R2 Model

The sample needs to be deployed to a report server. Place the model files in a network location that is accessible to the

computer that you intend to run Model Designer on. After it is deployed, you can open the model in the Business

Intelligence Development Studio.

To open the Adventure Works 2008 R2 model in Model Designer

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 and then click *Business Intelligence

Development Studio*.

1. On the File menu, point to Open, and then click Project/Solution.
2. In Open Project, navigate to the network location where you saved the model files.
3. Select the Adventure Works 2008 R2 Model.sln file, and then click Open.

Note:
Alternatively, you can add the .smdl file to an already existing model by right-clicking the Report Models folder, pointing to Add, and clicking Existing Item. Navigate to the location where you saved the sample files. Select the .smdl file and then click Add. | The sample model is displayed in the Business Intelligence Development Studio window. You can edit the model and .dsv files, save the model to the report server and assign security permissions. Deploying the Model When you are finished modifying the model, you need to deploy the model to the report server. To deploy the Adventure Works 2008 R2 model from Model Designer In Solution Explorer, right-click the Adventure Works 2008 R2 Model project, and then click Properties. In the * Adventure Works 2008 R2 Model Property Pages* dialog box, verify that the TargetDataSourceFolderand TargetModelFolder properties contain the folders that you want to use and the and the TargetServerURL property the report server to which to deploy the model.# In Solution Explorer, right-click the Adventure Works 2008 R2 Model project. On the Build menu, click Deploy Report Model Project.
Note:
Alternatively, right-click the report model project and select Deploy.

Deployment can take several minutes. If successful, a message is displayed at the bottom of the window indicating the

model was successfully deployed. If an error occurred during deployment, a message is displayed indicating the error.

Assigning Permissions to the Report Model

To assign permissions to the Adventure Works 2008 R2 report model in Report Manager

1. Open Report Manager and navigate to the folder that contains the Adventure Works 2008 R2report model. # Click the model

and click Security.

1. In the left pane, click Model Item Security.
2. Select the Secure individual model items independently for this model check box.
3. Chose whether to assign specific permissions to items or apply inherited permissions to items. You must assign

permissions to the top level item in the model.

1. If assigning specific permissions, click *Assign read permission to the following users and groups (semi-colon

separated)* and in the text box, type or copy paste user and group names, and the click Apply.

1. If using inherited permissions, click the item to which apply the permissions, click *Inherit permissions from the

parent item, and the click Apply*.

Opening the Adventure Works 2008 R2 Model Sample in Report Builder

After you have saved the report model to the report server and assigned the appropriate permissions to the report, you can

open use it in Report Builder.

To query the Adventure Works 2008 R2 report model in Report Builder 3.0

1. In your Web browser, type the URL for your report server in the address bar. By default, the URL is

http://<webservername>/reports.

1. Click Report Builder.
2. Create a data source that connects to Adventure Works 2008 R2 report model.
3. Create a dataset from data retrieved from the Adventure Works 2008 R2 report model.

1. In your Web browser, type the URL for your report server in the address bar. By default, the URL is

http://<webservername>/reports.

1. Click Report Builder.
2. In the Getting Started pane, select Adventure Works 2008 R2, and then click OK.Your model is displayed in the

Explorer pane.

Other Report Model Resources

For more information about report models see the following topics in SQL Server Books Online on msdn.microsoft.com.
*Designing and Implementing Report Models
*Working with Model Designer
*Generating Models Using Report Management Tools
*How to: Generate Report Models (Reporting Services in SharePoint Integrated Mode)
*How to: Publish a Report Model (Model Designer)

Help and Information

© 2010 Microsoft Corporation. All rights reserved.