DrillDownX MDX Functions and Calculated Members in R2

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:

image

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.

image

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]

image

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]

image

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s