Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 18, 2006

Black Belt Components: Support Simple Navigation with a Document Map

By William Pearson

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.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM