Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2012 Forums  Analysis Server and Reporting Services (2012)  Ageing report in MDX query

Author  Topic  padh Starting Member 1 Post Posted - 2014-12-11 : 02:16:17 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] Thanks & Regards Padh