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:

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.

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.

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

RS002-1

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

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

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:

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

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.

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.

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

My server name, MOTHER1, appears in this article.

The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Illustration 10.

We receive a message box, indicating a successful test connection, as shown in Illustration 11.

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.