# 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]

Thursday, December 11, 2014 2:11 AM

• 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]
``````
• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, December 17, 2014 3:04 AM

### All replies

• 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]
``````
• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, December 17, 2014 3:04 AM
• 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.

Tuesday, January 6, 2015 10:27 AM