Report Portal

MDX-Query to show year level data for all years except last one, and month level data for last year

Q: I need an MDX query to show year level data for all years except the last one, and month level data for the last year.

A: Example of query

SELECT {NULL:
ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember).PrevMember
, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember)
, [Date].[Calendar].[Month])
}
ON 0
FROM [Sales Summary]

Results:

CY 2001 CY 2002 CY 2003 January 2004 February ... July 2004 August 2004
$11,331,808.96 $30,674,773.18 $41,993,729.72 $3,002,792.27 ... $50,840.63 (null)

Tags: mdx, faq, time dimension

 

2007-2015 VidasSoft Systems Inc.