MDX Question

Post Reply
AskAndAnswer
Posts: 41
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

MDX Question

Post by AskAndAnswer »

Hello,
I am trying to write MDX that would return leaf elements for multiple consolidations. For example,
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, "M01")}, ALL, RECURSIVE )}, 0)}
will return all dates under M01 (Month1). Is there a way to write MDX so it would return all dates for M01, M02 and M03 without using UNION?
babytiger
Posts: 78
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: MDX Question

Post by babytiger »

In your MDX example, TM1FILTERBYPATTERN is not doing anything, since you don't have a wildcard (*) in your criteria.

If what you wanted is just to specify, months (M01 to M12), then bring through all the children/leaf elements, then this may help:

Code: Select all

{TM1FILTERBYLEVEL(
	{DESCENDANTS(
		{[Period].[M01],[Period].[M02],[Period].[M03],[Period].[M04],[Period].[M05],[Period].[M06]}
	)},
0)}
DESCENDANTS is a substitute for TM1DRILLDOWNMEMBER( {} , ALL RECURSIVE).

But if filtering is required, then the code is a little bit more complicate:

Code: Select all

{TM1FILTERBYLEVEL(
	{DESCENDANTS(
		{FILTER(
			{TM1SUBSETALL([Period])},
			LEFT([Period].CurrentMember.Name,1)= "M" or LEFT([Period].CurrentMember.Name,2)= "M0")},
				)},
0)}
MK
AskAndAnswer
Posts: 41
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: MDX Question

Post by AskAndAnswer »

This is perfect, thank you!
Post Reply