Touring the Tablix in SQL Server Reporting Services

In SQL Server Reporting Services (SSRS), there are several controls (also known as data regions) that you can use to lay out your data in a report. You can choose one of the data visualization options, such as a chart, gauge, or map, or you can choose to present the data in a table, matrix, or list. Beginning in SQL Server 2008, the latter three data regions were consolidated into a single type of report item called a tablix. The Toolbox window still lets you choose a table, matrix, or list for your report, but if you look at the Report Definition Language (RDL) that gets generated for these data regions, you'll see that they each get defined as a tablix.

The tablix gets its name by combining "table" with "matrix." As you'll see later, the tablix can behave like either of these data regions. When you add one of them to your report, the properties governing the behavior of the rows and columns are predefined to make it easier for you to lay out the report quickly. For example, if you start with a table data region, the layout of the tablix has a fixed number of columns, but you can easily change it to include dynamic columns rather than start over with a separate data region.

I'll take you on a tour of the tablix so that you can better understand how it works. With that knowledge, you can take advantage of the tablix as you structure the data in your reports.

Understanding Tabular Layouts

Simply put, the tablix is a collection of rows and columns. As Figure 1 shows, there are five types of rows in the tabular version of a tablix: tablix header, group header, detail row, group footer, and tablix footer. You can design the table to use all of these row types or only some of them. Some types of rows can appear multiple times in a tablix, whereas others can appear only once. Similarly, some types of columns in a tablix can appear multiple times, whereas others can't. The trick to working with a tablix is to know which rows and columns can appear only once (also known as static rows and columns) and how to manage the rows and columns that can repeat.

Figure 1: Types of rows in the tabular version of a tablix 

In the tablix in Figure 1, there are two columns that are static. That is, no matter how many rows of data are returned by the query, this table will always display two columns. In this example, the query returns three rows, which in turn correspond to the detail rows in the tablix. These detail rows represent one group in the tablix. Encapsulating the detail rows are a pair of rows -- the group header and the group footer -- that repeat for each instance of a group, which in this case is based on year. That is, there's a group header and footer row for 2001 and another group header and footer row for 2002. If you were to examine the query results, you'd see two distinct values for the calendar year field. Finally, the tablix contains a header and footer that are completely independent of the data set and any groups defined in the tablix. However, it's possible to include a value calculated from the data set, as you can see by the total value that appears in the tablix footer.

In addition to deciding how to define groups when you create a tablix, you must decide the content that should appear in each cell. Some cells will have fixed data in them, such as the text labels that you see in the header and footer rows in Figure 2. However, most of the time, cells will display data from the data set as indicated by a field reference, such as [SalesTerritoryGroup] or [SalesAmount]. This abbreviated field reference style was introduced in SQL Server 2008 and serves as a placeholder for the field expression in the cell, such as =Fields!SalesTerritoryGroup.Value.

Figure 2: Tablix cell content 

A cell can also contain expressions. For example, in the tablix in Figure 2, there's a subtotal expression in the group footer and a grand total expression in the tablix footer, both of which are represented as [Sum(SalesAmount)]. Although the expression in each row type looks the same, it resolves as a different value based on its current scope. You can put the subtotal expression in either the group header or the group footer, or even both places if you like.

In the group header or footer, the scope is the set of detail rows associated with the current group instance. The scope of the tablix footer is the entire set of detail rows from the data set and not the sum of the values that appear in the tablix. This latter point is an important concept -- expressions always apply to the data in scope and have no dependency on the visible data in the tablix.

Because scope is crucial for subtotal expressions, you must pay close attention to the scope of a cell as you insert the necessary calculations. SSRS's Report Designer provides visual cues for this very purpose. When you click a text box in the tablix, such as the one containing the subtotal in the group footer, a dark gray line appears as the border for the selected text box and an orange bracket displays on the left edge of the tablix to show the rows that belong to the same group, as Figure 3 shows. If you were to click the text box containing the grand total calculation in the tablix footer, you'd see a gray bracket instead of an orange one because there's no group scope for this text box. Instead, the scope of the text box is the entire data set bound to the tablix.

Figure 3: Scope of the subtotal expression in the group footer 

You can also see information about groups by looking at the lines in the row handles along the left edge of the tablix. For example, you can identify the details row by the stack of three horizontal lines. You can also see that [CalendarYear] and <> are the topmost and bottommost rows, respectively, in a group. The line furthest to the left of the bracket is the set of detail rows associated with a particular year. This line indicates the section of rows that will repeat for each instance of a group.

By looking at the Row Groups pane in Figure 3, you can understand the relationship between the two groups in the tablix. CalendarYear appears above and to the left of Details, which indicates the Details group is nested within the CalendarYear group. In other words, the section of rows that repeats for each year instance includes the details row. If the data set includes more fields, you can continue to build the hierarchy within the tablix by dragging a field from the Report Data pane and dropping it between CalendarYear and Details in the Row Groups pane or by dropping it above CalendarYear.

Understanding Matrix Layouts

Now let's consider how the structure and behavior of the tablix changes when you use a matrix rather than a table. In Figure 4, you can see how the same data from the previous example displays in a matrix. Notice that the tablix no longer has headers and footers for the groups or for the tablix. Instead, the repeating data is placed in rows and columns and becomes part of the row grouping and column grouping. More specifically, the year group is in the columns and the sales territory group is in the rows. The tablix's body holds the aggregated numeric values in the data set, whereas the corner holds a label for the row groups below it. The body can also hold non-numeric values from the data set but would display the field value for the first row of the current group. Each tablix footer contains subtotals for its corresponding rows or columns.

Figure 4: Simple matrix layout 

The double-dashed lines in Figure 4 define the boundaries separating the text boxes in the group headers from the body of the tablix. You'll see corresponding gray double-dashed lines in the tablix in design mode to help you distinguish between group header text boxes and body text boxes. A tabular tablix doesn't include the dashed lines because all the text boxes are in the body.

Each additional row group adds another column to the row grouping section in the tablix. As with the stacked group layout that you saw in Figure 1, each group instance appears only once. However, in Figure 5, notice that the outer group value in the Category column doesn't repeat with each row of the inner group but rather appears only once in a cell that spans the child group's rows in a blocked format. Similarly, a new column group would result in a new row in the column grouping section, and each outer group value would span the inner group's columns.

Figure 5: Matrix layout with nested groupings 

Instead of nested groups, you can create adjacent groups and add subtotals for each group, as Figure 6 shows. The grand total in the tablix footer row reflects only the total of the rows in the data set and doesn't sum the column values. Notice in the Row Groups pane that the two groups align on the left to indicate that they're adjacent groups rather than nested groups, as Figure 5 shows. You can create adjacent column groups as well.

Figure 6: Matrix layout with adjacent groups 

Figure 7 shows another type of design that you can implement in a tablix. In addition to repeating a row or column based on group instances, you can repeat a set of rows or columns with each instance. When you add a new row or column to the tablix, you specify whether the new unit will be inside the current group or outside the current group to establish the scope applicable to that row or column.

Figure 7: Matrix layout with repeating columns 

Adding a Tablix to a Report

At this point, you should have a better understanding of tablix concepts. Now you're ready to apply these concepts by adding a tabular version of a tablix to a report. To follow along with the example I'm about to provide, you first must create a report project and add a data source that connects to the AdventureWorksDW2008R2 database. You then need to create a data set using the code in the CreateDataSet.sql file, which you can download  by clicking the 142029.zip hotlink at the top of this page. If you need help with creating a report project and adding a data source to it, see the MSDN tutorial.

To work with a tablix in a tabular layout, drag the Table data region from the Toolbox and drop it into the report body section. When you add a table data region to the body of the report, the default layout includes a tablix header row and a details row. In addition, Report Designer adds a Details group to the Row Groups pane. You can right-click the Details group to access the Group Properties dialog box, where you can configure sorting, filtering, and other properties.

The next step is to add field references from the data set to the text boxes in the tablix. You can achieve this two ways. You can drag a field from the Report Data pane and drop it into the target text box. Alternatively, you can click the Field Selector in the text box to display a list of available fields and select one.

For this report, click the Field Selector in the first column of the details row and select SalesTerritoryGroup. Report Designer will then add the field reference to the tablix using the placeholder expression [SalesTerritoryGroup]. It will also add a label to the tablix header row based on the field name. Next, use the Field Selector to add SalesTerritoryCountry to the second column and SalesTerritoryRegion to the third column.

When you require more than three columns in a table, you can add another column one of two ways. One option is to right-click the last column, click Insert Column, and click Right to append the column to the end of the tablix (or click Left to insert the column into the tablix and retain the current column at the end). The other method is to drag a field from the Report Data pane to the right edge of the last column. When you see a blue line along the right edge of the column, you can release the mouse button to insert the new column. If you want to insert the new column between two existing columns, you can release the mouse when the blue line appears between the existing columns. To finish the tabular layout, add CalendarYear as the fourth column and SalesAmount as the fifth column in the tablix.

Grouping Data into Row and Column Groups

One way you can improve the appearance of a table is to group data. For example, you can easily convert a tabular layout to a crosstab layout by creating row groups and column groups. To do this, drag the SalesTerritoryGroup field from the Reports Data window and drop it above the Details row group in the Row Groups pane, so it looks like that in Figure 8.

Figure 8: The addition of a row group 

When you added the SalesTerritoryGroup field, Report Designer added a row grouping section to the tablix, which is the first step toward turning the table structure into a matrix. The double-dashed line between the new SalesTerritoryGroup column and the second SalesTerritoryGroup column is your visual cue that the tablix now contains a row grouping section. In addition, Report Designer set the group expression for the new row group to [SalesTerritoryGroup]. You can review the group expression by double-clicking the SalesTerritoryGroup group in the Row Groups pane.

At this point, you have duplicate columns for SalesTerritoryGroup, as Figure 9 shows. (You'll eliminate the duplicates shortly.) One column is a row group, which will display once in a cell that spans all detail rows associated with that group. The other column is in the tablix body, which will repeat for each detail row.

Figure 9: Duplicate SalesTerritoryGroup columns 

Continue adding groups to complete the matrix. Drag the SalesTerritoryCountry field from the data set and drop it between the SalesTerritoryGroup and Details groups in the Row Groups pane. Repeat this process to place the SalesTerritoryRegion field between the SalesTerritoryCountry and Details groups.

Other ways to insert a new row group is to drag and drop a field onto the tablix in the row grouping section or use the context menu of a row group text box in the same way that you insert columns into the tablix body. If you create groups in the wrong order, you can drag each group to the proper location in the Row Groups pane.

Next, delete the SalesTerritoryGroup, SalesTerritoryCountry, SalesTerritoryRegion, and CalendarYear columns from the body of the tablix. To do this, click the [SalesTerritoryGroup] text box for the detail row in the tablix body (the column immediately to the right of the double-dashed line), then hold down the Ctrl key while clicking each of the three text boxes to its right so that you have all four text boxes selected. Finally, right-click any of the text boxes and click Delete Columns.

Now drag CalendarYear from the data set to the Column Groups pane. Clear the Sales Amount label from the text box between [CalendarYear] and [SalesAmount] by double-clicking the text box, highlighting the text, and pressing the Delete key. If you leave the Sales Amount label in place, it'll repeat with each year because the label is part of the tablix body. The labels in the row grouping section display only once because they're not part of the tablix body. Finally, right-click the Details group in the Row Groups pane, click Delete Group, click Delete Group Only, and click OK to confirm.

One of the key differences between a tablix structured as a table and a tablix structured as a matrix is the Details group. A table usually (but not always) includes the Details group, whereas the matrix eliminates the Details group to display aggregated values only. Therefore, it's necessary to manually remove the detail group from the report to convert the report layout from a table to a matrix.

At this point, there's another change necessary to complete the conversion to a matrix. The SalesAmount value currently represents the value at the detail level because it's a direct reference to a data set field rather than an aggregated value. Because a matrix must display aggregated values only, you need to create an expression to display the correct value. Report Designer automatically displays placeholders when you drag and drop an item into a text box. However, you can also type a placeholder expression directly into a text box. Just double-click the SalesAmount text box to switch to edit mode, then type [SUM(SalesAmount)].

If you preview the report before and after making this change, you'll notice there's no difference in the values that display in the matrix. The data set doesn't have multiple rows for each distinct combination of SalesTerritoryGroup, SalesTerritoryCountry, SalesTerritoryCountry, and CalendarYear. If it did, you'd see the correct aggregated value only after making the aforementioned change. Prior to making the change, you'd see the amount for the first row for each combination of SalesTerritoryGroup, SalesTerritoryCountry, SalesTerritoryCountry, and CalendarYear. Although the change wasn't required to view the correct values in this report, you should develop the habit of ensuring you use an expression with an aggregate function whenever the tablix is in a matrix layout. Then, if you or someone else later adds fields to the data set, the correct values will still display.

A Welcome Feature

The tablix provides a lot more flexibility for your data layout than its counterparts provided in earlier versions of SQL Server. Not only do you have more flexibility, but you have the option of rearranging the layout rather than starting a new layout if you discover that a matrix would be better than a table or vice versa. Any feature that saves time is a welcome feature.