MDX to get all elements of a ParamCube

[schlemiel29]

I have a cube (}TM1Config_UserReportDimElements), where I store selections of the user. Because they can select multiple elements of a dimension, I have a dimension "}numbers" which only include number elements "1" ... "20". So I can store each element the user has has chosen in a list in the elements "1" ... "20" without holes.

Code: Select all

{
  FILTER(
    TM1SUBSETALL([}KW_N_Von]),
    SUM(
      {[}Numbers].[AllMembers]},
      IIF(
        [}TM1Config_UserReportDimElements].(
          StrToMember("[}Clients].[" + USERNAME + "]"),
          [}TM1Config_Reports].[R371],
          StrToMember("[}TM1Config_Dimensions].[}KW_N_Von]"),
          [}Numbers].CurrentMember,
          [}TM1Config_ReportDimElement].[Element]
        ) = [}KW_N_Von].CurrentMember,
        1,
        0
      )
    ) > 0
  )
}

Unfortunately this MDX does not return any element. It should return two elements, because I entered them in the slot for dimension "}KW_N_Von" in cube }TM1Config_UserReportDimElements. Any idea?

[Wim Gielis]

Can you break it down and test piece by piece ?
Also, inserting hardcoded text instead of functions can often lead to identifying the place(s) where it goes wrong.

[schlemiel29]

Yes, I already did. Example:

Code: Select all

{
  FILTER(
    TM1SUBSETALL([}KW_N_Von]),
    SUM(
      TM1SUBSETALL([}KW_N_Von]),
      IIF( 1 = 1, 1, 0 )
    ) > 0
  )
}

This also returns nothing. If I change "> 0" to "= 0" I get all elements. I have totally no clue!

[burnstripe]

I'll also add, the original query won't work as I'm guessing you are trying to run this in the subset editor for the dimension }kw_n_von

In that scenario the currentmember statement will only work on that dimension, it won't work on [}Numbers].Currentmember because it's not in context

You'd have to add each line number individually

[schlemiel29]

OK, thanks. I realized that by simple try and error. So my solution is a cube with rules and then request that cube with checking values > 0. Unfortunatly I need a cube for every dimension with this filter feature.

[gtonkin]

Can you maybe post a spreadsheet example or some screenshots of what you have and what you expect, may be easier to assist.