Indentation of hierarchy parameters in Reporting Services pickers

When the requirement is to have a nice indented Date Hierarchy picker in you Reporting Services report, along the lines of...

here is the technique.

1. Extend MDX statement

You need to extend the MDX query in the Dataset so that it brings in 'level' information. Something like:


WITH 
MEMBER [Measures].[ParameterValue] 
AS '[My Date].[Financial Year-Quarter-Month].CURRENTMEMBER.MEMBER_CAPTION' 
MEMBER [Measures].[ParameterLevel] 
AS '[My Date].[Financial Year-Quarter-Month].CURRENTMEMBER.LEVEL.ORDINAL' 
SELECT {
   [Measures].[ParameterValue], 
   [Measures].[ParameterLevel]} 
ON COLUMNS , 
DESCENDANTS(
   [My Date].[Financial Year-Quarter-Month].[All Time],
   [My Date].[Financial Year-Quarter-Month].[Financial Month],
   SELF_AND_BEFORE )
ON ROWS 
FROM 
   [My Cube]

Note that you'll need to have built a Hierarchy to hold the Year->Quarter->Month structures.

2. Add calculated field to Dataset

Call it ParameterCaptionIndented and the calculated field expression will be:

=Space(3*(Fields!ParameterLevel.Value-1)) + Fields!ParameterCaption.Value

3. Define parameter fields

Create a parameter to hold the values returned by your querySet the following properties:

Value Field = ParameterValue

Label Field = ParameterCaptionIndented

Hey presto, this should work. Can be used to do other hierarchies such as Organisation hierarchies as well.