Pro SQL Server 2012 Reporting Services

CHAPTER 6  BUILDING REPORTS

Adding Tablix Elements

SSRS 2008 introduced a significant change in report design by way of the Tablix data region. Essentially,
the Tablix, as covered in detail in Chapter 4, combines aspects of the Table and Matrix data regions. The
Table region available in SSRS 2005 worked well with row data of variable length based on the dataset. In
turn, the Matrix data region included support for a variable number of columns. Reporting Services 2008
allowed use of either control to include custom row or column groupings anywhere in the report. As you
will see in the subsequent addition to the Employee Service Cost report, adding column groupings to a
table data region is straightforward.
In the section “Configuring Report and Group Variables” that follows, you will add a column
grouping to the EmployeeServiceCost_Tablix_Start report using the Year field. The Year field, as you may
recall, indicates what year the types of services were rendered for the patients. So far in the report, you
have a distinct patient and employee count as well as estimated cost and visit count. So, what if you
wanted to see each of these values grouped by the year of service for multiple years?
You will perform the following actions to add a column group for the Year to the Employee Service
Cost report:

1. Right-click the [CountDistinct(PatID)] cell in the table and select Add Group
   and then Parent Group under the Column Group section.


2. In the Group by Expression field, select [Year]. Do not check the Add Group
   Header or Add Group Footer button. Click OK.


3. Since we want to have groups that are independent of one another, we now
   need to add what is called an Adjacent Group. On the new [Year] field that we
   just created (which should be just above the No of Patients label), right-click
   and select Add Group and then Adjacent Right. Group on the [Year], as you did
   in step 2. Click OK. This new column will hold all of the values in the No of
   Employees column that currently exists. In step 5, we will shift all of the values
   to be underneath the new [Year] columns.


4. Perform step 3 two more times, each time using the right-most [Year] field.


5. Now that we have three columns that just have the [Year] in the column
   groupings, we need to move our existing values underneath of them. At this
   point, the design surface should look like Figure 6-36. Next select all of the
   fields from the No of Employees, Estimated Cost, and Service Count columns.
   Be sure to select the header text too! Then cut and paste the values from their
   original cells into the three empty columns under our new Adjacent Groups
   that we created in steps 2 and 3.


6. The three columns on the right are no longer needed. Delete them by right-
   clicking the column and choosing Delete Columns. You can select all three of
   them by holding the CTRL key as you select them.


7. Finally, adjust the widths of the columns with enough room to show the labels
   as shown in Figure 6-37.