Just wondering if this is possible in MDX or i'm totally missing something easy.. (probably the latter)
I have a small cube holding string measures with 3 dimensions Employee, Index and Measure.
I want to filter an active form based on a value of one of the string measures. However how do i define the stacked row dimension (Employee & Index) members in each of the TM1RPTROWs so the active form will only display a specific string measure.
MDX for Employee dim
{FILTER({TM1SUBSETALL( [Employee] )},[String Cube].( [Index].[_____] , [Measure].[String1]) = "ABC")}
MDX for Index dim
{FILTER({TM1SUBSETALL( [Index] )},[String Cube].( [Employee].[_____] , [Measure].[String1]) = "ABC")}
I've tried the following which returned no results
- [dim].members & [dim].currentmember
- using subset names
- leaving the dimension out
The primer states that leaving a dimension out will use the CurrentMember when it used for a value but doesn't say anything about Strings
"Usually you will just need a specific named member (e.g. ‘All Entities’). If the dimension is omitted then the CurrentMember is used instead which is similar to using !dimension (i.e. “for each”) in a TM1 rule, and could return different results at a different speed."
Active Form would look like below with Employee and Index being TM1RPTROWs
Employee .... Index ..... String1
Emp1 .... 1 .... ABC
Emp1 .... 3 .... ABC
Emp6 .... 2 .... ABC
MDX to filter on string value with stacked dimensions
-
- Regular Participant
- Posts: 173
- Joined: Sat Mar 20, 2010 3:03 pm
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007-10
Re: MDX to filter on string value with stacked dimensions
Hello
You could look at the "Generate" syntax as you will need to evaluate the measure for each Index and each employee:-
You could apply this syntax to both the Employee and Index dimensions but I am not sure that any kind of dimension filtering is going bring for just the specified cube value, E.g. if we take the following cube:-
Using the syntax above on both dimensions would give you
Hope that helps
You could look at the "Generate" syntax as you will need to evaluate the measure for each Index and each employee:-
Code: Select all
{
Generate(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)}
, Filter(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Index] )}, 0)}
, [EmployeeMeasures].(
[Index].CurrentMember
, [Measure].sVal
) = 'a'
)
)
}
{
Generate(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Index] )}, 0)}
, Filter(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)}
, [EmployeeMeasures].(
[Employee].CurrentMember
, [Measure].sVal
) = 'a'
)
)
Employee | Index | Measure |
Fred | i01 | a |
Fred | i02 | b |
Fred | i03 | c |
Bill | i01 | b |
Bill | i02 | b |
Bill | i03 | c |
Harry | i01 | b |
Harry | i02 | a |
Harry | i03 | c |
Using the syntax above on both dimensions would give you
Employee | Index | Measure |
Fred | i01 | a |
Fred | i02 | b |
Harry | i01 | b |
Harry | i02 | a |
-
- Posts: 11
- Joined: Tue Aug 20, 2013 6:28 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 2013
Re: MDX to filter on string value with stacked dimensions
Thanks AmbPin
I think that's about the closest i'm going to get. Users can filter out the other measures in excel but it's a much smaller set of data to filter out rather than the whole cube.
Cheers
I think that's about the closest i'm going to get. Users can filter out the other measures in excel but it's a much smaller set of data to filter out rather than the whole cube.
Cheers