Black Belt Components: Support Simple Navigation with a Document Map

Monday Dec 18th 2006 by William Pearson
Share:

BI Architect Bill Pearson exposes the use of the out-of-the-box Document Map feature in Reporting Services. In this article, we get hands-on practice in providing navigation support within an OLAP report, while we consider scenarios where the Document Map can be a convenient feature.

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.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, into which we can implement enhancements from the perspective of the SQL Server Business Intelligence Development Studio. The focus of our efforts is the addition of navigation support via the Reporting Services Document Map feature within a report (the mechanics behind enabling the feature, not the design of the report itself). Because of time limitations, we will be working with a simple, pre-existing sample report – in reality, the business environment may require significantly more sophistication. The process of setting up a Document Map is the same in real world scenarios, with perhaps a more complex set of underlying considerations involved. I often encounter the need to add more sophisticated navigation capabilities within client environments, which might include customization of the Document Map feature that comes with Reporting Services, combinations of the rudimentary Document Map feature with other navigation support, or even fully customized solutions based upon additional programming.

We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to work within the focus of the article more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) the Microsoft SQL Server 2005 integrated business intelligence solution. Creating a “clone” of the report means we can make changes to our report while retaining the original sample in a pristine state – perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Making preparatory modifications, and then making the enhancements to the report to add the functionality that forms the subject of our lesson, can be done easily within the Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project

For purposes of our practice session, we will open the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 integrated business intelligence solution. We will complete our practice session within the sample project so as to save the time required to set up a development environment from scratch within the Business Intelligence Development Studio.

To open the AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:

Open the Sample Report Server Project

Ascertain Connectivity of the Shared Data Source

Let’s ensure we have a working data source. Many of us will be running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone. (The default server for the Adventure Works DW project sample’s connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default.)

If you do not know how to ascertain or modify connectivity of the Analysis Services data source, please perform the steps of the following procedure in the References section of my articles index:

Ascertain Connectivity of the Analysis Services Data Source

Create a Copy of the Sales Reason Comparisons Report

We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercise in meeting the business requirements of our Adventure Works colleagues. Creating a “clone” of the report means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes. (Examples might include using the original to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Reporting Services, and other components of the Microsoft integrated business intelligence solution in general.)

If you do not know how to create a copy of an existing report, please perform the steps of the following procedure in the References section of my articles index:

Create a Copy of a Sample OLAP Report

Once we have a clone OLAP report file within our Reporting Services 2005 Project, we can proceed, in the next section, to make modifications for our subsequent practice session.

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective is to begin the session with a simple OLAP report that contains significant depth to render navigation useful. The report, as it currently exists, is a simple, single-page presentation; we will add a bit more structure and volume to generate a presentation whereby we can benefit from an ability to easily navigate between sections and pages. Once we have added the data and levels to provide a richer presentation, we can commence our practice steps within the focus of this article, the enablement, and use of the Document Map.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.


Illustration 1: Opening the New Report ...

DBJ_OLAP_Report.rdl opens in Layout view, where we will start with the Data tab.

3.  Click the Data tab.

We enter the Data tab, where we will make a simple adjustment to the report’s primary dataset. We will accomplish this from the perspective of the MDX Query Builder, the main components of which are labeled in Illustration 2 below.


Illustration 2: The MDX Query Builder

4.  Within the Dataset selector, ensure that the ProductData dataset (the default), is selected.

5.  Click the Execute button (“!”) to generate the currently specified dataset in the Data pane, as depicted in Illustration 3.


Illustration 3: Execute the Query to Populate the Data Pane

The Data pane populates, and appears much as that shown above. It is here that we will make a modification to the query specification to support a richer report layout, for the reasons we have described earlier.

6.  Within the Metadata pane, expand the Product dimension by clicking the “+” sign to its immediate left.

7.  Click the Product attribute hierarchy that appears within the Product dimension folder’s exposed contents, to select it.

8.  Drag the Product attribute hierarchy into the Data pane, dropping it at a point between the Sales Territory Group column and the Internet Order Quantity column (a red “beam” appears at the perspective drop point), as depicted in Illustration 4.


