Report Builder: Creating a Report Model

Monday Apr 17th 2006 by William Pearson
Share:

Provide "self-serve" reporting and data exploration to information consumers with Report Builder. Architect Bill Pearson leads hands-on practice in creating a basic Report Model within Business Intelligence Development Studio.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with tips and techniques for real-world use. For more information on the series, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a presentation component within an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise business intelligence systems, such as Cognos, Business Objects / Crystal, MicroStrategy, Hyperion, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Note: To follow along with the steps we undertake within the articles of this series, the following components, samples and tools are recommended, and should be installed / accessible, according to the respective documentation that accompanies MSSQL Server 2005:

Server Requirements

  • Microsoft SQL Server 2005 Reporting Services

  • Microsoft SQL Server 2005 Database Services

  • The AdventureWorks sample databases

  • Microsoft SQL Server 2005 Analysis Services

  • The AdventureWorks OLAP cube

Client Requirements

  • Microsoft Internet Explorer 6.0 with scripting enabled

  • Business Intelligence Development Studio (optional)

Sample Files

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005. The samples with which we are concerned include, predominantly, the Adventure Works DW database. This database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Note: Current Service Pack updates are assumed for the operating system, along with the applications and components listed above and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

About the BlackBelt Articles ...

As we have stated in earlier BlackBelt articles, one of the greatest challenges in writing tutorial / procedural articles is creating each article to be a freestanding document that is complete unto itself. This is important, because it means that readers can complete the lesson without reference to previous articles or access to objects created elsewhere. When our objective is the coverage of a specific technique surrounding one or more components of a report, a given administrative function surrounding all reports, and other scenarios where the focus of the session is not the creation of reports, per se, challenges can arise because a report or reports often has to be in place before we can begin to cover the material with which the article concerns itself.

The BlackBelt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) under consideration. We will attempt to use existing report samples or other "prefabricated" objects that either come along as part of the installation of the applications involved, or that are readily accessible to virtually any organization that has installed the application. While we will often have to make modifications to the samples involved (we will actually create a copy, to allow the original sample to remain intact), to refine it to provide the backdrop we need to proceed with the object or procedure upon which we wish to concentrate, we will still save a great deal of time and distraction in getting to our objective. In some cases, we will still have to start from scratch with preparation, but my intention with the BlackBelt articles will be to avoid this, if at all possible.

For more information about the BlackBelt articles, see the section entitled "About the BlackBelt Articles" in BlackBelt Components: Manage Nulls in OLAP Reports.

Overview

As I have discussed in many past articles, ad hoc reporting capabilities are highly valued by information consumers. While we have, up until this article, focused on adding interactive features, such as parameterization, and ad hoc sorting and other conditional formatting features in the reports we develop within Reporting Services, one feature of Reporting Services 2005 is bound to meet with popularity among administrators, as well as among analysts and other information consumers: the Report Builder.

Report Builder provides a means for consumers to explore and find information without having to understand the underlying data source structures. Report Builder is fully integrated with SQL Server Reporting Services and delivers ad hoc reporting support quite similar to that found in Cognos Query, among other enterprise reporting offerings. Because it uses familiar Microsoft Office paradigms, the learning curve for using Report Builder is relatively minimal. In effect, end users leverage a report layout template that contains predefined data regions, in combination with the Report Model, which contains report items such as data fields, which are ideally designed into the model based upon business requirements gathering. The users drag and drop the report items onto the data regions within the template, apply filters, and so forth to refine the eventual report display to fit their needs.

Consumers can create new fields / calculations based on the data in the Report Model, and preview, print, and publish their reports. They can also export them to Microsoft Office Excel and several other formats - retaining formatting, etc., in a way I have yet to see even approximated within the export capabilities of Cognos, or other enterprise applications.

Report Builder can also be used in an "Explorer" mode, whereby consumers can interactively browse the related data within the Report Model. In this mode, Report Builder generates "clickthrough reports" automatically, allowing consumers to follow navigation paths that exist within the Report Model. As long as a relationship exists from a given "current position," consumers can continue to click through. Clicking through triggers the automatic generation of queries (information is passed about the data items the consumer is placing in the report) based upon the "context" of the location of the data that the user selects to use in the report.

Because Report Builder reports are saved as RDL, they can be opened and modified using the advanced programming capabilities in Reporting Services' Report Designer, if required, and Report Builder reports are managed, secured, and delivered using the same methods and APIs used to manage, secure, and deliver other Reporting Services reports. It is easy to control who can use or access a given report, and, if Reporting Services' role-based security is implemented correctly, we can effectively manage who can create and edit reports in Report Builder (we can create customized roles for other considerations).

