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
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.
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
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
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,
working copy of the sample Territory Sales Drilldown 2008 report, with which
we will work in the practice section of this article;
sample Territory Sales Drilldown 2008 report, and navigate to the Layout tab;
examination and discuss basic features, properties and settings of the Tablix data
region within the
sample Territory Sales Drilldown 2008 report;
concepts applying to the Tablix data region, including:
between detail data and grouped data;
Row groups and
Static and dynamic
rows and columns and how they relate to groups.
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.
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.
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
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
Lets 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:
and click, SQL Server
Business Intelligence Development Studio, as appropriate.
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.
Close the Start
page, if desired.
Select File -> Open from the main menu.
/ Solution ... from the cascading menu, as shown in Illustration 1.
Illustration 1: Selecting the Project / Solution ...
The Open Project dialog appears.
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
Reporting Services sample reports folder appears as depicted in Illustration 2.
Illustration 2: Locating the Reporting Services Sample Reports Folder
Select the Reporting
Services sample reports folder, and then click the Open button on the Open
Select the AdventureWorks
Sample Reports folder that appears among the contents of the opened Services
sample reports folder.
Click the Open
button on the Open Project dialog, once again, as shown in Illustration 3.
Illustration 3: Opening the AdventureWorks Sample Reports Folder
Select the AdventureWorks
2008 Sample Reports.sln file within the sample reports folder,
Click the Open
button on the Open Project dialog, once again, as depicted in Illustration 4.
Illustration 4: Opening the AdventureWorks 2008 Sample Reports.sln File ...
2008 Sample Reports solution opens, and we see the various objects within
appear in Solution Explorer, once again, as shown in Illustration 5.
Illustration 5: The Solution Opens within BI Development Studio ...
Create a Clone of the Sample Territory Sales Drilldown 2008 Report
within the open Adventure Works 2008 Reports Sample project, in the SQL Server Business Intelligence
Development Studio, take the following steps:
the Reports folder underneath the Shared Data Sources folder, in the Solution
Select Add -> Existing Item ... from the cascading context menus
that appear, as depicted in Illustration 6.
Illustration 6: Adding the Report to the Project ...
Existing Item AdventureWorks Sample Reports dialog appears.
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
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.
Illustration 7: Navigating to the Sample Reports Folder ...
the Territory Sales Drilldown 2008 report inside the dialog.
from the context menu that appears, as depicted in Illustration 8.
Illustration 8: Performing a Quick Copy of the Territory Sales Drilldown 2008
somewhere in the white space inside the Add Existing Item AdventureWorks 2008 Sample Reports dialog.
from the context menu that appears, as shown in Illustration 9.
Illustration 9: Select Paste within the Folder ...
of the Territory Sales Drilldown 2008 report appears within the dialog.
the new file.
Select Rename from
the context menu that appears.
following name in place of the highlighted existing name:
NOTE: Be sure to include the .rdl extension in the file name.
renamed copy of the Territory Sales Drilldown 2008 sample report appears as
depicted in Illustration 10.
Illustration 10: The New Report File, DBJ_Relational_Tablix_Report.rdl
white space to the right of the file name, to accept the new name we have
new file by clicking it.
Click Add on
the dialog box to add the new report to report project AdventureWorks 2008 Sample
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
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.