mdx issues

[EvgenyT]

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

[Darkhorse]

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

[EvgenyT]

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

[Darkhorse]

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

[Wim Gielis]

Darkhorse,

This is not in TI but in the Subset Editor.

[Gregor Koch]

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

[EvgenyT]

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

[EvgenyT]

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