Practice: Examine Basic Features, Properties and Settings of the Tablix Data Region
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
the row group headers,
the column group headers, and
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
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.
Right-click the DBJ_Relational_Tablix_Report within
the Solution Explorer.
Select Open from
the context menu that appears, as depicted in Illustration 12.
Illustration 12: Opening the New Report ...
opens in Layout view, and appears as shown in Illustration 13.
Illustration 13: Our Report Opens in Layout View ...
preview the report, so as to get a feel for its general operation prior to
performing our enhancements.
Click the Preview
tab to execute the DBJ_Relational_Tablix_Report.
begins. Once executed, the report appears as depicted in Illustration 14.
Illustration 14: The Report Appears with Default Parameter Selection
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
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.
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.
Illustration 15: Click inside the Box in the Leftmost Column ...
visual cues appear in the Layout view of the Tablix data region. These cues
are depicted, circled in red, in Illustration 16.
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.
Illustration 17: Handle Displays that Can Appear in a Tablix Data Region ...
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
Lets 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.
Illustration 18: The Tablix Visual Cues, with Explanations ...
The row and column handles indicate the following group associations:
highlighted group indicator that shows the innermost group membership for a
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
Select File -> Exit to leave the design environment, when ready
(saving as desired), and to close the Business Intelligence Development Studio.
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 ...
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