CODEWITHSUNDEEP
mdxolapssas
Farshid Zaker
Farshid Zaker

Reputation: 1990

Merging two tuples in the same hierarchy into one (in MDX WITH SET command)

The following MDX query returns measure X on 3 tuples: 2001, 2002-1 and 2002-2. What I am trying to do is merging 2002-1 and 2002-2 into one tuple and have the measure X for 2001 and 2002-1&2. Using SUM function is not possible. Because measure X is used on other axis.

with 
member v as [Measures].[X]
set w as {[Dim Date].[Calendar Date].[Year].&[2001],
[Dim Date].[Calendar Date].[Month].&[1]&[2002],
[Dim Date].[Calendar Date].[Month].&[2]&[2002]}
select w on 0, v on 1
from [DS];

Upvotes: 0

Views: 2014

Answers (1)

ic3
ic3

Reputation: 7680

You can add calculated members in [Dim Date]:

with 
member [Dim Date].[Calendar Date].[2002 All] as [Dim Date].[Calendar Date].[Month].&[1]&[2002] + [Dim Date].[Calendar Date].[Month].&[2]&[2002]
...

You can use aggregate or sum functions if your prefer this syntax.

Upvotes: 1

Related Questions