In this article, we will focus largely upon the design and creation of the Report Model. We will concentrate on the use of the Report Builder from the consumer perspective in articles where we focus specifically within that context. It is in those articles that we will focus upon end user access and use, including report creation and publication using the tool. Our purpose in this session will be to get some hands-on exposure to the creation of a Report Model. As a part of our examination of the steps involved, we will:

  • Create a new Report Model Project within the Business Intelligence Development Studio;
  • Define a Data Source containing the information Report Builder needs to connect to a database;
  • Create a Data Source View containing schema information upon which we will base the Report Model;
  • Define the Report Model within the Report Model Project;
  • Publish the Report Model to make it available to select information consumers;
  • Discuss, at appropriate junctures, the results obtained within the development techniques that we exploit throughout our practice session.

Create a Report Model

Objective and Business Scenario

A Report Model represents an abstract layer consisting of metadata about an underlying physical database. The published model enables information consumers to assemble reports without understanding query languages (or queries in general), databases (in general, or the "plumbing and tanks" that store and move their own), and so forth. Report Models also provide a means whereby we can provide just the data we wish for consumers to be able to access – and no more. A Report Model is made up of entities, or "masters" of sorts, which consist of report items that are related in nature. The model allows us to both group and name the items in a way that makes sense to the end users, alleviating the need for them to understand database names and structures that might not be intuitively recognizable to them.

Report Model entities are quite flexible. Combined with a forehanded approach to gathering business requirements, and coupled with effective (and imaginative) planning and design of the model, entities can make data exploration and report building easy and efficient for even inexperienced consumers. (The importance of Report Model design in the delivery of a quality user experience is not to be underestimated). Entities present the report items, as well as predefined relationships between entities and predefined calculations (consumers can also create new fields and calculations based upon the data we have defined within the Report Model), within user-friendly groups within Report Builder. As we noted earlier, consumers can preview, print, publish and export their reports from Report Builder, too.

Hands-On Procedure

We will begin by creating a new Report Model Project within the Business Intelligence Development Studio, wherein we will create a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of our session more efficiently.

Preparation

Create a New Report Model Project

We begin our preparation with the creation of a new Report Model Project. Report Model Projects contain a Data Source definition file, a Data Source View definition file, and a Model definition file. We will gain exposure to each of these components in the respective section of the procedure that follows.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 1.


Illustration 1: Opening SQL Server Business Intelligence Development Studio

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.


Illustration 2: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)

4.  Close the Start Page tab.

5.  Select File --> New on the Visual Studio main menu.

6.  Select Project from the cascading menu, as depicted in Illustration 3.


Illustration 3: Beginning a New Project ...

The New Project dialog appears.

7.  Select Business Intelligence Projects in the Project types pane of the dialog.

8.  Select Report Model Project in the Templates pane, as shown in Illustration 4.


Illustration 4: Select Report Model Project

NOTE: The templates that appear in your template pane may differ, depending upon which SQL Server 2005 components are installed in your environment, as well as whether additional templates (for Business Intelligence Projects or other types of projects that can be created) have been defined in Visual Studio.

9.  Change the project Name (currently displaying a default) to the following:

RS028 Intro Report Model

The Solution Name changes to match the project Name by default.

10.  Navigate to a convenient location to store the Project and Solution files, modifying the Location box accordingly (Visual Studio will create a directory based upon our input here).

The New Project dialog appears similar to that depicted in Illustration 5.


Illustration 5: The New Project Dialog, with our Input

11.  Click OK to accept our input and to create the new Report Model Project.

The New Project dialog closes, as Visual Studio creates the project. Both solution and project RS028 Intro Report Model appears in the Solution Explorer as shown in Illustration 6.


Illustration 6: The New Report Model Solution and Project Appear

Solution Explorer presents a tree view of the objects contained in the solution, the primary management unit within the Business Intelligence Development Studio, which can contain multiple projects. Individual projects, such as the one we have created, themselves contain folders for the objects that can be defined for projects of a similar type.

As we can see in the present instance, the Report Model Project template, upon which our RS028 Intro Report Model Project was based, contains the following folders:

  • Data Sources
  • Data Source Views
  • Report Models

