With the release of SQL Server 2008 Reporting Services, Microsoft merged the Table, Matrix, and List data regions into a single unified data region: the tablix. So when you drag a matrix region to a report, even though it says Matrix on the Toolbox, you are actually dragging a tablix onto the report surface.
The major benefit of the unified Tablix data region is the added flexibility over the separate data regions. This allows you to create more flexible matrix reports as well as reports that blend features of both tabular and matrix reports into one hybrid report. The next few sections illustrate some of the newfound flexibility of SQL Server 2008 tablix region in producing more flexible matrix reports.
Adding Adjacent Dynamic Columns to a Matrix
Thanks to the Tablix data region, you can create matrix reports that display multiple dynamic columns. This was impossible prior to SQL Server 2008. With the introduction of the Tablix data region, however, it’s fairly simple.
To add a second (or third, etc.) dynamic column to an existing matrix (tablix) region, click on the arrow to the right of an existing column group in the Grouping pane and select Add Group|Adjacent Before or Add Group|Adjacent After from the popup menu as shown in Figure 1.
Figure 1. Adding an adjacent column group.
A Two-Dynamic Column Example: The rptTwoDynamicCols Report
The rptTwoDynamicCols report contains two adjacent column groups. The first group, like most of the sample reports in this chapter is based on the OrderYear field. The report contains an adjacent column group, based on the CategoryName field. The rptTwoDynamicCols report is shown in Design view in Figure 2. The Tablix data region on the reports contains an extra header row that was added above the column groups by right-clicking on one of the column group cells and selecting Insert Row|Outside Group – Above.
Figure 2. The rptTwoDynamicCols report contains two adjacent column groups.
The report is shown in Firefox in Figure 3.
Figure 3. The rptTwoDynamicCols report in Firefox.
Adding Adjacent Static Columns to a Matrix
With SQL Server 2008 Reporting Services, you aren’t limited to creating a report that is either a tabular report or a matrix report. Reporting Services now allows you to add elements of one report type to another. For example, you can take an existing matrix report and add a static column adjacent to it.
To add a static column to the left or right of an existing matrix region, rightclick on the column selector of an existing column group, and select Insert Column|Outside Group – Left or Insert Column|Outside Group – Right, respectively as shown in Figure 4. After adding the column, you can either drag and drop a field from the Report Data pane to the new blank column or hover over the blank column and click on the field list to select from the list of dataset fields.
Figure 4. Adding a static column to the right of a dynamic column.
A Matrix/Table Hybrid Example: The rptDynamicAndStaticCols Report
The rptDynamicAndStaticCols report contains a static column to the right of the dynamic OrderYear column. This column contains a text box that is bound to the LastOrder field from the dsSales2 dataset. The report is shown in Design view in Figure 5 and in the Firefox browser in Figure 6.
Figure5. The rptDynamicAndStaticCols report in Design view.
Figure 6. The completed rptDynamicAndStaticCols report adds a static column, LastOrder, to the right of a dynamic column, OrderYear.
Adding a Percentage to a Matrix Report
Adding a percentage to a dynamic column in a matrix report was close to impossible prior to SQL Server 2008. Now, however, it’s a pretty simple process. The basic trick is to insert a new column on the design surface that is inside the dynamic column group. By selecting Inside Group, you are ensuring that the new column, which will host the percentage calculation, repeats with each instance of the dynamic column.
This post is an excerpt from the online courseware for our Microsoft SQL Server 2008 Reporting Services course written by expert Paul Litwin.
Paul Litwin is a developer specializing in ASP, ASP.NET, Visual Basic, C#, SQL Server, and related technologies. He is an experienced trainer, has written articles for MSDN Magazine and PC World, and is the author of several books including ASP.NET for Developers (SAMS) and Access 2002 Enterprise Developer’s Handbook (SYBEX). Paul is a Microsoft MVP, the chair of the Microsoft ASP.NET Connections conference, and a member of the INETA Speakers Bureau.