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

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.
William Pearson
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