Anytime we create a new Report Model Project, the Solution Explorer and the Properties window are visible and docked, as we see them in the present case. The following windows are hidden and docked, initially, and appear on the right, bottom, or left side of the development environment, depending upon where they are docked:

  • Server Explorer
  • Toolbox
  • Task List
  • Error List

Viewing any of the hidden windows is as simple as positioning the pointer over it – its mouseover behavior is to reappear. We can also click the Auto Hide button (depicted for the Solution Explorer in Illustration 7) to hide or unhide a window.


Illustration 7: The Auto Hide Button – Solution Explorer

Finally, we can always open a closed window by selecting it from the View menu atop the development environment, as shown in Illustration 8.


Illustration 8: Reopen Closed Windows from the View Menu ...

Having created a new Report Model Project, we are ready to define a Data Source and Data Source View.

Procedure

Define a Data Source

Defining a Data Source, as we have discovered in past articles, is typically the first step we take with most BI Projects – be they Analysis Services, Report Model, or other varieties. A project can have multiple Data Sources, within which we are essentially defining the connection string used to connect to the actual source of our data. While we can define connections to source databases on remote computers in the business environment, we will, in this article, be working with one of the sample databases that are available to us when we install MSSQL Server 2005, the AdventureWorks database.

Let's create a Data Source within our new RS028 Intro Report Model Project by taking the following steps:

1.  Right-click Data Sources folder within the Solution Explorer.

2.  Select Add New Data Source from the context menu that appears, as depicted in Illustration 9.


Illustration 9: Initial Step in Defining a New Data Source

The Data Source Wizard appears, opening with the Welcome to the Data Source Wizard page, as shown in Illustration 10.

Click for larger image

Illustration 10: Welcome to the Data Source Wizard ...

3.  Click Next.

The Select how to define the connection page appears. This page allows us to define a Data Source based upon a new or existing connection. (It also allows us to base a Data Source upon a connection that has been previously created, as long as the existing Data Source definition exists within the same project, within another project housed in the same solution, or from within another Report Model Project.)

The Select how to define the connection page appears.

4.  Ensuring that the radio button to the left of Create a data source based on an existing or new connection is selected, click New.

The Connection Manager dialog appears.

5.  Ensure that .Net Providers\SqlClient Data Provider is selected in the Provider selection box atop the Connection Manager dialog.

6.  Type \ select the appropriate server name / server name with instance into the Server name box.

We can substitute "localhost" or "localhost\<instance name>" here, if it is appropriate to our environment.

7.  Select / complete the appropriate authentication choice for your environment in the section labeled Log on to the server (I selected the radio button to the left of Use Windows Authentication in the lab environment in which I prepared this article).

8.  Select AdventureWorks in the Select or enter a database name selector box, within the Connect to a database section of the dialog.

The Connection Manager dialog appears similar to that depicted in Illustration 11.


Illustration 11: Connection Manager Dialog in My Lab Environment

9.  Click the Test Connection button (in the bottom left corner of the Connection Manager dialog) to ascertain that we have a valid connection.

We receive a positive confirmation message, Test connection succeeded, assuming settings in the dialog are in accordance with the above steps, as appropriate for our own environments. The confirmation message box appears as shown in Illustration 12.


Illustration 12: Confirmation Message Indicating Successful Connection Test

10.  Click OK to dismiss the message box and return to the Connection Manager dialog.

11.  Click OK on the Connection Manager dialog to accept our settings and create the data connection.

We return to the Select how to define the connection page of the Data Source Wizard, which appears similar to that depicted in Illustration 13.


Illustration 13: Select How to Define the Connection Page with New Data Connection

12.  Click Next.

The Completing the Wizard page appears.

13.  Replace the name that appears in the Data source name box with the following:

RS028_Adventure Works

The Completing the Wizard page appears, as shown in Illustration 14.


Illustration 14: Completing the Wizard ...

14.  Click Finish to create the new Data Source, and to dismiss the Data Source Wizard.

The Wizard closes, and the new Data Source appears in the Data Sources folder within the Solution Explorer as depicted in Illustration 15.


Illustration 15: The New Data Source Appears in the Solution Explorer

Having created a Report Model Project, and defined a Data Source, we are ready, to define a Data Source View.

Define a Data Source View

Our next steps surround the creation of the Data Source View, a procedure that is customary at this stage in both Analysis Services and Report Model Projects. It is important to realize, as we work with a "live" data connection that we have defined, that we could certainly continue our development efforts with the metadata without an open connection. The Data Source View provides a single, unified view of the metadata from the tables and views that concern us within our project.

