Over the last year MVP Tim Kent has put out a series of really useful posts showing how various data visualizations can be created using SSRS.
The latest post on Trellis displays got me thinking on how I could tweak a few of the settings in Tim’s very useful sample report just a bit more.
In order to show more sample data, I changed the top axis of Tim’s sample report to show sales Bike subcategories because as we all know AdventureWorks sells waaaay more bikes than anything else. Below is a shot of the original report after that change.
I made a few more tweaks and changes and came up with the report below
The main ink-saving tip is to conditionally hide / show labels on the X and Y axes based on the items at the top left and bottom left of the trellis. In this case it is Road Bikes and Northeast. All that is needed here is a small amount of extra MDX to ORDER and RANK members in both the Region and Subcategory sets to provide the right meta data required to perform the conditional hide / show. Here is the MDX for the report showing the ordering and ranking of the appropriate sets.
The real trick here is to use an expression to conditionally hide / show the axis labels so that we only see the X-axis labels at the bottom of the trellis and Y-axis labels on the left side of the trellis.
Y-axis uses an expression that only shows the axis for the MIN ranked member for Subcategory
Fields!SubcategoryRank.Value = MIN(Fields!SubcategoryRank.Value, "Trellis")
and X-axis uses the MAX ranked member for Region
Fields!RegionRank.Value = MAX(Fields!RegionRank.Value, "Trellis")
It also helps to check the Hide first and last labels along this axis for the Y-axis as the zero value tends the throw off the alignment with the other charts where the X-axis is hidden.
The sample .rdl file can be downloaded here.