Introducing the Tablix Data Region: Basic Grouping Concepts

This month, I continue my introduction to the Tablix data region, which is new in Reporting Services 2008 and combines the by-now familiar table, matrix and list regions, and actually underlies these options, as we noted in Introducing the Tablix Data Region in Reporting Services 2008, Pt. 1. We will work with this exciting new data region in other articles of our series. These related articles, interspersed among other topics within my MSSQL Server Reporting Services series over time, will demonstrate how to create reports to meet various requirements of various types with the Tablix data region, typically focusing upon Analysis Services data sources; they will also demonstrate how we can exploit this rich and flexible Reporting Services component to enable us to make report data more meaningful, and easier to understand, from the perspective of our information consumer audiences.

In virtually all cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions, such as Cognos (PowerPlay, Impromptu, and other applications), Business Objects, and myriad other reporting / OLAP applications can be met by Reporting Services – at a tiny fraction of the cost. And the flexibility and richness of the Tablix data region, among many other Reporting Services components, exceed in many ways the rather fixed options available in other enterprise reporting solutions.

As I have repeated in many of my articles in this column, one of the first things that becomes clear to “early adopters” of Reporting Services is that the “knowledgebase” for Analysis Services reporting with this tool is, to say the least, sparse. The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services in recent years, continues to represent a serious “undersell” of Reporting Services, from an Analysis Services reporting perspective. I hope to contribute to making this space more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.

Note: For more information about my MSSQL Server Reporting Services column in general, see the section entitled “About the MSSQL Server Reporting Services Series” that follows the conclusion of this article.

Overview

As we noted in Introducing the Tablix Data Region in Reporting Services 2008, Pt. 1, and as we see in other articles within this series, Reporting Services enables us to present both summarized and detailed data in chart and other data regions of various designs. The Tablix data region merges tabular and crosstab features, exceeding the capabilities of either. A generalized layout report item, the Tablix displays report data in cells that are organized into rows and columns. Report data can be detail data as it is retrieved from the data source, or aggregated data organized into groups that we specify. Each Tablix cell can contain any report item, including a text box, an image, or another data region such as a Tablix region, a chart, or a gauge. (To add multiple report items to a cell, we would first add a rectangle, which would serve as a container, and then add the report items to the rectangle.)

We noted, in Pt. 1, that we can choose from three data region types supported by the Tablix. (As we have discussed in various articles of this series, a data region is an area on a report that contains data from a data source that is repeated. The general types of data regions are list, matrix, table, and chart.) As we have already begun to see in earlier examinations of various data regions within Reporting Services, and as we shall see through in-depth, practical exercises in this and coming articles, we can also extend the value of our Tablix-endowed reports in myriad ways. Among these ways, just for starters, are the capability to format the Tablix data region and other objects within a host of options, to drill down to see the details behind graphical / numerical summaries, to combine Tablix regions with other types of regions, and to access many other options in the powerful Reporting Services tool set.

We stated in Introducing the Tablix Data Region in Reporting Services 2008, Pt. 1 that the initial focus of the “Tablix for Analysis Services” articles that will appear within this series is a full set of procedures that are designed to support a more in-depth study of specific property settings, and so forth. My objectives will be to typically allow a reader to quickly assemble a given Tablix report (relying upon, for instance, already assembled data sets and other underlying support within existing sample reports), and to move efficiently into targeted reporting nuances that meet real world needs. The ultimate objective, as is typically the case within my various columns, is to provide hands-on opportunities to learn overall, start-to-finish procedures, before homing in on specific options of interest, although we will certainly deal with many of these options in even our early exercises, as a part of completing the stated objectives of these sessions.

Introducing the Tablix Data Region (continued …)

In Introducing the Tablix Data Region in Reporting Services 2008, Pt. 1, we overviewed the nature and characteristics of the Tablix data region, stating that it lies at the heart of the table, matrix and list regions. We noted that the Tablix allows us to easily decide which region with which to begin our reporting efforts, because each data region is manifested in the toolbox, where we can select it. For example, if we drag a matrix data region onto the report canvas, the Tablix will not present pre-defined columns and rows, but will allow dynamic columns and rows. Moreover, if we start with a specific data region, such as a table, and then decide that a matrix might be more appropriate to display, say, the ever-growing membership of our product lines, we can transmute the table to a matrix with ease, thanks to the capabilities of the Tablix that underlies it.

