• Article

Lesson 2: Creating a Report Model Using Report Manager

Creating a report model in Report Manager 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 start Report Manager from a browser

  1. Open Microsoft Internet Explorer 6.0 or later.

  2. In the address bar of the Web browser, type the Report Manager URL. By default, the URL is https://localhost/reports.

To create a data source

  1. On the Home page, click New Data Source.

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

  3. In the Description box, type This is the Report Manager data source file for the AS cube.

  4. Verify that the Enable this data source check box is selected.

  5. In the Data sourcetype 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.

  6. In the Connection string box, type the following:

    Data Source=localhost;Initial Catalog="Adventure Works DW"

  7. Select Windows integrated security.

  8. 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

  1. On the SQL Server Reporting ServicesHome page, click RM_ASCube.

  2. Scroll down to the bottom of the page, and click Generate Model.

  3. In the Name box, type

    RM_ASCubeReportModel

  4. In the Description box, type:

    This is the AdventureWorks SSAS cube-based model created using Report Manager**.**

  5. To specify in which folder you want to save the model, click Change Location and select the folder that you want.

  6. 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 Analysis Services 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

  1. To return to the Home page, click Home.

  2. Click Report Builder.

    The Report Builder window appears.

  3. 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.

  4. 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.

  5. To exit Report Builder, on the File menu, click Exit.

Next Steps

Congratulations, you have successfully completed this tutorial. You created an SSAS cube-based report model by using Report Manager.