SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How do you write MDX query that returns measure ratio to parent value?

A: Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is independant on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]
} ON 1
FROM [Adventure Works]

Results of query above:

Order Count Order Count Ratio To Parent
CY 2001 1,379 4.38%
CY 2002 3,692 11.74%
CY 2003 12,440 39.55%
CY 2004 13,944 44.33%
All Periods 31,455 (null)

If you query data at month level, ratio will be calculated comparing to level quarter:

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL
, [Measures].[Order Count]
/
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
)
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count]
, [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[Calendar Quarter].&[2003]&[4], 1)
, [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
} ON 1
FROM [Adventure Works]

Result of this query:

Order Count Order Count Ratio To Parent
October 2003 1,779 29.95%
November 2003 1,888 31.79%
December 2003 2,272 38.26%
Q4 CY 2003 5,939 58.87%