Illustration 4: Dropping the Product Attribute Hierarchy into the Data Pane ...

The Product attribute hierarchy appears (with its associated data members) between the Sales Territory Group and the Internet Order Quantity columns, as partially shown in Illustration 5.


Illustration 5: The New Data Column Appears (Partial View)

We now have a richer dataset (an additional new level, containing many new members), upon which we can modify the existing report to present significantly more information - giving us a presentation within which we can support navigation in a more meaningful context than we might even find necessary with a single-page report. Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.

9.  Click the Layout tab, as depicted in Illustration 6.


Illustration 6: Click the Layout Tab

10.  On the Layout tab, within the Datasets pane, expand the topmost dataset, ProductData.

We see the new Product attribute hierarchy, as shown in Illustration 7.


Illustration 7: The Newly Added “Product” in the Datasets Pane

11.  On the canvas area of the Layout tab, Click inside the upper left corner of the Matrix Data Region to give the Matrix the focus.

12.  Right-click the upper left-hand corner of the Matrix Data Region (the gray square).

The gray column and row bars disappear, as a light, patterned outline forms around the Matrix Data Region, and the context menu appears.

13.  Select Properties from the context menu, as depicted in Illustration 8.


Illustration 8: Select Properties from the Context Menu ...

The Matrix Properties dialog appears, defaulted to the General tab.

14.  Click the Groups tab.

15.  Click Add ... to the right of the Rows list, in the upper half of the tab, as shown in Illustration 9.


Illustration 9: Adding a New Group ...

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

16.  Atop the General tab, replace the default entry that appears in the Name box with the following name:

matrix1_Product

17.  Click the currently empty top row in the Expression list within the Group on section of the tab, to enable the selector.

18.  Select =Fields!Product.Value within the selector, as depicted in Illustration 10.


Illustration 10: Selecting a “Group On” Expression for a New Group ...

19.  Click OK, to accept our selection, and to close the Grouping and Sorting Properties dialog.

We return to the Groups tab, where we see the new matrix1_Product entry (currently highlighted) within the Rows list, underneath the originally appearing matrix1_Sales_Reason group, as shown in Illustration 11.


Illustration 11: Two Row Groups Appear in the Groups Tab

20.  Click OK on the Groups tab, to close the Matrix Properties dialog.

A wider report layout appears, as expected.

21.  Once again on the Layout tab, within the Matrix Data Region, right-click the value appearing underneath the Internet Orders column heading (the leftmost of the three value cells).

22.  Select Properties on the context menu that appears, as depicted in Illustration 12.


Illustration 12: Modifying Properties for the Count Value ...

23.  On the Textbox Properties dialog that next appears, click the Format tab.

24.  Replace the existing Format code setting (in the upper left corner of the Format tab) with the following string:

#,###

Here we are simply changing the existing format to one more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as shown in Illustration 13.


Illustration 13: Replacing the Existing Format Code ...

25.  Click OK to accept our modifications, and to dismiss the Textbox Properties dialog.

We will next execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

26.  Click the Preview tab, as depicted in Illustration 14.


Illustration 14: Click the Preview Tab to Execute the Report

27.  Select Bikes in the Product Category parameter selector, as shown in Illustration 15.


Illustration 15: Select Bikes in the Parameter Selector ...

28.  Click the View Report button, as required.

The Report is being generated message briefly appears, and then the report displays. The modified report appears as partially depicted in Illustration 16.


Illustration 16: The Modified Report (Partial View)

We now have a clone OLAP report file within our Reporting Services 2005 Project, with which we can proceed in the next section to add the navigation support that our client colleagues need, using the Document Map feature.

Procedure: Add Navigation Support with the Document Map Feature

As most of us are already aware, the Document Map feature is not new with Reporting Services 2005. Many of us have implemented it in Reporting Services 2000, and will find that the steps involved in enabling and configuring the Document Map are quite similar in the current version. Moreover, those of us who have been working with Reporting Services for any length of time have also become aware of numerous approaches to providing navigation support for the information consumers within the organizations we serve. As we stated earlier, the Document Map provides a great “out of the box” navigation tool which, by itself, or perhaps in conjunction with other means of “getting around,” can help us to meet the requirements of users. Indeed, I have found the Document Map to be of assistance in the design and development phase of reporting engagements, even when it might prove less than ideal for specific navigation requirements within reports that are ultimately deployed to production scenarios.

