Lesson 2: Creating a Report Model Using Report Manager
New: 5 December 2005
Creating a report model in Report Manager or Management Studio is a two step process: you first need to create the data source, and then you can generate the model. A data source is a connection string that indicates the location of the data that is to be used by Reporting Services to create a report model and by the report server when running Report Builder reports. In this lesson, you will use Report Manager to create a data source and report model, and then view the report model in Report Builder.
To open Report Manager
Open your Internet browser.
In the Address bar, type the URL for your report server.
By default, the URL is http://<servername>/reports.
Press Enter.
The Reporting Services Home page opens in your browser.
To create a data source
On the Home page, click New Data Source.
In the Name box, type RM_ASCube.
Using this name will help differentiate the data source from any other data sources that might be saved to Report Manager already.
In the Description box, type This is the Report Manager data source file for the AS cube.
Verify that the Enable this data source check box is selected.
In the Connectiontype list, select Microsoft SQL Server Analysis Services.
Note
The connection type must be either Microsoft SQL Server or Microsoft SQL Server Analysis Services (SSAS) for it to be used as a model source. To create a report model based on a SQL Server database, select Microsoft SQL Server.
In the Connection string box, type the following:
Data Source=localhost;Initial Catalog="Adventure Works DW"
Select Windows integrated security.
Click OK.
A new data source is created and appears on the home page. You can move the data source to a folder on the report server by selecting the data source, clicking Move, selecting a folder on the report server, and then clicking OK. However, for this tutorial, leave the data source on the Home page.
To create a report model
On the SQL Server Reporting ServicesHome page, click RM_ASCube.
Scroll down to the bottom of the page, and click Generate Model.
In the Name box, type
RM_ASCubeReportModel
In the Description box, type:
This is the AdventureWorks SSAS cube-based model created using Report Manager.
To specify in which folder you want to save the model, click Change Location and select the folder that you want.
To create the report model, click OK.
The model is created in the folder that you specified or on the Home page by default. When the model is created, the General page of the Properties tab for the model appears. Your model is now deployed and you can begin to use it in Report Builder.
Important If the SSAS cube is changed, the model must be regenerated in order to reflect these changes. The model can be regenerated by selecting the model, clicking the General tab, and then clicking Regenerate Model.
To explore the model in Report Builder
To return to the Home page, click Home.
Click Report Builder.
The Report Builder window appears.
In the Getting Started pane, locate RM_ASCubeReportModel.
Notice that the there are several items nested below the RM_ASCubeReportModel report model. These nested items are perspectives. When a report model is created from an Analysis Services cube, the cubes in the SSAS database are displayed as perspectives in Report Builder. The top level model cannot be used to generate reports; instead, you must select one of the nested perspectives.
Select the Channel Sales perspective, and then click OK.
On the Explorer pane, notice that the name of the perspective that you selected appears at the top of the pane, and the cube contents are displayed as entities, folders, and fields.
To exit Report Builder, on the File menu, click Exit.
Next Steps
You have successfully created a model using Report Manager and opened it in Report Builder. In the next lesson, you will learn how to create a model using Management Studio. See Lesson 3: Creating a Report Model Using Management Studio.
See Also
Concepts
SQL Server 2005 Analysis Services Tutorial
Other Resources
Generating Models Using Report Management Tools
Working with Report Builder (Ad Hoc Reports)
Introducing SQL Server Management Studio
Developing Analysis Services Solutions and Projects
Working with Data Sources (Analysis Services)
Working with Data Sources How-to Topics (SSAS)