Recursive Report to Traverse Time Hierarchy

I need to create a report that allows end-users to select multiple items from the Date hierarchy (e.g., Year-2007, Q4-Year-2006, November-2007) and then the report should show a chart and a table with measures for the selected periods like this one (using the Adventure Works cube from SQL 2012)

And the next requirement is to allow users to click on the date in the table (e.g., Q4 CY 2006) and the reports will show the same information but for all direct children of the selected time period  (e.g., Oct 2006, Nov 2006, Dec 2006) like this one:

And if a user clicks on the month it will get the report for all dates in the selected month.

I started with Query designer and placed "Internet Sales Amount", Category (from the Product dimension) , and the Date.Calendar hierarchy. I also add the Date.Calendar hierarchy to the parameters panel.

When I changed the design mode the MDX script generated by the Query Designer is this (slightly reformatted)

SELECT 
NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, 

NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS * 
     [Date].[Calendar].[Date].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

FROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNS 

FROM [Adventure Works]) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


So the @DateCalendar parameter contains the initial selection made by the user using the unique names for each item.

When a user clicks on a date in the table we need to call the same report and pass it the unique name of the selected date item.
Also we need a way to find out names for the dates.
And we do not need all dates (as crossjoin with[Date].[Calendar].[Date].ALLMEMBERS) defines.

So we will modify the MDX query by adding two calculated members for the Unique name and Member name, and instead of crossjoin with all the dates we will crossjoin with StrToSet(@DateCalendar)



WITH 
MEMBER [Measures].[DateName] AS [Date].[Calendar].CurrentMember.member_caption
MEMBER [Measures].[UName] AS [Date].[Calendar].CurrentMember.UniqueName

SELECT 
NON EMPTY {  [Measures].[Internet Sales Amount], [Measures].[DateName], [Measures].[UName]
} ON COLUMNS, 
NON EMPTY { 
(  [Product].[Product Categories].[Category].ALLMEMBERS *  StrToSet(@DateCalendar) )
} 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The returned amount of data is much smaller now and two new columns (dateName and Uname) will be used in the report

Now when the report is initially launched by the user we need to crossjoin products with StrToSet(@DateCalendar).

But when the user clicks on a date in the table we'll pass a unique name for this member and we need to show all children for the member.
So we need crossjoin with
         StrToSet(@dateCalendar + '.Children")

But when we reached the date level in the date hierarchy we need again crossjoin with
                     StrToSet(@DateCalendar)

Thus, we need a new hidden report parameter (let's call it @ShowChildren) with type Boolean and the default value set to FALSE

Now we can add this report parameter as a parameter to the MDX query

And having the parameter ready the MDX query itself will change as this


WITH 
MEMBER [Measures].[DateName] AS [Date].[Calendar].CurrentMember.member_caption
MEMBER [Measures].[UName] AS [Date].[Calendar].CurrentMember.UniqueName
SELECT 
NON EMPTY {  [Measures].[Internet Sales Amount], [Measures].[DateName], [Measures].[UName]
} ON COLUMNS, 
NON EMPTY { 
(  [Product].[Product Categories].[Category].ALLMEMBERS *  
-- StrToSet(@DateCalendar) 
iif (  @ShowChildren AND StrToSet(@DateCalendar).Count=1 And  NOT IsLeaf(StrToMember(@DateCalendar)), StrToSet(@DateCalendar + ".Children"),  StrToSet(@DateCalendar))

)
} 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Now the last step is to define action on the text box with the date name in the report.

We call the report itself (Report1) and pass to it two parameters:

- DateCalendar with value from the [Uname] field

- ShowChildren with value TRUE

That's all we need. 

Now the report when called initially will get date periods as selected by the user (it might be one or multiple items but in any case the Internet Sales amount will be shown for the selected dates only).

When the user clicks on any date in the table the report will show the Internet Sales amount for the direct children of the data user clicked on.

When we reach the bottom of the hierarchy (e.g., January 18, 2007) and user clicks again on the date the report will show the same information because that member is a leaf member in the hierarchy.

And just for completeness, the category group in the chart is [DateName] and Series Group is [Category]