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.

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:

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.

The Open Project dialog appears.

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

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

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.

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:

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

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.

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

DBJ_Relational_Tablix_Report.rdl

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

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

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.