MDX , create subset by value in other cube

[sk1080in]

I have a value written in one of the lookup cubes (Example: W010215, W050115, W040115) and on the basis of first 3 characters want to create subset in another dimension which has values like W01, W02, W03....

So whatever the value is in lookup cube, should become the basis for subset in another dimension.

Can somebody guide me if this is possible.

[rmackenzie]

TM1 supports the LEFT function - it may likely be un-documented and differ between versions etc.

Anyway, you can do something like this:

Code: Select all

{TM1FILTERBYPATTERN( 
  {TM1SUBSETALL([Interesting W Things])}, 
    LEFT(
      [Control Cube].(
        [Control Parameter Dimension].[Some Parameter],
        [Control Measure Dimension].[String]
      ), 
      3
    ) + "*"
)}

Note the concatenation of the wildcard character (*) at the end of the filter.

[sk1080in]

Thanks a lot, it is working fine, however giving all levels of elements starting with the 3 characters selected. Can it be restricted to 0 Level only ?

[ardi]

Thanks a lot, it is working fine, however giving all levels of elements starting with the 3 characters selected. Can it be restricted to 0 Level only ?

You can embed the above statement with TM1FILTERBYLEVEL and that will give you only level zero elements

[EvgenyT]

TM1 supports the LEFT function - it may likely be un-documented and differ between versions etc.

You are right, it's not documented anywhere.

BTW RIGHT function is supported too (not documented of course)

[sk1080in]

Thanks a lot everyone, it has worked for me. Last question...Can I pick something from between as well ? Like start from 4th Character and pick 2 Characters ?

[EvgenyT]

To my knowledge (and from testing) MID function is not supported in TM1 MDX.

[rmackenzie]

To my knowledge (and from testing) MID function is not supported in TM1 MDX.

But you can achieve the output of a MID if you have LEFT, RIGHT and LEN. E.g. this will work:

Code: Select all

{TM1FILTERBYPATTERN(
  {TM1FILTERBYLEVEL(
    {TM1SUBSETALL([Interesting W Things])}, 
    0 
  )},
  LEFT(
    RIGHT(
      [Control Cube].(
        [Control Parameter Dimension].[Some Parameter],
        [Control Measure Dimension].[String]
      ),
      LEN(
        [Control Cube].(
          [Control Parameter Dimension].[Some Parameter],
          [Control Measure Dimension].[String]
        )
      ) - 4 + 1
    ), 2
  ) + "*"
)}

[sk1080in]

Brilliant Solution, Thanks for that. You are a Star.