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
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
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
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).
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.