Introducing the Tablix Data Region: Basic Grouping Concepts - Page 2

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.

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

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

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

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.

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

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

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