Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

DrillDownX MDX Functions and Calculated Members in R2

leave a comment »

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.

Written by Chris Webb

October 6, 2010 at 11:45 am

Posted in MDX

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

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: