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?
MDX Question
-
- Posts: 41
- Joined: Fri Jun 02, 2017 6:35 pm
- OLAP Product: Planning Analytics
- Version: 2.0...
- Excel Version: 2016
-
- 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
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:
DESCENDANTS is a substitute for TM1DRILLDOWNMEMBER( {} , ALL RECURSIVE).
But if filtering is required, then the code is a little bit more complicate:
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)}
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
-
- Posts: 41
- Joined: Fri Jun 02, 2017 6:35 pm
- OLAP Product: Planning Analytics
- Version: 2.0...
- Excel Version: 2016
Re: MDX Question
This is perfect, thank you!