Conditionally Hiding Axes for Trellis Displays

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.

• Trellis / Lattice displays
• Win / Loss Charts
• Bullet Graphs in SSRS 2008
• Bullet Graphs in SSRS 2005

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.

WITH
  SET [SalesOrderedSubcategories] AS
    Order
    (
      [Product].[Product Categories].[Category].[Bikes].Children
     ,[Measures].[Sales Amount]
     ,BDESC
    )
  SET [SalesOrderedRegions] AS
    Order
    (
      [Sales Territory].[Sales Territory].[Region].MEMBERS
     ,[Measures].[Sales Amount]
     ,BDESC
    )
  MEMBER [Measures].[SubcategoryRank] AS
    Rank
    (
      [Product].[Product Categories].CurrentMember
     ,[SalesOrderedSubcategories]
    )
  MEMBER [Measures].[RegionRank] AS
    Rank
    (
      [Sales Territory].[Sales Territory].CurrentMember
     ,[SalesOrderedRegions]
    )
SELECT
  {
    [Measures].[Sales Amount]
   ,[Measures].[SubcategoryRank]
   ,[Measures].[RegionRank]
  } ON COLUMNS
,(
    [SalesOrderedSubcategories]
   ,[SalesOrderedRegions]
   ,[Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS
  ) ON ROWS
FROM [Adventure Works];

Set the SORT property of each group to its respective GroupNameRank calculated member, this way we can be sure that in our example the top ranked Subcategory (Road Bikes) will be the left-most item and the lowest ranked Region (Southwest) will be the bottom-most item in the trellis.

 

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

=IIF(
    Fields!SubcategoryRank.Value = MIN(Fields!SubcategoryRank.Value, "Trellis")
    , false
    , true
)

and X-axis uses the MAX ranked member for Region

=IIF(
    Fields!RegionRank.Value = MAX(Fields!RegionRank.Value, "Trellis")
    , false
    , true
)

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.