Introducing the Tablix Data Region: Basic Grouping Concepts

Tuesday Dec 22nd 2009 by William Pearson
Share:

BI Architect Bill Pearson continues his examination of the general characteristics and properties of the Tablix data region, which debuts in Reporting Services 2008. Here, we discuss further concepts, and begin our hands-on examination of a Tablix data region in a working sample report.

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.rdl appears 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.

Practice: Examine Basic Features, Properties and Settings of the Tablix Data Region

In the overview procedures that follow, we will examine the basic features, properties and settings of the Reporting Services 2008 Tablix data region. To gain an understanding of the Tablix data region, we will need to grasp the following concepts that underlie the Tablix data region:

  • The difference between detail data and grouped data.
  • Groups, which are organized as members of group hierarchies on the horizontal axis as row groups and on the vertical axis as column groups.
  • The purpose of Tablix cells in the four areas of a Tablix data region:
    • the body,
    • the row group headers,
    • the column group headers, and
    • the corner.
  • Static and dynamic rows and columns and how they relate to groups.

An understanding of these concepts equips us to recognize the structure that Report Designer adds for us when we add templates and create groups, and modify the structure to suit our own needs. (Report Designer provides multiple visual indicators to help us recognize Tablix data region structure, as we shall see.) We will examine each of these key concepts in the respective subsection that follows.

Open the Sample Territory Sales Drilldown 2008 Report

Now that we have a copy of the sample report, we are ready to open the report in preparation for our primary practice objective, to examine the properties and settings of a working Tablix data region.

1.  Right-click the DBJ_Relational_Tablix_Report within the Solution Explorer.

2.  Select Open from the context menu that appears, as depicted in Illustration 12.

Opening the New Report
Illustration 12: Opening the New Report ...

DBJ_Relational_Tablix_Report.rdl opens in Layout view, and appears as shown in Illustration 13.

Our Report Opens in Layout View
Illustration 13: Our Report Opens in Layout View ...

Let’s preview the report, so as to get a feel for its general operation prior to performing our enhancements.

3.  Click the Preview tab to execute the DBJ_Relational_Tablix_Report.

Execution begins. Once executed, the report appears as depicted in Illustration 14.

The Report Appears with Default Parameter Selection
Illustration 14: The Report Appears with Default Parameter Selection

4.  Click the Layout tab to return to the Layout view.

Concept: The Difference between Detail Data and Grouped Data

The difference between detail data and grouped data is a concept well understood by those of us who are familiar with tabular reporting in general. Detail data is all the data from a report dataset as it comes back from the data source. Detail data is essentially what we see in the Query Designer results pane when we run a dataset query. The actual detail data includes calculated fields that we create, and is restricted by filters set on the dataset, data region, and details group. We display detail data on a detail row by using a simple expression such as [Quantity]. When the report runs, the detail row repeats once for each row in the query results at run time.

Grouped data is detail data that is organized by a value that we specify in the group definition, for example, [SalesOrder]. We display grouped data on group rows and columns by using simple expressions that aggregate the grouped data, for example, [Sum(Quantity)].

Concept: Row Groups and Column Groups

Groups are organized as members of group hierarchies. Row group and column group hierarchies are identical structures on different axes. We can think of row groups as expanding down the page and column groups as expanding across the page.

A tree structure represents nested row and column groups that have a parent/child relationship, for example, a category with subcategories (such as we see in the Adventure Works 2008 Product data). The parent group is the root of the tree and child groups are its branches. Groups can also have an independent, adjacent relationship, for example, sales by territory and sales by year. Multiple unrelated tree hierarchies are called a forest. In a Tablix data region, row groups and column groups are each represented as an independent forest.

Concept: Tablix Cells and their Purposes in the Four Areas of a Tablix Data Region

A Tablix data region has four possible areas for cells: the Tablix corner, the Tablix row group hierarchy, the Tablix column group hierarchy, or the Tablix body.

The Tablix body always exists. The other areas are optional. Cells in the Tablix body area display detail and group data.

Cells in the row groups area are created automatically when we create a row group. These are row group header cells and display row group instance values by default. For example, when we group by [SalesOrder], group instance values are the individual sales orders by which we are grouping.

Cells in the Column Groups area are created automatically when we create a column group. These are column group header cells and display column group instance values by default. For example, when we group by [Year], group instance values are the individual years by which we are grouping.

Cells in the Tablix corner area are created automatically when we have both row groups and column groups defined. Cells in this area can display labels, or we can merge the cells and create a title.

Concept: Static and Dynamic Rows and Columns and How They Relate to Groups

A Tablix data region organizes cells in rows and columns that are associated with groups. Because group structures for row groups and columns are identical, we can adequately accomplish the consideration of both in a discussion of row groups. While we talk about row groups, we can apply the same concepts to column groups.

