Page 1 of 1
MDX TM1FILTERBYPATTERN - from a subset
Posted: Fri May 25, 2012 8:46 pm
by pmakulski
I want a dynamic subset that is based on a value stored in another dimension-subset.
I'm starting with this, which works fine:
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Organization] )},
"EC4GB"
)}
But what I actually want to test on is in another dimension subset: [ForecastOrg].[DynamicTop]
( Forecast org is a less deep hierarchy that has a common upper level)
This dynamic subset contains a single item which happens to be "EC4GB"
I've tried lots of variations of
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Organization] )},
[ForecastOrg].[DynamicTop]
)}
but none of them compile. Can this be done? What is the syntax.
THanks.
Re: MDX TM1FILTERBYPATTERN - from a subset
Posted: Fri May 25, 2012 10:19 pm
by qml
Not sure what you want to achieve exactly, but in this particular case you don't need TM1FILTERBYPATTERN. All you need is:
Re: MDX TM1FILTERBYPATTERN - from a subset
Posted: Mon May 28, 2012 2:31 pm
by pmakulski
{[ForecastOrg].[DynamicTop]} gives me a subset from the ForecastOrg Dimension
I actually want a subset from the Organization dimension.
From what I can tell, mdx relates the subset to the dimension first mentioned.
ForecastOrg is shallow
Total
-EC4IND
-EC4GB
Organization is deep
Total
-EC4IND
--7 more levels below
-EC4GB
-- 7 more levels below this.
ie. EC4GB is a child in ForecastOrg, but a Consolidation in ForecastOrg.
Re: MDX TM1FILTERBYPATTERN - from a subset
Posted: Mon May 28, 2012 6:45 pm
by TrailRunnerMark
I think it's the DynamicTop that is giving you problems. It doesn't seem to be recognized by TM1. If you type it into the expression window of the Subset Editor you get the same "Failed to compile MDX expression" message. What is the logic for the MDX to pick just the single element EC4GB from the multiple elements in ForecastOrg ? Perhaps a differnt expression will work.
Re: MDX TM1FILTERBYPATTERN - from a subset
Posted: Mon May 28, 2012 10:55 pm
by rmackenzie
pmakulski wrote:I want a dynamic subset that is based on a value stored in another dimension-subset.
pmakulski wrote:{[ForecastOrg].[DynamicTop]} gives me a subset from the ForecastOrg Dimension
I actually want a subset from the Organization dimension.
So you want a dynamic subset in the ForecastOrg dimension that refers to a subset in the Organization dimension? I don't think that it is possible to refer to a dimension (in your MDX) other than the one for which you are writing the expression. Are you getting the 'incorrect dimension for subset' error?
Re: MDX TM1FILTERBYPATTERN - from a subset
Posted: Wed Jun 06, 2012 8:04 pm
by pmakulski
Doh!
OK, I've read over the suggestions. Thanks for them.
The easy answer to my problem above was to replicate the [DynamicTop] subset in the Organization dimension.
[ForecastOrg].[DynamicTop] is
Code: Select all
{FILTER( {TM1SUBSETALL([ForecastOrg])} ,
[Forecast].CurrentMember.Name =
[}ElementAttributes_}Clients].(StrToMember("[}Clients].[" + USERNAME +"]"),[}ElementAttributes_}Clients].[DefaultTop])
)}
So [Organization].[DynamicTop] can be
Code: Select all
{FILTER( {TM1SUBSETALL([Organization])} ,
[Organization].CurrentMember.Name =
[}ElementAttributes_}Clients].(StrToMember("[}Clients].[" + USERNAME +"]"),[}ElementAttributes_}Clients].[DefaultTop])
)}
So now they both return the single item EC4GB (if that is what the }Client DefaultTop attribute calls for.
But I think the reason I wanted to reference the other dimension's subset was to do more than one item. (I got around the problem by making the needed subset on the fly in a TI,
but I think it is still an interesting exercise).
Basically {TM1Filter( {TM1SUBSETALL([Organization]), {This other list of similar looking items from [ForecastOrg].[AnotherSubset] )};