Let’s rejoin our new report in Layout view and make the necessary settings to support the navigation capabilities requested by the information consumers.

1.  Open the DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer, if it is not already open from the steps above.

2.  Click the Layout tab, as necessary, to transit to the Layout view.

DBJ_OLAP_Report.rdl opens in Layout view.

We are now positioned to enable the report to support navigation with the Document Map. We will do so from within the Layout view, where we will again traverse the handful of dialogs and tabs that we passed through in establishing the Product group we added earlier. We will begin, once more, within the Matrix Properties settings.

3.  On the canvas area of the Layout tab, right-click the upper left-hand corner of the Matrix Data Region, as we did within our preparation steps earlier.

4.  Select Properties from the context menu, once again.

The Matrix Properties dialog appears, defaulted to the General tab, as we saw earlier.

5.  Click the Groups tab.

6.  Click the top entry in the Rows list, matrix1_Sales_Reason, to select it.

7.  Click the Edit ... button to the right of the Rows list, as shown in Illustration 17.


Illustration 17: Editing the First Group’s Settings

The Grouping and Sorting Properties dialog once again appears, defaulted to its General tab, as we saw earlier. It is here that we associate the label that is to appear in the new Document Map with the corresponding Group label, as we shall see.

8.  Using the downward pointing arrow on the right side of the Document map label box (just underneath the Group on / Expression section of the General tab), select =Fields!Sales_Reason.Value within the selector, as depicted in Illustration 18.


Illustration 18: Selecting a Value for the Document Map Label Property ...

We are simply assigning the same value to the Document Map label as we have assigned to the Group on Expression (and, by default, to the Group label that appears on the report at runtime). We might have been more sophisticated, and used a combination of fields, or even a function-driven label (I have used conditional logic within expressions, as an example, to generate the labels in Document Map trees for various clients in the past), instead of a simple value.

9.  Click OK, to accept our selection, and to close the Grouping and Sorting Properties dialog.

We return to the Groups tab.

10.  Click the second entry in the Rows list on the Groups tab, matrix1_Product, to select it.

11.  Click the Edit ... button to the right of the Rows list, as we did for the previous group.

We arrive again at the General tab of the Grouping and Sorting Properties dialog, as before. All that remains at this point is to associate the label that is to appear in the new Document Map for the Product group with the respective Group label.

12.  Using the downward pointing arrow on the right side of the Document map label box, just as before, select =Fields!Product.Value within the selector, as shown in Illustration 19.


Illustration 19: Selecting a Value for the Document Map Label for Product ...

Once again, we see that the assignment of the label, particularly in a situation where we want the label to be identical to the Group label itself, is the ultimate in simplicity. The straightforwardness of the steps involved is even more pronounced when we consider the fact that simply assigning a single Document Map label drives the enablement itself of the Document Map.

13.  Click OK, to accept our selection, and to close the Grouping and Sorting Properties dialog, once again.

14.  Click OK on the Groups tab, to close the Matrix Properties dialog.

We return to the Layout view, where we are now ready to verify the effectiveness of our modifications, and to witness the operation of the new Document Map in action.

Verification: Preview the Report and Inspect Effectiveness of the Document Map

Let’s preview the report to inspect the results of our handiwork.

1.  Click the Preview tab, once again.

2.  Select All Products in the Product Category parameter selector, as depicted in Illustration 20.


Illustration 20: Select “All Products” in the Parameter Selector ...

3.  Click the View Report button, to execute the report.

The Report is being generated message briefly appears, as before, and then the report displays. The report appears as partially shown in Illustration 21.


Illustration 21: The Report Tab Displays (Document Map Tree Collapsed) ...

As we can see, DBJ_OLAP_Report.rdl has again executed, this time returning its data for all Product Categories. This time, however, we see the new Document Map tree appear in the left margin of the report. We note that the Document Map tree is collapsed by default (as we see circled in illustration above).

We will expand the Document Map tree, and then take it for a “test spin,” in the steps that follow.

4.  Expand the collapsed Document Map tree (the top level of which, as we can see, is named after the report itself), by clicking the “+” sign to its immediate left.

We see the first hierarchical level of the Document Map, representing the Sales Reason group in the report, appear. Each label can itself be expanded, to see the levels below, as we shall see, but we can also navigate the report from this level as well.

5.  Click the “Other” Sales Reason level within the tree.

We are taken to the section of the report (actually moved to Page 4 from the default display starting point, Page 1), where the “Other” Sales Reason data group begins, as partially depicted in Illustration 22.


Illustration 22: Navigating To the Level 1 “Other” Sales Reason Group ...

It is useful to remember that we are previewing the report within the development viewer, where behavior may not exactly mimic that of the viewing environment for a deployed report. For example, within the Preview tab we may click upon a given Sales Reason, and be taken appropriately to the page upon which the data for the selected Sales Reason begins, but it may appear that we are in an adjacent level until we scroll to the selected group within the window, which, of course, makes visible only a portion of a given page at a time.

6.  Expand the collapsed “Manufacturer” Sales Reason level (the level immediately underneath the “top,” DBJ_OLAP_Report level we have already expanded, in the Document Map tree, by clicking the “+” sign to its immediate left.

We see those members of the third hierarchical level of the Document Map (Product) that are associated with the respective second level, Manufacturer” Sales Reason, appear.

7.  Click the Touring-2000 Blue, 46 member of the expanded “Manufacturer” Sales Reason, within the tree.

We are taken to the section of the report where Touring-2000 Blue, 46 appears, as shown in Illustration 23.


Illustration 23: Navigating To a Specific Product ...

Another convenient characteristic of the Document Map feature resides in the fact that we can easily turn it on or off, by clicking the Show or Hide Document Map button that appears in the upper left-hand corner of the tab, just above the top level of the Document Map tree. This button is depicted (circled) in Illustration 24.


Illustration 24: Show or Hide Document Map Button

We have, therefore, seen that the Document Map feature supports navigation in the intended manner, and thereby enables us to meet the need as expressed by the information consumers. As we have noted, although the business requirement in our immediate scenario involved a simple matching of group labels to their associated Document Map labels, the flexibility of navigation support via the Document Map tree is extended in its allowance for more sophisticated labeling, based upon expressions through which we might accommodate more customized specifications. And even though the Document Map feature is not necessarily a good fit in all implementations, it can still be useful as a part of the design and development phase of the report lifecycle.

8.  Select Save -> Save All to save our work to this point.

9.  Perform other operations with the Document Map, as desired.

10.  Select File -> Exit to leave Reporting Services, when ready.

Conclusion ...

In this article, we examined the Document Map feature, which is available within any implementation of Reporting Services, to support easy navigation for information consumers. Our hands-on exposure to the “out-of-the-box” Document Map feature specifically focused upon its enablement within a sample OLAP report, containing a Matrix data region. We discussed the general concepts, and then set up a scenario wherein which we worked with a modified copy of a sample report to expose the steps involved in establishing a Document Map within a report.

Our overall objective was to demonstrate the ease with which we can employ a Document Map within a report to support a commonly encountered business need: providing information consumers with a means of easy navigation, within a report, to information upon which they wish to focus, eliminating the need for them to page through potentially large quantities of data to get there. As a part of preparing the backdrop for a practice exercise surrounding the setup of a Document Map, we created a copy of an existing sample report to leave the original intact for other uses.

The steps we took within the clone report gave us a feel for what is involved in bringing easy, out-of-the-box navigation to a report. We noted that, while the Document Map feature might not provide the sophistication to meet the precise needs of some consumers, the flexibility offered by the option for using expressions within its settings make it appealing to many who want a solution that does not involve programming and customization outside the basic application features. As we performed the necessary settings to enable the Document Map within the report, we addressed the options available, as well as the fact that the feature might have utility in the design and development phases of the report life cycle, even if it does not meet the needs of a targeted end audience. Finally, we verified the operation of our enhancements in a test of report operation.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved