Page 1 of 1

MDX to filter on string value with stacked dimensions

Posted: Wed Nov 15, 2017 1:22 am
by trentban
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

Re: MDX to filter on string value with stacked dimensions

Posted: Wed Nov 15, 2017 9:13 am
by AmbPin
Hello

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'
      )
  )
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:-
EmployeeIndexMeasure
Fred i01a
Fred i02b
Fred i03c
Bill i01b
Bill i02b
Bill i03c
Harryi01b
Harryi02a
Harryi03c

Using the syntax above on both dimensions would give you
EmployeeIndexMeasure
Fred i01a
Fred i02b
Harryi01b
Harryi02a
Hope that helps

Re: MDX to filter on string value with stacked dimensions

Posted: Wed Nov 15, 2017 10:56 pm
by trentban
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