CODEWITHSUNDEEP
ssasmdx
John
John

Reputation: 47

Filter in WHERE using Named Set

Is it possible to use an aggregated named set to filter queries in MDX? I don't want the set items in the result set so moved it to the WHERE, however this seems to cause all measures to return (null).

First, I build a set using a couple of members from a staff hierarchy:

WITH 
  SET [Combined] as {
   [Staff].[Group].[Practice Group].&[04],
   [Staff].[Group].[Practice Group].&[06]
  }

Then I aggregate that set into a new member:

  MEMBER [Staff].[Group].[Group Combo] AS Aggregate([Combined])

Usually I would then use this in my query as a reporting area, possibly with hierarchize (depending on the query) and everything is fine. However this time I needed to filter the data based on this combination of members e.g:

WHERE ([Staff].[Group].[Group Combo])

This gave me (null) values and if I used [Combined] I received a cyclical reference error, however using the below worked fine:

WHERE ({[Staff].[Group].[Practice Group].&[04],[Staff].[Group].[Practice Group].&[06]})

Am I overlooking something here? Or using the wrong approach? Maybe it is just a quirk of the cube I am querying?

Upvotes: 1

Views: 848

Answers (1)

whytheq
whytheq

Reputation: 35557

I would say that the behaviour is related to the order in which the processor is executing the clauses within your query:

Logical order an MDX query is processed

WHERE happens before WITH

You mentioned that the following works fine - specifying explicit members in the where clause it is pretty standard and fast: 

WHERE ({[Staff].[Group].[Practice Group].&[04],[Staff].[Group].[Practice Group].&[06]})

You could add it to a sub-select just as it is on the 0 axis:

SELECT
 ...
 ...
FROM
  (
    SELECT
       {[Staff].[Group].[Practice Group].&[04],[Staff].[Group].[Practice Group].&[06]} ON 0
    FROM [YourCube]
  );

Upvotes: 2

Related Questions