A row is either static or dynamic. A static row is not associated with a group. When the containing report runs, a static row renders once. Table headers and footers are examples of static rows. Static rows display labels and totals. Cells in a static row are scoped to the data region. A dynamic row is associated with one or more groups. A dynamic row renders once for every unique group value for the innermost group. Cells in a dynamic row are scoped to the innermost row group and column group to which the cell belongs.

Dynamic detail rows are associated with the details group that is automatically created when we add a table or list to the design surface. (As we noted in our introductory comments, both here and in Pt 1, the Tablix data region underlies each of these data regions). By definition, the Details group is the innermost group for a Tablix data region. Cells in detail rows display detail data.

Dynamic group rows are created when we add a row group or column group to an existing Tablix data region. Cells in dynamic group rows display aggregated values for the default scope.

The Add Total feature automatically creates a row outside the current group on which to display values that are scoped to the group. We can also add static and dynamic rows manually. Visual indicators help us to understand which rows are static and which rows are dynamic.

Enter the Newly Created Sample Report Copy to Examine a Working Tablix Data Region

To control how the rows and columns of a Tablix data region display data in a report, we must understand how to specify rows and columns for detail data, for group data, and for labels and totals. In many cases, we can use the default structures for a table, matrix, or list to display the data of our clients and employers.

A Tablix data region displays detail data on detail rows and detail columns, and grouped data on group rows and group columns. When we add row groups and column groups to a Tablix data region, rows and columns on which to display the data are automatically added. We can manually add and remove rows and columns to customize a Tablix data region and control the way our data displays in the report.

Tablix Visual Cues

To understand how to customize a Tablix data region, we should first understand how to interpret the visual cues we see when we select a Tablix data region on the design surface. Since our sample report already has a Tablix data region in place, we will examine these cues within the existing data region.

1.  Click inside the box under the header labeled Sales Territory, in the Tablix data region appearing on canvas in the Layout view, as shown in Illustration 15.

Click inside the Box in the Leftmost Column
Illustration 15: Click inside the Box in the Leftmost Column ...

Various visual cues appear in the Layout view of the Tablix data region. These cues are depicted, circled in red, in Illustration 16.

Visual Cues Appear in the Tablix
Illustration 16: Visual Cues Appear in the Tablix

Visual cues on a Tablix data region help us work with the data region to display the data we want. When we select the Tablix data region, the row and column handle graphics indicate the purpose of each row and column. Handles indicate rows and columns that are inside a group or outside a group. Table 1 explains the variety of handle displays.

Handle Displays that Can Appear in a Tablix Data Region
Illustration 17: Handle Displays that Can Appear in a Tablix Data Region ...

Group Rows

Rows inside a group repeat once per unique group value and are typically used for aggregate summaries. Rows outside a group repeat once with respect to the group and are used for labels or subtotals. When we select a Tablix cell, row and column, handles and brackets inside the Tablix data region, such as those we saw earlier (“visual cues”), show the groups to which a cell belongs.

Let’s look again at the visual cues in the Tablix data region we have selected. Illustration 18 displays the row and column handles, once again, to the left of the Tablix data region.

The Tablix Visual Cues, with Explanations
Illustration 18: The Tablix Visual Cues, with Explanations ...

The row and column handles indicate the following group associations:

  • A highlighted group indicator that shows the innermost group membership for a selected cell.
  • Group indicators that show group memberships for a selected cell.

Once we have row and column groups in place, we can add a row to display totals for columns and a column to display totals for rows. We will accomplish this in Pt. 3 of this article, where we will further explore characteristics, settings and properties of the new Tablix data region.

1.  Select File -> Exit to leave the design environment, when ready (saving as desired), and to close the Business Intelligence Development Studio.

Conclusion

In this, the second part of an introduction to the Reporting Services 2008 Tablix data region, we continued our discussion of the data region 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.

Having opened the sample Report Server solution, AdventureWorks 2008 Sample Reports, and ascertained connectivity of its shared MSSQL Server 2008 data source, in Pt. 1, we completed preparation for our practice sessions by creating a “working” copy of the sample Territory Sales Drilldown 2008 report, with which we will work in the practice section of this and subsequent parts of the article. We next opened the sample Territory Sales Drilldown 2008 report, and navigated to the Layout tab, where we began our hands-on examination and discussion of basic features, properties and settings of the Tablix data region within the sample Territory Sales Drilldown 2008 report.

We discussed basic concepts applying to the Tablix data region, including the difference between detail data and grouped data, row groups and column groups, and static and dynamic rows and columns and how they relate to groups. We next examined Tablix visual cues and Tablix group rows, referring to our sample report for illustration of the concepts. Finally, we looked 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.

About the MSSQL Server Reporting Services Series ...

This article is a member of the series MSSQL Server Reporting Services. The column 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.

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

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