Introducing the Tablix Data Region in Reporting Services 2008
November 25, 2009
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.
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:
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).