NOTE: For more information on Data Source Views, see my article Introduction to MSSQL Server Analysis Services: Introducing Data Source Views at Database Journal.

To define a Data Source View, we will take the following steps:

1.  Right-click Data Source Views folder within the Solution Explorer.

2.  Select Add New Data Source View from the context menu that appears, as shown in Illustration 16.


Illustration 16: Initial Step in Creating a New Data Source View

The Data Source View Wizard appears, opening with the Welcome to the Data Source View Wizard page, as depicted in Illustration 17.

Click for larger image

Illustration 17: The Initial Page of the Data Source View Wizard

3.  Click Next.

We arrive at the Select a Data Source page, where we see the Data Source we created in the previous section, RS028_Adventure Works, in the Relational data sources list box on the left side of the page, as shown in Illustration 18.


Illustration 18: Our New Data Source Appears in the Relational Data Sources

Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise. A Data Source View for a Report Model Project, unlike a Data Source View for an Analysis Services Project, can only reference a single Data Source.

4.  Click Next.

We arrive at the Select Tables and Views page, where we see the various tables of the Adventure Works data source appear in the Available objects list box on the left of the page.

5.  Select all tables in the Available objects list, by clicking the button marked ">>", as depicted in Illustration 19.


Illustration 19: Selecting All Tables for Inclusion in the Data Source View ...

The tables in the Available objects list box move to the Included objects list on the right half of the page, as shown in Illustration 20.


Illustration 20: All Tables Selected for Inclusion in the Data Source View

6.  Click Next.

The Completing the Wizard page appears.

7.  Replace the name that appears in the Data source name box with the following:

RS028_Adventure Works

The Completing the Wizard page appears, as depicted in Illustration 21.


Illustration 21: Completing the Wizard ...

8.  Click Finish to create the new Data Source View, and to dismiss the Data Source Wizard.

The Wizard closes, and the new Data Source View appears in the Data Source Views folder within the Solution Explorer as shown in Illustration 22.


Illustration 22: The New Data Source View Appears in the Solution Explorer

We have defined a Data Source View that contains the metadata for the entire Adventure Works sample database. Naturally, we might have only entrained select tables, based upon the business requirements of the intended audience of the model, but for purposes of our practice session, this will serve to illustrate the concepts. We will next define our Report Model based upon some of this metadata, which designated information consumers can then use within the Report Builder.

Define the Report Model

Report Models themselves represent an abstract layer, as we noted earlier, within which we can create business-based entities from which consumers can select report items. Let's create a Report Model next, and then discuss publishing it for consumer use.

1.  Right-click the Report Models folder within the Solution Explorer.

2.  Select Add New Report Model from the context menu that appears, as depicted in Illustration 23.


Illustration 23: Initial Step in Creating a New Report Model

The Report Model Wizard appears, opening with the Welcome to the Report Model Wizard page, as shown in Illustration 24.

Click for larger image

Illustration 24: The Initial Page of the Report Model Wizard

3.  Click Next.

We arrive at the Select Data Source View page, where we see RS028_Adventure Works, the Data Source View we created in the previous section, displayed within the Available data source views list box, as depicted in Illustration 25.

Click for larger image

Illustration 25: The RS028_Adventure Works Data Source View Appears as Available

Our newly created Data Source View is positioned as the default, and will serve us in the creation of our new Report Model.

4.  Click Next.

We arrive at the Select report model generation rules page, where we see the rules upon which automatic metadata generation will be based. For purposes of the current session, we will allow selection of all the defaults, as shown in Illustration 26.


Illustration 26: The Select Report Model Generation Rules Page – Default Selections

5.  Click Next.

The metadata is combined into entities. The Collect Model Statistics page appears next.

6.  Ensure that the checkbox to the immediate left of Update model statistics before generating is selected, to ensure that database statistics upon which the Report Model depends are updated from the Data Source View, as depicted in Illustration 27.


Illustration 27: Updating Statistics Prior to Generation of the Model ...

7.  Click Next.

We arrive at the Completing the wizard page, where we see that, by default, the Report Model has been given the same name as the underlying Data Source View, RS028_Adventure Works, as shown in Illustration 28.


Illustration 28: Completing the Wizard Page ...

8.  Click Run.

The statistics update occurs, and then the rules that were selected on the Select report model generation rules page are applied within the Report Model creation process. After all passes are completed, the status messages come to an end, and the Finish button becomes enabled, as depicted in Illustration 29.


