Advanced Matrix Reporting Techniques : Page 5

Technique 6: Custom Graph Colors
To add some additional splash, you can add a chart to display the variance of sales year-over-year. Adding this chart requires the same grouping that you used on the matrix component. Add the chart above the matrix and set it to Simple Column with the same dataset as our matrix (AW_Sales).

On the Data tab, create a new Value. Then, on the Values tab use the following expression =Sum(Fields!LineTotal.Value) to display the total dollar amount of sales. Use the same expression on the Point Labels tab in the Data Label field. Be sure to check ‘Show Point Labels’ and format for currency (C0) so you can see the actual totals on the chart. On the Appearance tab, click the Fill tab. Set the first color to CornSilk (where do they get these color names?) and for the second color (since I am a gradient fiend) use the conditional expression:

 
=IIF(Year(Fields(Parameters!TimeSlicer.Value).Value)=Parameters!Year.Value, "Gold", "Blue")

Figure 6. Fill Tab: You can use the Fill tab to select distinct colors, which makes the bars much easier to read.

Using the Fill tab provides distinct colors on the report for each year, which makes the bars much easier to differentiate (Figure 6).

Next, create two Category Groups: one for the Month and the second for the Year. The Month has the Group On and Sort Expression =Month(Fields(Parameters!TimeSlicer.Value).Value), though our label wraps that expression in MonthName(), which is more user-friendly.

The Category Group for Year also uses the same expressions you used earlier in the matrix component grouping. The Group On, Sort and Label expression is set to =Year(Fields(Parameters!TimeSlicer.Value).Value).

Final Output
When you Preview the report, the parameters allow for a great deal of flexibility.

Figure 7 shows the various groupings you can select, along with multiple months and a dynamic date criteria.

Figure 8 shows a summarized view of variances only and Figure 9 illustrates what the detail looks like when exposed.

The end result is a report that behaves very much like a Pivot Table in Excel. You can extend this with additional conditional formatting or provide additional value to reports that use Analysis Services as the source for a more cube-like browsing experience in SSRS. Hopefully, this example has armed you with some new skills so you can grab the red pill and tackle those tougher matrix reporting challenges in the future.

Figure 7. Previewing the Report: You can see the various groupings selected.

Figure 8. Previewing the Report: You can view a summarized view of variances.

Figure 9. Previewing the Report: You can view what the details look like as shown in this figure.

Previous Page