Earlier this year I blogged about one of the few MDX-related changes in SSAS 2008 R2, to do with the Subqueries=2 connection string property. In that post I mentioned that the DrillDownX family of MDX functions has also changed in R2 but didn’t go into details – so here, at last, are the details (not that they are very interesting)…
Basically, functions like DrillDownMember have got a new parameter that allows them to include any calculated members that are children of the member you’re drilling down on. Consider the following query on the Adventure Works cube:
WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 1
SELECT {} ON 0,
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,1)
ON 1
FROM [Adventure Works]
The DrillDownMemberTop function returns the top n members underneath the members in a given set, to allow you to control the number of members you see when you drill down. In the query above I’ve got a calculated member underneath the year 2003 and I’m drilling down to show only the top 1 children of 2003, which returns only H1 CY 2003:
Before R2 if I wanted calculated members to appear on an axis in a query like this I’d have to use AddCalculatedMembers, like so:
WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 2
SELECT {[Measures].[Internet Sales Amount]} ON 0,
ADDCALCULATEDMEMBERS(
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,1)
)
ON 1
FROM [Adventure Works]
And in this case, even though I’m only asking for the top 1 children I’m getting two children displayed: H1 CY 2003 and DEMOCALC.
Not good. Which is why this new parameter is needed. By using the INCLUDE_CALC_MEMBERS parameter instead of the AddCalculatedMembers() functions, like so:
WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 2
SELECT {[Measures].[Internet Sales Amount]} ON 0,
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,1
,,,INCLUDE_CALC_MEMBERS)
ON 1
FROM [Adventure Works]
WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 2
SELECT {[Measures].[Internet Sales Amount]} ON 0,
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,3
,,,INCLUDE_CALC_MEMBERS)
ON 1
FROM [Adventure Works]
I can make sure calculated members are treated like any other child when drilling down.
Thanks to Akshai once again for giving me the details of this change.
