Search code examples
excelssasmdxolapcube

MDX calculated Member not allowed multiple hierarchy tuple

I've using a sql Table to generate filters on each dimensions for a value in a SSAS Cube.

SQL Table

The MDX Query is based on the column Query below, the calculated member is:

   AGGREGATE
    (
      IIF(Query= "" or ISEMPTY(Query),
           [Code].[_KeyQuery].[ALL],
           StrToTuple('('+ Query+')')
           ),[Measures].[Value]
    )

I have to work with pivot Table in Excel. It works perfectly, the value is correctly filter on each dimension member. If i use a query like this, it's ok.

[Level].[LevelCode].&[A],[Status].[StatusCode].&[ST]

But now i need adding the possibility to filter on multiple dimensions members. For exemple, using a query : 

[Level].[LevelCode].&[A],[Level].[LevelCode].&[X],[Status].[StatusCode].&[ST]

It doesn't works, i've try changing the query like this:

{[Level].[LevelCode].&[A],[Level].[LevelCode].&[X]},[Status].[StatusCode].&[ST]

but the StrToTuple() function causes error. I don't know how to filter in multiple values for a same dimension hierarchy.

Solution

  • If it will always be a tuple then no need to use AGGREGATE just a tuple should return the value:

      IIF(
    Query= "" OR ISEMPTY(Query),
    (
      [Code].[_KeyQuery].[ALL]
     ,[Measures].[Value]
    )
   ,StrToTuple('('+ Query +',[Measures].[Value])')
  )

    Or this version:

       StrToTuple(
      '('
      + IIF(
          Query= "" OR ISEMPTY(Query)
         ,[Code].[_KeyQuery].[ALL]
         ,Query 
       )
     +',[Measures].[Value])'
    )

    possible approach for decision between tuple and set

    Add a column to your control table "TupleOrSet" with values of either "T" or "S". Then you could amend your code to something like this:

      IIF(
    Query= "" OR ISEMPTY(Query),
    (
      [Code].[_KeyQuery].[ALL]
     ,[Measures].[Value]
    )
   ,IIF(
       TupleOrSet = "T"
      ,StrToTuple('('+ Query +',[Measures].[Value])')
      ,AGGREGATE( StrToSet('{'+ Query +'}'), [Measures].[Value])
    )
  )

    note

    A tuple is a definite point in the cube space so cannot therefore be made up of two members from the same hierarchy - this would create coordinates that are non-determinant