MDX to filter on string value with stacked dimensions
Posted: Wed Nov 15, 2017 1:22 am
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
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