Speaking the language of business intelligence with an Australian accent

Wednesday, October 21, 2009

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.

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.

TimKentTrellis

I made a few more tweaks and changes and came up with the report below

Trellis

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.

Sorting 

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.

AxisLabels

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.

HideLablels

The sample .rdl file can be downloaded here.

3 comments:

Tim K said...

Nice update :)

Nick Barclay said...

Thanks again for your initial post :)

Anonymous said...

nice stuff...
i am having little problem...

when i drag the matrix to the right it expands leaving behind white space between row heading and graph (unlike southeast and the graph to its right in your picture)...how do you manage to remove that white spaces...plz tell me something abt...