Black Belt Components: Support Simple Navigation with a Document Map

About the Series …

This
article is a member of the series MSSQL Server Reporting Services. The series is designed to
introduce MSSQL Server Reporting Services (“Reporting Services”),
presenting an overview of its features, with tips and techniques for real-world
use. For more information on the series in general, please see my initial Database Journal article,
A New
Paradigm for Enterprise Reporting
. For the software components, samples and tools needed to complete the
hands-on portion of this article, see BlackBelt
Administration: Linked Reports in Report Manager
, another article within this
series.

About the BlackBelt Articles …

As I state
in BlackBelt
Components: Manage Nulls in OLAP Reports
and other articles of this subs-series, the BlackBelt
articles represent an attempt to minimize the setup required in simply getting
to a point within an article where we can actually perform hands-on practice
with the component(s) or method(s) under consideration. I typically accomplish
this by using existing report samples or other “prefabricated” objects that
either come along as part of the installation of the applications involved, or
that are otherwise readily accessible to virtually any organization that has
installed the Microsoft business intelligence solution. While we will often
have to refine the sample involved (we will typically create a copy, to allow
the original sample to remain intact), to provide the specific backdrop we
need to proceed with the object or procedure upon which we wish to concentrate,
we will still save a great deal of time and distraction in getting to our
objective. In some cases, we will have to start from scratch with preparation,
but my intention with the BlackBelt articles will be to avoid
this, if at all possible.

For
more information about the BlackBelt articles, see the section
entitled “About the BlackBelt Articles” in BlackBelt
Components: Manage Nulls in OLAP Reports
.

Overview

In this article, we will get hands-on exposure to further
supporting simple navigation for our users with the Document Map feature
that is available within any implementation of Reporting Services. Our hands-on
practice will specifically focus on the enablement of the Document Map
within a sample OLAP report, containing a Matrix data region. We will
discuss the general concepts, and then set up a scenario within which we work
with the basic OLAP report to expose the steps involved. In establishing a Document Map within a report, we will:

  • Open the
    sample Report Server project, AdventureWorks Sample Reports, and
    ascertain connectivity of its shared Analysis Services data source;

  • Create, and
    then modify, a clone of an existing sample OLAP report, containing a Matrix
    data region, with which to perform our practice exercise;

  • Make structural
    enhancements to the clone report, to meet the business requirements of a
    hypothetical group of information consumers for “out-of-the-box” hierarchical
    navigation support via a simple Document Map;

  • Preview the
    report to ascertain the effectiveness of our solution;

  • Discuss the
    results obtained with the development techniques that we exploit.

Navigation Support Using the Document Map

Objective and Business Scenario

Throughout the
MSSQL Server Reporting Services
series, we have encountered, and
managed, varied navigation requirements within our reports for information
consumers. While we have examined many of these techniques within the context
of very specific needs, general ease of navigation within our reports is a
consideration in virtually any scenario. As reports become large, based upon
their scope (many scenarios do not allow for high level summarization – at
least not enough to contract the report to, say, a single page), and span what
might be many pages, we are challenged to make it easy for information
consumers to be able to quickly locate the information that they seek.

Reporting Services offers a basic, built-in feature
to address general navigation support. The Document Map provides an
intuitive tree that appears in the left margin of any deployed report for which
it is enabled. The tree that is automatically generated, based upon settings
that are easily established by even beginner report authors, provides an
outline of the report data, and serves, in effect, the function of a linked table
of contents. Data can be mapped at various levels via the tree, which can be
used to locate, by navigating to, the relevant information wherever it is
located in the report.

In the
following sections, we will perform the steps required to enable the Document
Map
feature within a sample OLAP report containing a Matrix
data region. To provide a report upon which we can practice the steps of our
hands-on exercise, we will begin with the Sales Reason Comparisons sample
report, based upon the AdventureWorks DW Analysis Services database that
is available with the installation of the MSSQL Server 2005 samples. The
Sales Reason Comparisons report is intended to present comparative
summary data for each of six classes of reasons behind the sales of Adventure
Works products
(the defined titles of the reasons for which sales are determined
to have taken place are Manufacturer, On Promotion, Other,
Price, Quality or Review). The data is presented for
specified Product Categories (a parameter is in place to allow selection
of the desired category at runtime), for each of the three Sales Territory
Groups
within the organization. For the purposes of our article, we will
say that the intended audience for the report is a group of information consumers
within the Accounting department of our client, the Adventure Works
organization.

To illustrate
the business need, let’s say that the information consumers have expressed the
need to be able to easily navigate within a specific report, which contains
information surrounding the reasons behind sales for the products that the
company offers. Because the report summarizes information about each of
several Sales Reasons, for a host of different products, the report is
significantly larger than the original single-page report upon which it is
based, as we shall see. The consumers have expressed overall satisfaction with
the report, but want to enhance it a bit to make it more efficient to use. They
would like to be able to quickly navigate to relevant points within the report’s
pages, from a central starting point atop the front page at runtime, based upon
either specific Sales Reasons or individual Products, either or
both of which might be the focus of a given examination of the report.

We
particularly note that the ability to navigate based upon more than one
hierarchical level is important to the consumers. Of additional importance, we
are told, is that the solution be as “out-of-the-box” as possible, as the
report authors want an inexpensive, quick solution that they can extend to
other reports that already exist, as well as to prospective reports, without
the involvement of their already backlogged IT department. As part of our
typical business requirements gathering process, we listen attentively to the
details, formulating, in the background, an idea of the steps we need to take
in modifying a copy of the report to produce the desired results. Once we
grasp the stated need, and confirm our understanding with the intended
audience, we begin the process of modifying the Sales Reason Comparisons report
to satisfy the information consumers.

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