Ageing Report MDX query

Question

User-944954964 posted

Hi,

I want to generate the ageing report. Below is my MDX query. But its giving the accumulated amount for the each period. I want the result like

                                   0-30 Days                30-60 Days              60-90 Days     > 90 Days

Customer Name 1           15                                  8                              11                     120

Customer Name 2           32                                 21                             7                        75

With
Member [Measures].[0-30] as
Sum(
ClosingPeriod([Cal Date].[Month].[Month], [Cal Date].[Month].[All]).Lag(1)
: ClosingPeriod([Cal Date].[Month].[Month], [Cal Date].[Month].[All].Lag(0))
,[Measures].[Master Count] )

Member [Measures].[31-60] as
ClosingPeriod([Cal Date].[Month].[Month], [Cal Date].[Month].[All]).Lag(2)
: ClosingPeriod([Cal Date].[Month].[Month], [Cal Date].[Month].[All].Lag(1))
,[Measures].[Master Count] )

Member [Measures].[61-90] as
Sum(
ClosingPeriod([Cal Date].[Month].[Month], [Cal Date].[Month].[All]).Lag(3)
: ClosingPeriod([Cal Date].[Month].[Month], [Cal Date].[Month].[All].Lag(2))
,[Measures].[Master Count] )

select
{[Measures].[0-30], [Measures].[31-60], [Measures].[61-90] } on 0,
{
[Customer].[Name].[Name].Allmembers
} on 1
from [My Cube]

Answer 1

User260050777 posted

• Your script looks ok to me. Try shifting the closingperiod section to a custom set:

  With
  set [x] as
    {ClosingPeriod([Cal Date].[Month].[Month], [Cal Date].[Month].[All])}
  Member [Measures].[0-30] as
    Sum(
     [x].item(0).Lag(1) : [x].item(0).Lag(0)
     ,[Measures].[Master Count] 
    )
  Member [Measures].[31-60] as
    Sum(
     [x].item(0).Lag(2) : [x].item(0).Lag(1)
     ,[Measures].[Master Count] 
    )
  Member [Measures].[61-90] as
    Sum(
     [x].item(0).Lag(3) : [x].item(0).Lag(2)
     ,[Measures].[Master Count] 
    )
  select
   {[Measures].[0-30], [Measures].[31-60], [Measures].[61-90] } on 0,
   {
   [Customer].[Name].[Name].Allmembers
   } on 1
  from [My Cube]
  

Answer 2

User-944954964 posted

Hi sdg,

Thanks for your help. In addition to that I want to include ">120" column. How to write the member for this.

And I want to include where clause with multiple dimensions. How to add it. Once again thanks for your help.