We discussed the fact that there are many business and other scenarios where Tablix data regions are useful, and that, depending upon the needs and objectives of the report audience, and how the information will be used, summary information presentation – and the potential impact that can be imparted – can be quite significant. After several other comments about the need to focus upon the intended audience as we design reports, we pointed out that managerial and executive reports are often enhanced through the use of the Tablix data region, because it presents information with a high degree of flexibility. The capability to provide summarization horizontally, vertically, or both is a primary attraction in the use of this “combination table-list-matrix” data region.

In this article, we will continue the introduction to the Tablix data region we began in Reporting Services 2008, moving beyond our discussion of its general uses and characteristics in Pt. 1, and getting some hands-on exposure to the characteristics and properties of a working example. This will prepare us, as we have noted, for other articles where we employ the Tablix types in reporting from an Analysis Services data source, and demonstrate properties (and creative ways to manipulate them within our reports) and methods that we can employ to format and deliver information to meet the business needs of our clients and employers.

In Pt. 1 of our introduction to the Reporting Services 2008 Tablix data region, we began a discussion of the Tablix and its general uses and characteristics. We stated that this overview would serve to prepare us for other articles where we employ the Tablix data region in reporting from an Analysis Services data source, and demonstrate properties (and creative ways to manipulate them within our reports) and methods that we can leverage to format and deliver information to meet the business needs of our clients and employees.

In introducing the Tablix data region, we briefly discussed preparation for our practice session, specifically touching upon how to obtain and install the samples for Reporting Services 2008. We then opened the sample Report Server solution, AdventureWorks 2008 Sample Reports, and ascertained connectivity of its shared MSSQL Server 2008 data source. Throughout the first half of the article, we discussed basic features of the Tablix data region, and looked forward to this, the second half of the article, where we will further discuss the basic features of the Tablix data region, and examine the supporting properties and settings within an existing sample report.

In continuing our introduction to the Tablix data region, we will:

  • Create a “working” copy of the sample Territory Sales Drilldown 2008 report, with which we will work in the practice section of this article;
  • Open the sample Territory Sales Drilldown 2008 report, and navigate to the Layout tab;
  • Begin our examination and discuss basic features, properties and settings of the Tablix data region within the sample Territory Sales Drilldown 2008 report;
  • Discuss basic concepts applying to the Tablix data region, including:
    • The difference between detail data and grouped data;
    • Row groups and column groups;
    • Static and dynamic rows and columns and how they relate to groups.
  • Examine Tablix visual cues;
  • Examine Tablix group rows;
  • Look forward to Pt. 3, where we will continue to examine and discuss basic features, properties and settings of the Tablix data region within the sample Territory Sales Drilldown 2008 report.

Examination

We left off preparatory steps in Pt. 1 at the point of ascertaining connectivity of the AdventureWorks 2008 Sample Reports solution, with an eye toward creating a copy of the Territory Sales Drilldown 2008 sample report, within which we can follow along as we examine a working, basic Tablix data region in the development environment. We will return to the MSSQL Server Business Intelligence Development Studio at this point to continue.

NOTE: 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 column, 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 keep to the focus of the article more efficiently.

Preparation: Access the Sample Report within the Reporting Services Development Environment (continued …)

As we noted in Pt. 1, for purposes of our practice session, we will employ a copy of the Territory Sales Drilldown 2008 report, one of several samples that are available to anyone installing the MSSQL Server 2008 integrated business intelligence suite. Using a copy of the sample report within our practice session allows us to get quickly to the procedure that forms the focus of an article. We can therefore avoid the lengthy preparation that might be required to simply create a realistic setting within which to work with a specific report component. Creating a “clone” of a report means we can make changes to a fully functional copy, if need be, while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Reporting Services.

Let’s return to the AdventureWorks 2008 Sample Reports solution, where we left off with our preparations in Pt. 1, and resume with the creation of a copy of the sample report by taking the steps that follow:

