Search code examples
filteringssasmdxssas-tabular

MDX: Define dimension sub set and show the total

Since in MDX you can specify the member [all] to add the aggregation between all the members of the dimension, if I want to show the totals of a certain measure I can build a query like 

SELECT {
    [MyGroup].[MyDimension].[MyDimension].members, 
    [MyGroup].[MyDimension].[all] 
    } *
    [Measures].[Quantity] on 0
FROM [MyDatabase]

Now I want to filter MyDimension for a bunch of values and show the total of the selected values, but of course if I generate the query

SELECT {
    [MyGroup].[MyDimension].[MyDimension].&[MyValue1], 
    [MyGroup].[MyDimension].[MyDimension].&[MyValue2], 
    [MyGroup].[MyDimension].[all] 
    } *
    [Measures].[Quantity] on 0
FROM [MyDatabase]

it shows the Quantity for MyValue1, MyValue2 and the total of all MyDimension members, not just the ones I selected.

I investigated a bit and came up to a solution that include the generation of a sub query to filter my values

SELECT {
    [MyGroup].[MyDimension].[MyDimension].members, [MyGroup].[MyDimension].[all]
    } * [Measures].[Quantity] ON 0
FROM (
    SELECT {
        [MyGroup].[MyDimension].[MyDimension].&[MyValue1], 
        [MyGroup].[MyDimension].[MyDimension].&[MyValue2]
        } ON 0
    FROM [MyDatabase]
)

Assuming this works, is there a simplier or more straight forward approach to achieve this?

I tried to use the SET statement to define my custom tuple sets but then I couldn't manage to show the total.

Keep in mind that in my example I kept things as easy as possible, but in real cases I could have multiple dimension on both rows and columns as well as multiple calculated measures defined with MEMBER statement.

Thanks!

Solution

  • What you have done is standard - it is the simple way!

    One thing to bear in mind when using a sub-select is that it is not a full filter, in that the original All is still available. I think this is in connection with the query processing of the clauses in mdx - here is an example of what I mean:

    WITH 
  MEMBER [Product].[Product Categories].[All].[All of the Products] AS 
    [Product].[Product Categories].[All] 
SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,NON EMPTY 
    {
      [Product].[Product Categories].[All]                        //X
     ,[Product].[Product Categories].[All].[All of the Products]  //Y
     ,[Product].[Product Categories].[Category].MEMBERS
    } ON 1
FROM 
(
  SELECT 
    {
      [Product].[Product Categories].[Category].&[4]
     ,[Product].[Product Categories].[Category].&[1]
    } ON 0
  FROM [Adventure Works]
);

    So line marked X will be the sum of categories 4 and 1 but line Y will sill refer to the whole of Adventure Works:

    enter image description here

    This behavior is useful although a little confusing when using All members in the WITH clause.