Parents of set in MDX

1
votes

I have a filter that selects a specific date in my date dimension, This will be passed in as a parameter into the MDX query.

Filter([Date Dimension].[Calendar Year Hierarchy].[Actual Date].members,
        [Date Dimension].[Actual Date].CurrentMember.Properties( "Name" ) = '2011-09-01 00:00:00.000')

I would now like to select the weeks and/or months in the hierarchy above that.

[Date Dimension].[Calendar Year Hierarchy].[Month] [Date Dimension].[Calendar Year Hierarchy].[Calendar Week]

I have tried several functions without much luck such as .Parent and DrillupLevel

I could be using them wrong or in the wrong spot,

thanks for the help

4
ssasmdx

4 Answers

2
votes

You could use the function GENERATE to get all ascendants:

Generate
 (
   {Filter([Date Dimension].[Calendar Year Hierarchy].[Actual Date].members,
        [Date Dimension].[Actual Date].CurrentMember.Properties( "Name" ) = 
'2011-09-01 00:00:00.000')},
{Ascendants([Date Dimension].[Calendar Year Hierarchy].CurrentMember)}
 )

Query using Adventure Works:

Select 
{[Measures].[Internet Sales Amount]} On Columns,
{Generate(
{Filter([Date].[Calendar].[Date].members,[Date].[Calendar].CurrentMember.Properties("Name") = 'April 1, 2004')}, 
{Ascendants([Date].[Calendar].CurrentMember)})} On Rows
From    [Adventure Works]
1
votes

If you know the level you're looking for you can use MDX Ancestor function instead of parent:

Ancestor([Date Dimension].[Calendar Year Hierarchy].currentmember, 
         [Date Dimension].[Calendar Year Hierarchy].[Month])

If no, it's fine using parent function. Note, using properties is not the quickest method to filter (for very large sets).

Why not using to StrToMember MDX function ?

StrToMember( ... here build your member with a string ... )

or if you can edit you mdx directly creating the statement directly with the help of a string builder ?

0
votes

so the answer i have at the moment is

Filter([Date Dimension].[Calendar Year Hierarchy].[Actual Date].members, [Date Dimension].[Actual Date].CurrentMember.Properties( "Name" ) = '2011-09-01 00:00:00.000') .item(0).parent

using the .item[0] to get the item in the set and the .parent to get that items parent (in my case i am actually calling it 3 times)

if anyone has any better ideas would love to here them.

0
votes

You can use STRTOSET and for each input parameter replace ] with ].Parent, this way you will have set of parents.