Report Model Samples

  • Article

With SQL Server 2005 Reporting Services, you can build semantic models based on SQL Server databases 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.

The AdventureWorks sample report model is shipped with SQL Server 2005. The samples are not installed automatically during setup. For instructions about how to install the samples, see Installing Samples in SQL Server 2005 Books Online.

The samples are installed by default at:

<drive>:\Program Files\Microsoft SQL Server\90\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 AdventureWorks 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 2005, including the following components:
    • Reporting Services
    • SQL Server Management Studio
    • Business Intelligence Development Studio
    • One sample database: AdventureWorks (data warehouse)
  • Microsoft .NET Framework 2.0 on the computer(s) that you are using to run Model Designer and Report Builder.
  • Visual Studio 2005.

In addition, make sure that you have permissions to:

  • Retrieve data from the AdventureWorks 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 SQL Server 2005 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 SQL Server 2005 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 AdventureWorks, and then click OK.

    Your model is displayed in the Explorer pane.

See Also

Tasks

Report Builder Report Samples
Installing Samples

Other Resources

Installing AdventureWorks Sample Databases and Samples
Adventure Works Cycles Business Scenarios
How to: Publish a Report Model [Model Designer]
Managing Permissions and Security for Reporting Services

Help and Information

Getting SQL Server 2005 Assistance