1.  Click Start.

2.  Navigate to, and click, SQL Server Business Intelligence Development Studio, as appropriate.

We again briefly see the splash page that lists the components installed on the PC, and then Visual Studio 2008 opens at the Start page, once more.

3.  Close the Start page, if desired.

4.  Select File -> Open from the main menu.

5.  Click Project / Solution … from the cascading menu, as shown in Illustration 1.

Selecting the Project / Solution
Illustration 1: Selecting the Project / Solution …

The Open Project dialog appears.

6.  Browse to the Reporting Services sample reports folder accessed in Pt. 1.

NOTE: Recall that the reports are installed, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location

	C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services

The Reporting Services sample reports folder appears as depicted in Illustration 2.

Locating the Reporting Services Sample Reports Folder
Illustration 2: Locating the Reporting Services Sample Reports Folder

7.  Select the Reporting Services sample reports folder, and then click the Open button on the Open Project dialog.

8.  Select the AdventureWorks Sample Reports folder that appears among the contents of the opened Services sample reports folder.

9.  Click the Open button on the Open Project dialog, once again, as shown in Illustration 3.

Opening the AdventureWorks Sample Reports Folder
Illustration 3: Opening the AdventureWorks Sample Reports Folder

10.  Select the AdventureWorks 2008 Sample Reports.sln file within the sample reports folder,

11.  Click the Open button on the Open Project dialog, once again, as depicted in Illustration 4.

Opening the AdventureWorks 2008 Sample Reports.sln File
Illustration 4: Opening the AdventureWorks 2008 Sample Reports.sln File …

The AdventureWorks 2008 Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, once again, as shown in Illustration 5.

The Solution Opens within BI Development Studio
Illustration 5: The Solution Opens within BI Development Studio …

Create a Clone of the Sample Territory Sales Drilldown 2008 Report

From within the open Adventure Works 2008 Reports Sample project, in the SQL Server Business Intelligence Development Studio, take the following steps:

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add -> Existing Item … from the cascading context menus that appear, as depicted in Illustration 6.

Adding the Report to the Project
Illustration 6: Adding the Report to the Project …

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

3.  Navigate to the actual location of the sample reports, if the dialog has not defaulted thereto already.

NOTE: The sample reports are installed, once again, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location.

C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services

An example of the Add Existing Item – AdventureWorks 2008 Sample Reports dialog, having been pointed to the sample Reports folder (which contains the Territory Sales Drilldown 2008 report file we seek), appears as partially shown in Illustration 7.

Navigating to the Sample Reports Folder
Illustration 7: Navigating to the Sample Reports Folder …

4.  Right-click the Territory Sales Drilldown 2008 report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 8.

Performing a Quick Copy of the Territory Sales Drilldown 2008
Illustration 8: Performing a Quick Copy of the Territory Sales Drilldown 2008

6.  Right-click somewhere in the white space inside the Add Existing Item – AdventureWorks 2008 Sample Reports dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 9.

Select Paste within the Folder
Illustration 9: Select Paste within the Folder …

A copy of the Territory Sales Drilldown 2008 report appears within the dialog.

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

10.  Type the following name in place of the highlighted existing name:

DBJ_Relational_Tablix_Report.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Territory Sales Drilldown 2008 sample report appears as depicted in Illustration 10.

The New Report File, DBJ_Relational_Tablix_Report.rdl
Illustration 10: The New Report File, DBJ_Relational_Tablix_Report.rdl

11.  Click the white space to the right of the file name, to accept the new name we have assigned.

12.  Re-select the new file by clicking it.

13.  Click Add on the dialog box to add the new report to report project AdventureWorks 2008 Sample Reports.

DBJ_Relational_Tablix_Report.rdlappears in the Reports folder, within the AdventureWorks 2008 Sample Reports project tree in the Solution Explorer, as shown in Illustration 11.

The New Report Appears in Solution Explorer – Report Folder
Illustration 11: The New Report Appears in Solution Explorer – Report Folder

We now have a basic report containing a Tablix data region within our Reporting Services 2008 project, within which we can perform the various steps that form the subject focus of our article.

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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles