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

Q: MDX query to get sales by product line for specific period plus number of months with sales

A: Function Count(<Set>, ExcludeEmpty) counts number of non empty set members. So if we crossjoin Month with measure we will get set that we can use to count members.

Query example:

WITH Member [Measures].[Months With Non Zero Sales] AS
COUNT(CROSSJOIN([Measures].[Sales Amount]
, DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month]))
, ExcludeEmpty
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Non Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

Result:

Sales Amount Months With Non Zero Sales
Accessory $1,987,396.37 19
Components $454,644.34 18
Mountain $24,430,307.51 19
Road $24,919,506.74 19
Touring $16,010,837.10 13

To test results we will run query to see sales for product line "Touring" in same period at the month level:

SELECT [Measures].[Sales Amount] ON 0
, DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) ON 1
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Product Line].[Touring])

Result of this query:

January 2003 (null)
February 2003 (null)
March 2003 (null)
April 2003 (null)
May 2003 (null)
June 2003 (null)
July 2003 $833,628.72
August 2003 $1,396,630.12
September 2003 $1,525,590.86
October 2003 $951,177.65
November 2003 $1,401,185.76
December 2003 $1,786,885.49
January 2004 $878,385.55
February 2004 $1,211,687.10
March 2004 $1,515,269.08
April 2004 $1,101,876.09
May 2004 $1,552,358.79
June 2004 $1,854,142.35
July 2004 $2,019.55
August 2004 (null)