Illustration 29: Processing Completes ... The Model is Generated

9.  Click Finish.

The Completing the wizard page, closes, and we are greeted with a Visual Studio message box (shown in Illustration 30) alerting us to the fact that modifications to the Data Source View have occurred outside the source editor.


Illustration 30: Microsoft Visual Studio Message Box Alerts Us To Changes ...

This alert is due to the statistics update (which we performed for practice, not because the Data Source View was likely to need an update - we had just created it shortly before). In any event, we can simply dismiss the message box without further ado.

10.  Click Yes to dismiss the message box.

The message box closes, and the new Report Model appears in the Report Models folder, within the Solution Explorer as depicted in Illustration 31.


Illustration 31: The New Report Model Appears within the Solution Explorer

Moreover, we see that the entities within the new Report Model appear on the new Report Model tab of the Designer, as shown in Illustration 32.


Illustration 32: Report Model Objects Appear within the Designer (Partial View)

11.  Click the Address entity on the Report Model tab, in the left Model tree.

We note that a list of fields, folders and roles (where appropriate), for an entity we select, is displayed in the middle pane of the tab. Moreover, properties for the selected entity appear in the Properties pane, as is the general case with objects throughout the Visual Studio environment, as depicted in Illustration 33.


Illustration 33: Selecting the Entity Displays Member Objects and Properties...

We can define new folders, source fields, expressions, roles and filters to further add to the richness of our Report Model. We do this by simply right-clicking within the pane, selecting New, and then selecting the object type from the cascading menu, as shown in Illustration 34.


Illustration 34: Embellishing the Model is a Right-click Away ...

We will work further with Report Models in subsequent articles within our MSSQL Server Reporting Services series. For the purposes of this introductory session, we will conclude with the steps required to publish our new Report Model Project in the next section.

Publish the Report Model Project

We "deliver" our design efforts to the information consumers by publishing the Report Model Project to a report server. We will publish the components of our project by taking the following steps.

1.  Ensure that the Report Server information is properly configured for the project, by right-clicking the new RS028 Intro Report Model Project in Solution Explorer.

2.  Select Properties from the context menu that appears, as depicted in Illustration 35.

Click for larger image

Illustration 35: Ensuring the Correct Report Server Settings ...

The Properties page appears, with the report server URL specified within the TargetServerURL property setting, as shown in Illustration 36.

Click for larger image

Illustration 36: The Target Server URL Specification ...

By default, the Business Intelligence Development Studio general setting is as shown above, http://localhost/reportserver. Once we have the report server aligned as we desire, deployment is a simple exercise.

3.  Make changes as appropriate to the report server URL within the TargetServerURL property setting.

4.  Click OK to accept changes, and to dismiss the Properties page.

5.  Right-click the RS028 Intro Report Model project in Solution Explorer, once again.

6.  Click Deploy from the context menu, as depicted in depicted in Illustration 37.


Illustration 37: Deploying the Report Model Project to the Designated Report Server ...

The Model Project uploads successfully to the report server (we can view it via Report Manager to verify the upload). To make the new model available to our information consumers, we must provide access via role assignments. We will take up these steps for using Report Builder, as well as the details of various in-depth techniques and nuances in Report Model design, in separate articles within the MSSQL Server Reporting Services series.

7.  Select File --> Save All, to save our work to this point, as shown in Illustration 38.


Illustration 38: Ensuring the Correct Report Server Settings

8.  Select File --> Exit, to leave the Business Intelligence Development Studio, when ready.

Conclusion ...

In this article, we introduced a powerful new "self-serve" reporting apparatus, the Report Builder. After initially discussing Report Builder and its features, we shifted to the focus of our hands on practice session surrounding the design and creation of the Report Model, noting that we concentrate on the use of the Report Builder from the consumer perspective in articles where we provide practice within that context. In gaining hands-on exposure to the creation of a Report Model, we created a new Report Model Project within the Business Intelligence Development Studio, and then defined a Data Source containing the information Report Builder needs to connect to a database. We next created a Data Source View within our project to contain schema information upon which we based our next addition, and the centerpiece of our efforts, the Report Model.

We then defined the Report Model within the Report Model Project. Once this was completed, we deployed our Report Model Project as a means of publishing the Report Model to make it available to our targeted information consumer audience. Throughout the steps of our practice session, we discussed, at appropriate junctures, various settings and techniques involved in achieving our objectives.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved