Introducing the Tablix Data Region in Reporting Services 2008

This month, I introduce a data region that is new in Reporting
Services 2008
. The tablix data region
combines the by-now familiar table, matrix and list regions,
and actually underlies these options, as we shall see in the examination
that follows. 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 have seen in previous articles, and as we will show in
many prospective 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 detailed 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 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 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.

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

As most of us are aware, the table data region presents
data two-dimensionally, in a tabular layout whose columns are fixed and whose
rows are dynamic. The table is, by far, the most popular data region with
report developers. The matrix
data region, which has grown in popularity (particularly among developers whose
focus is more OLAP than relational data), presents data in a crosstab layout.
In a matrix, both columns and rows are dynamic, one of the features that make
the data region ideal for reports whose layouts change over time or parameters.
Finally, the list data region allows us to present data in free-form reports,
giving us ultimate control over the placement of report items. Each of these
data regions can contain other data regions inside, adding even more
flexibility and sophistication for the knowledgeable report developer.

With Reporting Services 2008, the tablix data region
lies at the heart of the table, matrix and list regions. We can 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.

When we add the table, matrix, or list data region
templates to a report, we are actually adding a tablix data region that is
optimized for a corresponding, specific data layout. (Each of the three data
regions are represented by templates for the underlying tablix data region in
the Toolbox). In the customary manner, a table template displays detail data
in a grid layout by default, a matrix displays group data in a grid layout by
default, and a list displays detail data in a free-form layout by default. Moreover,
by default, each tablix cell in a table or matrix contains a text box. The cell
in a list contains a rectangle. We can replace a default report item with a
different report item, for example, an image. As we define groups for a table, matrix,
or list, Report Designer adds rows and columns to the tablix data region on
which to display grouped data.

As
I have noted many times within my MSSQL Server Reporting Services
series, the
rich data presentation capabilities made available within Reporting Services
are quite impressive. They are as powerful, and as easy to use, as the options
that I have found within any enterprise reporting solutions with which I have
worked, among which I include various products of Cognos, Business Objects, and
many others, as well as, for that matter, within Microsoft Office applications
such as Excel or Access. In many ways, the Reporting Services data region
offerings are more powerful, in general, largely due to their openness to the
use of expressions. And much like the independent list, table and matrix data
regions with which we have worked in so many of the pre- Reporting Services
2008 articles of this column, a Reporting Services tablix data region is supported by a data set and can leverage
filters, groups, query and report parameters, and much more in a similar manner
to the three previously standalone data regions.

There
are many business and other scenarios where tablix data regions are useful.
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. The intended usage and the identities
of the targeted information consumers should always be considered in report
design and creation. Moreover, the organizational, and other, roles that given
consumers assume in using the data we present (for example, whether the intent
is perform a specific task via the information obtained, or, in a management role,
perhaps, to apply the information to broader, more complex tasks such as
strategy implementation or overall performance objective measurement) should
also weigh heavily in our report design and creation efforts. Finally, we
should always keep in mind that some information consumers, such as business
analysts, may have a need to perform ongoing exploration of the data (and have
needs such as ad hoc grouping, sorting, pivoting and others), whereas many
consumers will require only a specific, fixed format for data delivery (such as
that provided by managed reports). 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-matrix” data region.

In this article, we will introduce
the tablix data region in Reporting Services 2008, discussing its general uses
and characteristics. 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
employees. In
introducing the tablix data region, we will:

  • Briefly
    discuss preparation for our practice session, specifically touching upon how to
    obtain and install the samples for Reporting Services 2008.
  • Open the
    sample Report Server project, AdventureWorks 2008 Sample Reports, and ascertain
    connectivity of its shared MSSQL Server 2008 data source;
  • Examine and
    discuss basic features of the tablix data region within an existing sample report.

Objective and Overview Scenario

In this article, we will
perform a relatively straightforward, introductory examination of the Reporting
Services tablix data region, from within a copy of an existing sample Reporting
Services 2008 report. Our focus will be to examine the basic features of the tablix
data region. As we noted earlier, this introduction will prepare us for
independent articles within the series where we employ individual tablix data
regions, sometimes in combination with other data regions, in reports where we
will typically use an Analysis
Services data source – although the sample we examine in this article uses a sample
MSSQL Server 2008 relational database as its source. In each article employing
a tablix data region, we will examine relevant properties of the data region,
and get some hands-on exposure to the manipulation of those properties to
support the presentation of information to meet the needs of our own respective
business environments.

To
provide a report upon which we can practice our exercises, we will begin with
the Territory Sales Drilldown 2008 sample report that, among other samples, can
be accessed by anyone who has installed Reporting Services 2008, and which is
based upon the AdventureWorks2008 sample MSSQL Server 2008 relational database (which
is also readily available to anyone installing MSSQL Server 2008).

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.

Latest Articles