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 ...
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
article, A New Paradigm for Enterprise Reporting.
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 2005 Reporting Services
Server 2005 Database Services
Server 2005 Analysis Services
AdventureWorks OLAP cube
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
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 ...
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
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
more information about the BlackBelt articles, see the section
entitled "About the BlackBelt Articles" in BlackBelt
Components: Manage Nulls in OLAP Reports.
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
Create a Data
Source View containing schema information upon which we will base the Report
Define the Report
Model within the Report Model Project;
Publish the Report
Model to make it available to select information consumers;
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.
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.
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
- 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.
Define a Data Source
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.
create a Data Source within our new RS028 Intro Report Model Project by taking the following steps:
Sources folder within the Solution Explorer.
Select Add New
Data Source from the context menu that appears, as depicted in Illustration
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.
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
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
Ensure that .Net
Providers\SqlClient Data Provider is selected in the Provider
selection box atop the Connection Manager dialog.
Type \ select
the appropriate server name / server name with instance into the Server name
We can substitute "localhost"
or "localhost\<instance name>" here, if it is
appropriate to our environment.
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).
AdventureWorks in the Select or enter a database name selector box,
within the Connect to a database section of the dialog.
Connection Manager dialog appears similar to that depicted in
Connection Manager Dialog in My Lab Environment
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
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
The Completing the
Wizard page appears.
13. Replace the name that appears in
the Data source name box with the following:
The Completing the
Wizard page appears, as shown in 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
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:
Source Views folder within the Solution Explorer.
Select Add New
Data Source View from the context menu that appears, as shown in Illustration
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.
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.
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.
tables in the Available objects list, by clicking the button marked ">>",
as depicted in 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
The Completing the
Wizard page appears.
name that appears in the Data source name box with the following:
The Completing the
Wizard page appears, as depicted in Illustration 21.
Completing the Wizard ...
to create the new Data Source View, and to dismiss the Data
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
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.
Report Models folder within the Solution Explorer.
Select Add New
Report Model from the context menu that appears, as depicted in Illustration
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.
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
created Data Source View is positioned as the default, and will serve us
in the creation of our new Report Model.
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
Illustration 26: The Select
Report Model Generation Rules Page Default Selections
The metadata is
combined into entities. The Collect Model Statistics page
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 ...
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 ...
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
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.
Yes to dismiss the message box.
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)
the Address entity on the Report Model tab, in the left Model
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
depicted in 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.
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
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.
that the Report Server information is properly configured for the
project, by right-clicking the new RS028 Intro Report Model Project
in Solution Explorer.
Properties from the context menu that appears, as depicted in Illustration
page appears, with the report server URL specified within the TargetServerURL
property setting, as shown in Illustration 36.
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.
changes as appropriate to the report server URL within the TargetServerURL
OK to accept changes, and to dismiss the Properties page.
the RS028 Intro Report Model project in Solution Explorer, once
Deploy from the context menu, as depicted in depicted in Illustration
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.
File --> Save All, to save our work to this point, as shown in Illustration
Illustration 38: Ensuring the
Correct Report Server Settings
File --> Exit, to leave the Business Intelligence Development Studio,
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.