TM1 Forum

Hi fellows,

Ran into a brick wall here...

MDX below is not happy:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)},[Customer Active Status].([Period].([System Info].( StrToMember([System Parameters].[Current Reporting Period]) ) ,[Active Status Measure].[Value])=0))}

What I am trying to do here is to filter out customer dim based on Customer Active Status cube value of 0... while its straightforward, the culprit is that I am trying to replace [Period].[Element] qualifier with the current reporting period string from the System Info cube (dim [Period]. [element from System info cube] ) ... .

this part here: [Customer Active Status].([Period].([System Info].( StrToMember([System Parameters].[Current Reporting Period]) )

Error im getting:

syntax error at or near: '[System Info].(StrMember( ......

I tried to pull that period string in the actual period dim with

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, [System Info].(StrToMember("[System Parameters].[Current Reporting Period]"),[System Measures].[String]))}

and worked happily. . .

Please suggest. I think I have been looking at it for too long now

You migh want to use EXCLUDE mdx rather than go that way round

Darkhorse wrote:You migh want to use EXCLUDE mdx rather than go that way round

Hi Darkhorse,

Thanks for the suggestion, however I dont see how is it going to help to be honest. In simple words I am trying to create Dynamic variable behavior of the Period dimension based on the System Info cube string, e.g CurrentReporting period 2013-09... and use this dynamic variable to filter out Customers based on their values in Customer Active Status cube for that period/value . Does it make sense?

If I was passing this mdx in excel i would use something along this lines:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)},[Customer Active Status].([Period].['&CellReference&'],[Active Status Measure].[Value])=0))}

As you can see from the example above, im trying to create dynamic variable (CellReference) inside subset editor

Thanks
EvgenyT

Hi you want to make things dynamic
PPeriod= q4;

Use ['| pPeriod|']

And the same for the exclude. If you use exclude it will again be easily replaced using the above solution and make the variable dynamic

Darkhorse,

This is not in TI but in the Subset Editor.

Giggle.

Evgeny, try something like this.

TM1Member(TM1SUBSETALL([Period]).Item([System Info].([System Parameters].[Current Reporting Period],[System Info Measure].[String])) ,0)

Given you have a System Info Measure dimension in your cube with an element 'String'.
This is not tested exactly like that as I had to change a few things around trying to fit you names.

The above can just be a dynamic subset in the period dimension called "Current Period" and elsewhere you could refer to it with something like:

tm1member(tm1subsettoset([Period], "Current Period").item(0),0)

Cheers

Hi Gregor,

Thank you for your suggestion. Yes I was leaning toward using subset to set since the nesting was getting out of hand there

I guess I could easily store current period under consolidation in Period dimension (and rebuild it with TI every so often) and just reference that in mdx, but I was trying to prove to myself that I can make it work

Darkhorse - thanks for your contribution, but I think you misunderstood the problem. Passing dynamic variables in subset editor is different from TI or Active Form. There is no easy way of doing it, as far as I know.

Thanks guys,

Evgeny

Gregor Koch wrote:
TM1Member(TM1SUBSETALL([Period]).Item([System Info].([System Parameters].[Current Reporting Period],[System Info Measure].[String])) ,0)

Gregor, unfortunate mdx threw Failed to Compile error with syntax above, however I was able to achieve it with the following syntax:

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, [System Info].(StrToMember("[System Parameters].[Current Reporting Period]"),[System Measures].[String]))}

And Victorious at last:

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)},[Customer Active Status].(tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0),[Active Status Measure].[Value])=1 )}



I have removed reference to [Period]. [ElementName] and replaced it as you suggested with (tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0)

I just wish there was an easier way of doing it ....

Thanks Gregor,

Evgeny