MSSQL Server Reporting Services: The Authoring Phase: Overview Part I

Thursday Feb 26th 2004 by William Pearson
Share:

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) license.

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.

Introduction

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" solutions.

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.

Get 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 Services.

I 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 following:

  • An introduction to the Authoring phase;
  • A discussion 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 Connection;
    • Build the Query;
    • Design the Report Layout;
    • Add the Data;
    • Save the Reporting Project and Report Definition for the steps in Part II of our Authoring overview.

The Authoring Phase

Once business 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.

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

Import - We can import reports from MS Access and other sources.

While we 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:

  • The physical, intended report layout;
  • Data source connection details;
  • 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.

Layout Classification

Description

Tabular

A table-like report with a set number of columns

Matrix

A flexible report whose column number changes dynamically to fit the result dataset of the underlying query.

List (free-form)

A report that allows the author to arrange data "free-form" within its boundaries.

Chart

A report that presents a graphic (such as the classic pie and bar charts) representation.



Table 1: Report Layouts Available in Reporting Services

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

The Authoring phase within Reporting Services consists of the following steps:

  • Creation of the Report Project / Report Files;
  • Establishment of a Data Connection to the desired data source(s);
  • Creation of the Query that entrains the data into the report;
  • Introduction and arrangement of report controls into the report (to establish general layout);
  • Introduction of data to the report layout;
  • Setting properties to customize the layout, add formatting and default values, and specify grouping and other arrangements.

Let's begin a practice example to transit the steps of Authoring a report in Reporting Services.

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

Creating the Report Project

First, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

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

Click for larger image

Illustration 1: It All Begins in Microsoft Visual Studio .NET 2003 ...

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 2.


Illustration 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).


Illustration 3: The New Projects Dialog, with Business Intelligence Projects as a Project Type

5.  Click Business Intelligence Projects in the Project Types list.

6.  Click Report Project in the Templates list.

7.  Navigate to a location in which to place the Report Project files.

8.  Type the following into the Name box, leaving other settings at default:

RS002-1

The New Project dialog appears, with our addition, as shown in Illustration 4.


Illustration 4: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 5.


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:

10.  Right-click the Reports folder in Solutions Explorer.

11.  Select Add from the context menu that appears.

12.  Click Add New Item from the cascading menu, as shown in Illustration 6.

Click for larger image

Illustration 6: Select Add ---> Add New Item

The Add New Item dialog appears, as shown in Illustration 7.

Click for larger image

Illustration 7: The Add New Item Dialog - Initial View

13.  Click Report in the Add New Item dialog.

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


Illustration 8: The Design Environment - Data View Tab (Compacted)

The design 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.

Some will 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 expect.)

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.

1.  Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Illustration 9.

Click for larger image

Illustration 9: Select New Dataset in the Dataset Selector - Data Tab

As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab.

2.  Type the name of the computer housing the targeted OLTP database, AdventureWorks2000.

My server name, MOTHER1, appears in this article.

3.  Select the Use Windows NT Integrated security radio button.

4.  Select AdventureWorks2000 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 10.


Illustration 10: Data Link Properties Dialog - Connection Tab

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


Illustration 11: We Test Positive for Connectivity

6.  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 view.

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.

1.  Type (or cut and paste) the following basic SQL into the Query pane of the Report Designer.


-- RS002  Basic Practice Example Query
SELECT
    T1.Name AS 'Category', 
        T2.Name AS 'SubCategory', 
        T3.Name AS 'Product', 
        T3.ListPrice AS 'List Price'
FROM
    ProductCategory T1
INNER JOIN
    ProductSubCategory T2 ON T1.ProductCategoryID = 
        T2.ProductCategoryID
INNER JOIN
    Product T3 ON T2.ProductSubCategoryID = 
        T3.ProductSubCategoryID;

The Query pane appears as shown in Illustration 12.

Click for larger image

Illustration 12: SQL Query in the SQL Pane

2.  Click the Run icon in the toolbar, shown in Illustration 13, to execute the query.


Illustration 13: The Run Icon

The dataset appears in the Results pane, below the query, as partially shown in Illustration 14.


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


1.  Click the Layout tab.


The Layout tab presents the Design Surface, as depicted in Illustration 15.



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

2.  Click the Toolbox icon, atop the Report Designer window, (see Illustration 16) to open the Toolbox, as necessary.


Illustration 16: The Toolbox Icon

The Toolbox appears to the left of the Layout tab.

3.  Click Table, within the Toolbox.

The Toolbox, with Table icon selected, appears as depicted in Illustration 17.


Illustration 17: Toolbox, with Table Icon Selected

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


Illustration 18: The Table Appears on the Design Surface

5.  Select the Fields tab, below the Toolbox (shown in Illustration 19).


Illustration 19: Select the Fields Tab to Raise the Fields Window

The Fields window opens, replacing the Toolbox, to the left of the Design Surface, as shown in Illustration 20.


Illustration 20: The Fields Window

The fields we captured in our SQL query appear in the Fields window, for easy selection into our report.


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.


Click for larger image

Illustration 21: Right-Click the Top of the New Product Column


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.

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