Explore the steps of the Authoring
Phase in MSSQL Server Reporting Services. MSAS Architect Bill Pearson leads a
hands-on overview for developers, evaluators and "early converters" alike.
About the Series ...
This is the second article of the series MSSQL
Server 2000 Reporting Services. The
series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting
Services"), with the objective of giving a preview of its features, as
well as sharing my conviction in its role as a new paradigm in enterprise
reporting. As I advise clients on a more and more frequent basis these days,
this is the future in a big way. I hope you will consider my input valuable,
and that you will investigate closely the savings and advanced functionality
that will soon be available to anyone with an MSSQL Server 2000 (and beyond)
Note: In addition to the installation of Microsoft SQL Server 2000
Reporting Services, Version 1.0, together with Microsoft Visual
Studio.NET (required to access Report Designer for report creation),
Service Pack 3
updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis
Services, 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 2000 Reporting Services, MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("MSAS").
Any Microsoft Office components that appear within the series are
members of the MS Office 2003 suite.
In the first article of this series, MSSQL
Server 2000 Reporting Services: A New Paradigm for Enterprise Reporting,
I offered my opinion that Microsoft SQL Server 2000
Reporting Services ("Reporting
Services") will change the face of enterprise reporting that we have come
to know. I gave several reasons that I have come to this conclusion, not the
least of which is that the savings to large enterprises, like my clients, could
amount to an easy five-to-six figures annually - perhaps more. When we add to
the savings the fact that Reporting Services provides an integrated,
end-to-end set of tools for creating, managing, and viewing / delivering
reports, and that it does so with a scalable engine that supports server-based
hosting and processing of reports, it becomes a compelling case, indeed. Integration
with the Microsoft Office suite, together with the operating systems that most
of us have come to take for granted, means extended use of tools and services
we already have, freeing us from the costly redundancies (in time and money -
most notably in support) that occur when we implement one of the old-style "end-to-end"
One of the best ways
to "audition" Reporting Services, and an approach I am
advising to many of my current clients, is to simply get a copy and do a
parallel scenario with the systems that are currently filling the BI space
within the organization. Microsoft is offering free evaluation copies of the
components, which are easily installed in development. It's going to be hard
to justify simply renewing licensing with the current enterprise BI vendor, once
the word is out about the massive savings others are experiencing (CFO's and
CEO's do have breakfast together occasionally ...), without performing the due
diligence that is incumbent upon any technology professional.
there first, and have the testing in process when someone asks you about it! I mentioned in my last article that we are about to
witness a migratory phenomenon that will be inspiring in its scope and
magnitude. OLAP, as well as multi-dimensional and relational reporting, is
moving toward a commodity market, and away from the proprietary, expensive
empire that has been dominated for years by a handful of dominant players.
Stay in touch with this new wave, and ride it past the untimely disappearances
of the "specialists" who have grown lax in the comfort of the
yesterday's products. Listen to common sense versus the "What, Me
Worry?" press releases from the still-incumbent vendors - how many times
have we already seen this happen, in other facets of the IT environment?
The objective of this
series will be to assist such an exploration with overviews of the general
reporting cycle in this new application, followed by articles that address how
to accomplish the sorts of reporting objectives that I have spent the last ten
years helping clients to perform with enterprise applications like Cognos,
MicroStrategy, Business Objects / Crystal,
and the like. I will be taking scenarios that I have encountered within these
implementations and projects, and then show how they can be accomplished in Reporting
stated in the previous article that we would perform an overview of each of the phases of
the enterprise reporting life cycle in the next few articles. The overviews set
will serve as an introduction to later, more detailed articles, as well as to
provide general, "high level" information. In this article, we will
take a look at the Authoring phase, where the reporting cycle begins.
In this and the subsequent article, we will discuss the Authoring phase
in general, exploring the steps involved in authoring within an illustrative
practice example. Our
overview of the Authoring phase in this article will include the
introduction to the Authoring phase;
of the general Authoring process, as part of a hands-on practice example
whereby we create a tabular report, taking the following steps:
Create the Report
Project / Report File;
Establish the Data
Build the Query;
Design the Report
Add the Data;
Save the Reporting
Project and Report Definition for the steps in Part II of our
The Authoring Phase
requirements are defined, the first stage in the reporting life cycle is the Authoring
phase. As report designers within Reporting Services, we create a Report
Definition with an authoring tool. The out-of the-box tool for Reporting
Services is the Report Designer in Visual Studio .NET 2003. Authoring
is accomplished on a client PC, from which (ideally, once it meets the business
requirements) it is published to one or more report servers. From these
servers the report can be accessed by organizational information consumers.
Report Designer affords us three main ways to
create a report. These general approaches consist of the following:
Manual - We create a blank report, to
which we add one or more queries and layout specifications.
Assisted - We use
the Report Wizard, which automatically creates a table / matrix report
(see Table 1 below for a description of report layout options), based
upon information we provide in answer to prompts.
- We can import
reports from MS Access and other sources.
will examine each of these approaches to report creation over the life of the MSSQL
Server 2000 Reporting Services series, this article, as an introduction
to the Authoring phase, will undertake a practice example using the Manual
method to create a simple report, so as to focus more on Authoring generalities.
We will have many opportunities to explore the creation of reports to meet
specific business needs in future articles, where specific techniques and
strategies will be exposed.
The Report Definition
itself is a draft layout of a report, before the report is processed and delivered,
similar to the "blueprint" to which most of us have become accustomed
in our existing reporting applications. The Report Definition contains
several elements, including:
intended report layout;
Data source connection
Query information that defines the
source data that is extracted and presented in the report.
Reporting Services offers numerous layouts for reporting, as we shall
see throughout the series. Page-oriented (supporting what Microsoft refers to
as "traditional" reports, or "paper-based reports" in the
vernacular of the business population at large, who 1) maintains that the
avoidance of paper is highly desirable, while 2) continuing to kick off print
batch jobs at an ever-increasing rate) reports are supported; in addition, web-based,
"interactive" reporting is fully supported. Standard layout types
include the classifications detailed in Table 1.
Table 1: Report
Layouts Available in Reporting Services
A table-like report with a set number of columns
A flexible report whose column number changes
dynamically to fit the result dataset of the underlying query.
A report that allows the author to arrange data "free-form"
within its boundaries.
A report that presents a graphic (such as the classic
pie and bar charts) representation.
many of the more advanced enterprise reporting applications that are common
today, layouts can be combined, with the Report Definition
becoming a set of layout sections to contain the constituent types.
phase within Reporting Services consists of the following steps:
the Report Project / Report Files;
of a Data Connection to the desired data source(s);
the Query that entrains the data into the report;
and arrangement of report controls into the report (to establish general
of data to the report layout;
to customize the layout, add formatting and default
values, and specify grouping and other arrangements.
begin a practice example to transit the steps of Authoring a report in Reporting
Creating the Report Project and the Report Files
We will begin a basic
report, based upon a table in our data source. For this exercise, we will use
the AdventureWorks2000 OLTP database that ships / installs with Reporting
Creating the Report Project
First, we will launch Reporting
Services' Report Designer, found in Microsoft Visual Studio .NET 2003.
Navigate to the Microsoft
Visual Studio .NET 2003 in the Programs group, as appropriate. The
equivalent on my PC appears as shown in Illustration 1.
Select File -->
New from the main menu.
Click Project from the
cascading menu, as shown in Illustration 2.
2: Selecting a New Project
The New Project
dialog appears, as depicted in Illustration 3. We note that Business
Intelligence Projects appears in the Project Types tree, indicating
an installation of Reporting Services (the folder was added by the
installation of Reporting Services, as it established the Report Designer
in Visual Studio .NET).
3: The New Projects Dialog, with Business Intelligence Projects as a Project
Click Business Intelligence Projects in the Project Types list.
Click Report Project in
the Templates list.
Navigate to a location in which
to place the Report Project files.
Type the following into the Name
box, leaving other settings at default:
The New Project dialog appears,
with our addition, as shown in Illustration 4.
4: The New Projects Dialog, with Addition
Our new project
appears in the Solution Explorer (upper right corner of the Visual
Studio .NET interface), as we see in Illustration 5.
The New Project Appears in the Solution Explorer
We have now created a Report
Project, and are ready to proceed with creating a Report File.
Creating the Report File
Creating the Report File is straightforward, and handled
through the following steps:
Right-click the Reports folder
in Solutions Explorer.
Select Add from the
context menu that appears.
Click Add New Item from
the cascading menu, as shown in Illustration 6.
The Add New Item dialog appears, as shown in Illustration
Click Report in the Add
New Item dialog.
Click the Open button at
the bottom of the Add New Item dialog.
The design environment
opens. We see the Data, Layout and Preview tabs appear.
Our report has opened in Data View, as shown in Illustration 8.
8: The Design Environment - Data View Tab (Compacted)
environment that we see is known as Report Designer. As is probably
obvious, this is a busy place. Report Designer's strengths are legion,
and include local report processing and report-rendering capabilities. This
means that, from one central workspace, we can define layout, position content
(with robust drag-and-drop functionality) and preview the end results of our
efforts, as easily as we can use Print Preview to see what a Microsoft
Word or Excel document will look like after printing.
feel a bit intimidated at first blush - I have often heard from clients that
some staff thought Crystal Reports (as an example) too much like "programming,"
when compared to other products such as Cognos Impromptu, which they thought
far friendlier. While coding can be accomplished here, it is not required. But
the capability to add functionality through coding, at the same point that
drag-and-drop report authoring can take place, and to do so within a rich,
controls-laden environment will likely lead more "standard" report
writers to enthusiastically become at least sometime-coders, when they begin to
see the power that they can assemble in this workspace. Moreover, the immense
power of working from the Report Designer interface will win over many
report authors with only a few report building experiences.
We will build
a simple tabular report, to begin the authoring portions of our series, returning
to do more complex reports in later articles. The idea now is to get a feel
for the general steps, and to see how easy it is to replicate anything you can
do with the current tools that are in place in your organization. (For that
matter, migrating existing reports is significantly easier than one might
Setting Up Connection Information
Our next step
is to set up a Data Connection. The good news here is that Reporting
Services can connect with, and create the datasets it needs from, virtually
any ODBC or OLE DB-compliant data source (in addition to the
obvious MSSQL Server and MSAS data stores). .NET-based API's add the
potential for other data sources, assuming that you have a legacy, or otherwise
eccentric, scenario on your hands.
Let's set up
a Connection, and create a Dataset within our practice example.
Select New Dataset in
the Dataset selector at the top of the Data tab, as depicted in Illustration
As soon as we click the New Dataset selection, the Data
Link Properties dialog box appears, defaulted to the Connection tab.
Type the name of the
computer housing the targeted OLTP database, AdventureWorks2000.
My server name, MOTHER1,
appears in this article.
Select the Use Windows NT
Integrated security radio button.
within the Select the database on the server selector.
The settings on the Connection
tab of the Data Link Properties dialog should resemble those shown in Illustration
Data Link Properties Dialog - Connection Tab
Click the Test Connection
button to verify connectivity to the data source.
We receive a message
box, indicating a successful test connection, as shown in Illustration 11.
11: We Test Positive for Connectivity
Click OK to accept the
settings we have made, and to close the Data Link Properties dialog.
Report Designer next presents us with a dataset design tool, based
upon our newly connected source. Illustration 12 depicts the resulting
We are immediately
positioned to design our query, which brings us to the next step.
Creating the Query
Let's say, for purposes of our session in this article, that organizational
information consumers are requesting information about the products which our
organization sells. One requirement is for a simple list of products by
category and subcategory. We will provide the SQL that underpins
the report, and then take a look at some of the cosmetics. We will look at other
ways to generate the datasets later, so if you are not an SQL expert, just bear
with me for now.
Type (or cut and paste) the
following basic SQL into the Query pane of the Report Designer.
-- RS002 Basic Practice Example Query
T1.Name AS 'Category',
T2.Name AS 'SubCategory',
T3.Name AS 'Product',
T3.ListPrice AS 'List Price'
ProductSubCategory T2 ON T1.ProductCategoryID =
Product T3 ON T2.ProductSubCategoryID =
The Query pane
appears as shown in Illustration 12.
Click the Run icon in
the toolbar, shown in Illustration 13, to execute the query.
13: The Run Icon
The dataset appears in the Results pane, below the query,
as partially shown in Illustration 14.
14: Results Dataset Appears (Partial View)
We have now defined and tested a simple SQL query. Let's
proceed with the next step of the Authoring process, and design the Layout
of the report.
Designing the Report Layout
We have put a query in place to designate the dataset from
which the report will select the data it presents. We now need to turn to the
physical Layout of the report.
The prospective Layout of a report is another consideration
in the business requirements gathering phase, regardless of the platform upon
which the report is being constructed. We will assume, for the purposes of our
example that we have obtained a sketch from the information consumers, showing the
desired arrangement of the data that will appear in the report. Meeting with
the satisfaction of the intended audience in this regard is a significant focus;
the arrangement that they specify is likely based upon an existing report or
other design that has worked for them before, and which presents the
information in a manner that makes it easy to use.
The Layout tab
presents the Design Surface, as depicted in Illustration 15.
15: The Layout Tab and Design Surface
The Design Surface
of the Layout tab acts as a central "palette:" from here, we
start our blank report. It is on the Design Surface that we add Data
Regions, define the report Layout and perform other steps in our
creation efforts. As we can readily see, a blank report contains no data,
layout specifications, graphical or style elements, or other components. When
we start with a blank report, it is really blank.
Creating a blank report
affords us complete control in the design and creation process, which is
typically the best, and often the only, way to write an enterprise-grade
report. After we assemble the basic reports set (with underlying datasets and
other components) in our "toolbox," we can reuse them with a
flexibility and ease that is unparalleled by the proprietary enterprise
reporting systems of the (recent) past.
Click the Toolbox icon,
atop the Report Designer window, (see Illustration 16) to open
the Toolbox, as necessary.
16: The Toolbox Icon
The Toolbox appears to the left of the Layout tab.
Click Table, within the Toolbox.
The Toolbox, with Table icon selected, appears as
depicted in Illustration 17.
17: Toolbox, with Table Icon Selected
Click on the design surface
(a small table icon will appear in place of the usual mouse pointer).
The Report Designer renders a three-columned table,
straddling the Design Surface, as shown in Illustration 18.
18: The Table Appears on the Design Surface
Select the Fields tab,
below the Toolbox (shown in Illustration 19).
19: Select the Fields Tab to Raise the Fields Window
window opens, replacing the Toolbox, to the left of the Design Surface,
as shown in Illustration 20.
20: The Fields Window
The fields we captured
in our SQL query appear in the Fields window, for easy selection into
6. Click, and then drag, the Category field from the Fields window to the left-most cell in the Detail row of the newly created table.
7. Drag the SubCategory field to the middle cell in the Detail row, to the immediate right of the Category field.
8. Drag the Product field to the right-most cell of the Detail row, to the right of the SubCategory field.
9. Right-click the top of the right-most column (where we installed the Product field in the last step), as shown in Illustration 21, to open the context menu.
10. Select Insert Column to the Right on the context menu (circled in Illustration 21 above).
A new, empty column appears.
11. Drag the List_Price field to the Detail row cell in the newly appearing column, to the right of the Product field inserted above.
The table appears as shown in Illustration 22.
Illustration 22: The Table, with our Field Additions, on the Design Surface
12. Click the Preview tab atop the Report Designer.
Report Designer generates a preview of the report that appears, within a moment, as partially shown in Illustration 23.
Illustration 23: The Report Preview (Partial View)
We have thus generated our basic report, entraining the data from our source, via a query, and placing it within the Report Design using a table.
Completing the report requires several steps that we will take up in our next article, The Authoring Phase: Overview Part II. We will rejoin our report file within the article, to accomplish the setting of various properties, grouping and other arrangements to meet the needs of the information consumers for whom we have designed and begun our example report.
13. Select File --> Save Report1.rdl as ... from the main menu, as depicted in Illustration 24.
Illustration 24: Saving the Report Definition File
The Save File as dialog appears.
14. Navigate to a convenient storage location.
15. Type RS02_Authoring in the File name box of the dialog.
16. Click Save to save the file as named.
We see the file appear in the Solution Explorer pane (upper right corner of the Report Designer), within the Reports directory. This shows, as an aside, the manner in which we can author numerous reports within a project, based upon the same data source / query combination. We will see in later articles how useful this can be in creating similar reports in an enterprise reporting environment that have differences in filters, among a host of other attributes.
17. Select File --> Exit from the main menu.
18. Save the Project and associated files, when prompted.
We will return to this Report Project, and the Report File and associated components that it contains, in our next article, where we will conclude our overview of Authoring.
Summary and Conclusion ...
In this article, we began an overview of the first of the main phases of the enterprise reporting life cycle. We introduced the article with observations surrounding the objectives of the MSSQL Server 2000 Reporting Services series, as well as the objectives of the initial phase overview articles, and then discussed the Authoring phase in general. We began an exploration of the steps involved in Authoring a blank report, mentioning in passing the general ways of creating reports, each of which we will revisit numerous times in later articles.
After introducing the Authoring phase, we began a practice example in which we set out to create a tabular report. First, we created the Report Project to house the Report File that we created next. Within the Report File, we established a Data Connection, and then built a simple SQL query to use against our specified data source, the AdventureWorks2000 sample OLTP database. We then designed the report Layout, and, finally, added data from the dataset resulting from our query.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.