MDX: Use a Element/member name in a filter

[sander]

Hello all,

I want to use a Element/member name in a filter.
For example I Have a dimension period with elements 201301 to 202512. I have an attribute 'Current Period' which is 201311 for all the elements. And now I want to make a MDX statement which gives me back the Current and all the following periods.
If I make an attribute Period Name (Text or Alias) and populate it with the element names the next MDX gives me what I want;

Code: Select all

{FILTER( {TM1SUBSETALL( [Period] )}, [Period].[Period Name] >= [Period].[Current Period])}

But of course I do not want to make an extra attribute and just use the element name. I just can't find how to do this, had to be quite simple I thought......

Can someone help me out here!

Thnx Sander

[ardi]

Try the following MDX:

{FILTER( {TM1SUBSETALL( [Period] )}, [Period].CurrentMember.Name >= [Period].[Current Period])}

It should give you the result you want

All the best

[sander]

Thnx! It is simple!

[paulsimon]

Hi

If you have the current month in a control cube you can also do the following:

{FILTER( {TM1SUBSETALL( [zTD_Effective_Mth] )},
[zTD_Effective_Mth].CurrentMember.Name >=
[zTD_Info].( [zTD_Any].[Any],[zTD_Info_Meas].[Curr Mth Mnn])
)}

Where zTD_Info is a cube with two dimensions
zTD_Any with a single element called Any
zTD_Info_Meas with various measures one of which is called Curr Mth Mnn

One thing to be careful of is that you need to ensure that the alias on the month dimension gives the same format as the one you are comparing against.

Regards

Paul Simon

[sander]

Paul, that's indeed also an option that I could use, haven't chosen it here because I also need the attribute 'Current Period' in BI. In BI the solution I chose here is the most simple, I Have the following code in BI to get the Current Period

Code: Select all

Item(Filter([Order Planning].[Period].[Period],caption([Order Planning].[Period].[Period])=[Order Planning].[Period].[Period].[Current Period]),0)

If there is a way to determine the 'Current Period' in BI without the attribute workaround, I would love to hear it.

Thnx, Sander

[lotsaram]

One thing to be careful of is that you need to ensure that the alias on the month dimension gives the same format as the one you are comparing against.

Hi Paul - are you sure it works with aliases? I have used this kind of MDX a lot in the past to drive dynamic subsets for current reporting period but I found I could only ever get it to work if the cube string value was the same as the principal element name, if the string is an alias the subset returns empty.

[paulsimon]

Hi

If you are using BI the approach we used was to create a consolidation of the relevant periods and then used BI macro script to show that in prompts etc.

Regards

Paul Simon

[sander]

Lotsa, Paul,

Tested it with aliases works in the form;
{FILTER( {TM1SUBSETALL( [Period] )}, [Period].[Period Name] >= [App Var].([App Var Value].[Value],[App Var Measure].[Current Period]))}
But not in;
{FILTER( {TM1SUBSETALL( [Period] )}, [Period].CurrentMember.Name >= [App Var].([App Var Value].[Value],[App Var Measure].[Current Period]))}
Where;
Principle Element name like YYYYMM (example 201312)
Alias 'Period Name' like YYMM (example 1312)
'Current Period' like YYMM (example 1312)

Seems logical to me.

Paul, Use the Current period in a daily report with no selection needed, so I do not need BI macro script. Thnx for the tip, may need it later on.

Regards Sander

[lotsaram]

I think I always used CurrentMember.Name so that makes sense.

[paulsimon]

Hi

To use an alias you can change the MDX to this

{FILTER( {TM1SUBSETALL( [zTD_Effective_Mth] )}, [zTD_Effective_Mth].CurrentMember.Properties('CalMthName') >=
[zTD_Info].( [zTD_Any].[Any],[zTD_Info_Meas].[Curr Mth Mmm])
)}

So instead of CurrentMember.Name you put CurrentMember.Properties( '<the alias>' )

However, my example uses alpha months eg Aug for August which doesn't really make sense to do as it is then doing an alpha comparison. Therefore for this sort of thing you would probably want to stick to the raw element name anyway which is hopefully numeric.

In practice you would probably need something like this

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [zTD_Effective_Mth] )}, 0 ) } , [zTD_Effective_Mth].CurrentMember.Name >=
[zTD_Info].( [zTD_Any].[Any],[zTD_Info_Meas].[Curr Mth Mnn])
)}

with a TM1FilterByLevel to keep the selection down to level 0 otherwise you will end up selecting Quarter consolidations, etc.

Regards

Paul Simon