CODEWITHSUNDEEP
ssasmdx
whytheq
whytheq

Reputation: 35557

Adding further information in the context of the cell set

I'd like to be able to search the [Employee Department] hierarchy - within any of it's levels using a string: the following does this ok and finds all members related to the string "Control"

Now I have tried to add a cross join so that I can always see the department name for each of the rows and then ORDER by the department. If the commented section is uncommented I unfortunately get a full cartesian product - I only want the departments in the context of the members found by the filter - is this possible? 

WITH 
    MEMBER [Measures].[LevelName] AS
        [Employee].[Employee Department].Level.Name
    MEMBER [Measures].[LevelNumber] AS
        [Employee].[Employee Department].Level.Ordinal
   SET [Set_TargetEmp] AS
        {
        FILTER(
            [Employee Department].AllMembers,
                (
                InStr(
                    1, 
                    [Employee].[Employee Department].currentmember.name, 
                    "Control") <> 0
                ) 
            )
        }
SELECT
//  ORDER(
//      [Department].members,
//      [Department].[Department].MEMBERVALUE
//      ) 
//  *
    ORDER(
        DESCENDANTS(
            [Set_TargetEmp],
            [Employee].[Employee Department].[Department],
            SELF_BEFORE_AFTER
            ),
        [Measures].[LevelNumber],
        BASC
    ) as X
    ON 1,
    {
    [Measures].[LevelName],
    [Measures].[LevelNumber] 
    } ON 0
FROM [Adventure Works]

Upvotes: 0

Views: 54

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Assuming you use Department the Department dimension, and the Employee Department is in a different dimension named Employee, you get a cross product. Analysis Services only applies "autoexists" within the same dimension. Across dimensions, you must apply this logic explicitly like this:

   ORDER(
      Exists([Employee].[Department Name].[Department Name].members,
             [Set_TargetEmp]
            ),
      [Department].[Department].MEMBERVALUE
      )

for the commented block in your code should deliver what you want.

In case you have more than one measure group that relate to both the department and the employee dimensions, you should state the name of the measure group to use as the third argument of Exists. This is a string argument, hence this name should be included in quotes.

Upvotes: 1

Related Questions