Report Model Samples

  • Article

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

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. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

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 AdventureWorks2008R2 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 (data warehouse)
  • 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 AdventureWorks 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 AdventureWorks model in Model Designer

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

  2. On the File menu, point to Open, and then click Project/Solution.

  3. In Open Project, navigate to the network location where you saved the model files.

  4. Select the Adventure Works 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 AdventureWorks model from Model Designer

  1. In Solution Explorer, right-click the Adventure Works Model project.

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

To assign permissions to the AdventureWorks model in Object Explorer

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2 and then click SQL Server Management Studio.

  2. When prompted, connect to the server where the report model is located.

  3. In Object Explorer, navigate to the model, right-click the model, and click Properties.

  4. In the Model Properties page, click Model Security.

  5. Select the Secure individual model items independently of this model check box.

  6. Click Use these roles for each group or user account.

  7. Click Add Group or User.

  8. Select the group or user for which you are creating the role assignment and then click OK.

Opening the AdventureWorks 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 it in Report Builder and create reports.

To open the Adventure Works model sample in Report Builder

  1. In your Web browser, type the URL for your report server in the address bar. By default, the URL is http://<webservername>/reports.

  2. Click Report Builder.

  3. In the Getting Started pane, select AdventureWorks2008R2, and then click OK.

    Your model is displayed in the Explorer pane.

See Also

Other Resources

How to: Publish a Report Model (Model Designer)
Granting Permissions on a Native Mode Report Server
Considerations for Installing SQL Server Samples and Sample Databases

Help and Information

Getting SQL Server 2008 R2 Assistance