Search code examples
mdxolapmondrian

Filter dimensions for a specific member in MDX

I need to filter the dimension [Line] just for a specific [Year].Member.

For example, [Time].[2004] to show results ([Product].[Line].[Classic Cars] and [Product].[Line].[Ships]) - excude the rest of [Product].[Line] members for [Time].[2004] but do not exclude [Product].[Line] members for the other [Time].Members.

I need a code compatible with Mondrian. Any suggestion?

SELECT
    NON EMPTY {[Measures].[Sales]} ON COLUMNS,
    NON EMPTY NonEmptyCrossJoin([Time].[Years].Members, [Product].[Line].Members) ON ROWS
FROM 
    [SteelWheelsSales]

enter image description here

Solution

  • Something like this should work:

    SELECT 
  NON EMPTY 
    {[Measures].[Sales]} ON COLUMNS
 ,NON EMPTY 
    {
      (
        [Time].[2004]
       ,{
          [Product].[Line].[Classic Cars]
         ,[Product].[Line].[Ships]
        }
      )
     ,NonEmptyCrossJoin
      (
        Except
        (
          [Time].[Years].MEMBERS
         ,[Time].[2004]
        )
       ,[Product].[Line].MEMBERS
      )
    } ON ROWS
FROM [SteelWheelsSales];