SSAS MDX Except statement issue

Question

I am working on an requirement in Cube MDX like data for year 2008 and prior should not be displayed in report.

So thought of using MDX 'EXCEPT' function in SCOPE statement to exclude the mentioned years.

Time dimension is hierarchy with structure Year-->Quarter-->Month

Initial code was like

SCOPE([Measure Item].[Measure Item].&[SAL]);
  SCOPE(EXCEPT({[Time].[Time].[Year]},{[Time].[Time].&[2006],[Time].[Time].&[2007],[Time].[Time].&[2008]}));    
    this=([Measure Item].[Measure Item].&[SAL]);      
   END SCOPE;   
END SCOPE;

With the code after deploying, data for year 2008 and prior got excluded. But the issue here is, I can only see data on Grand Total on each Year. Please see the example in below screenshot, I cannot see data on Quarter level and month level.

Not able to see data on quarter and month level

Tried few other codes with descendants function, which is working in SSMS but when write it with SCOPE statements its not working.

Code written in SSMS:

  select [Measures].[Value] on 0,
    EXCEPT({[Time].[Time].members} ,
    {descendants([Time].[Time].&[2006],0,SELF_AND_AFTER),descendants([Time].[Time].&[2007],0,SELF_AND_AFTER)}) on 1 from [TOPAS Reporting]

Working in SSMS but not working in SCOPE statement

I can see data on even Quarter and month level. But same code is not working in SCOPE statements of cube MDX.

Please help me to solve this issue and let me know for further details .

Thanks in advance.

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-10-30T19:53:54

Do you really need a scope statement?

You can achieve it by using the following calculation:

Aggregate(
      existing [Time].[Time].&[20090101]:NULL,
      [Measure Item].&[SAL]
)

where [Time].[Time].&[20090101] is the first day on the Day level.

SSAS MDX Except statement issue

1 Answers