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

About the Series …

This is the third 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, but previous versions will work
similarly in most instances

Introduction

In our
last article, The
Authoring Phase: Overview Part I
, 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 creating 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 basic tabular report. First, we created the Report
Project
to house the Report File, which 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.

We are
now ready to pick up where we left off, and undertake the remaining steps of
our initial walkthrough of the Authoring phase. We will complete our exploration of the general Authoring
process, within the remaining activities of the hands-on practice example we
began in Part I, rejoining the tabular report as
we saved it, and taking the following steps:

  • Perform
    modifications to the layout of our existing report, RS02_Authoring;

  • Perform Grouping
    within the report;

  • Modify the
    underlying Dataset for the report;

  • Filter the Dataset;

  • Add Subtotals
    and a Total;

  • Set Properties
    and Formats for illustrative report components;

  • Preview our work, throughout the article,
    to confirm the effects of our activities.

As we
mentioned in the first half of this two-part article, our intent is to perform
an overview of Authoring. We will return to various activities we touch
upon here, as well as to many of the topics we explore within the subsequent
two phase overviews, as we get involved in creating reports to accomplish
illustrative business needs. I intend to make this a series on enterprise
reporting in the widest sense. I have wanted to do this for years as a BI
consultant, but never had a unified, common platform from whence I could show
techniques and methods to support robust and creative business intelligence.
Before the advent of Reporting Services, we would have had to introduce
multiple tools to accomplish sophisticated solutions in many cases, but those
scenarios are now a thing of the past.

A new
era in enterprise reporting has dawned, as industry and analysts alike will
soon proclaim. Stay tuned – it will happen sooner than many appreciate, and
the exceptional benefits to analysts and other information consumers will
become the new standard.

William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles