Creating a Greenbar Matrix

The pattern used for a matrix is very similar to a table. Since the matrix generates column cells dynamically, there is no way to specify a different expression for each column. If I wanted the row header to have an alternating background color, I could use the same technique as the table; toggling the odd/even flag explicitly on the row header textbox. But, if the pivot cell is to be the left-most item with an alternate background color on each row, this becomes more challenging. To work around this limitation, I define an extra row group on the same field expression as the previous group in the row hierarchy. Figure 4 shows the group definitions. The two row groups both use the same expression. This will cause the second group header textbox to be repeated with each row. I'm going to hide this cell when I'm done.

Next, I set the BackgroundColor property using the same expression that I used in the table example. The second row header textbox sets the AlternateColor function to toggle the odd and even rows. Since the pivot cell (the textbox at the intersection of the row groups and column group) is repeated with the same background color for every column in a row, the second function argument is set to False.

In Figure 6, I've reduced the width of this cell and I've also hidden it by setting the Visibility/Hidden property to True. You actually can't completely eliminate all evidence of a cell but you can make it very narrow. I've set the GridSpacing property to .03125 (1/32 of an inch) so I could make this column as narrow as possible.

Figure 7 shows the end result. The utility cell causes a small gap between the row header and the remaining columns, alternate row colors are applied to the aggregate data cells.

This article is the first in a series of 3 articles from Paul Turley excerpted from the 90 page chapter, "Report Solution Patterns and Recipes" from the book Professional SQL Server 2005 Reporting Services written by Paul Turley, Dave DuVarney, James Counihan and Todd Bryant (Wrox, 2006, ISBN: 0-7645-8497-9). The full chapter contains a deep analysis of successful report projects, including project profiles, success factors, user discussions and scope management. Templates are provided to assist requirement gathering and management. The Report Recipes section of this chapter includes 19 specific examples of advanced report designs that implement custom coding and other techniques taught in previous chapters. This series of articles demonstrates 3 of these examples. The next 2 will be Creating a Business Scorecard and Creating Sparklines. Hitachi Consulting has business and IT consulting offices throughout the world. Paul works for Hitachi's Pacific Northwest Business Intelligence practice in Seattle and may be contacted at pturley@hitachiconsulting.com.