Report Portal

MDX query to get sales by prod line for period plus number of months with NON EMPTY sales?

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

A: You can use COUNT() function with ExcludeEmpty option. For count function you specify set that is corssjoin of Date members at the month level and measure that you are interested in.

WITH Member [Measures].[Months With Above Zero Sales] AS
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount]
, ExcludeEmpty
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above 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])

Results will be:

Sales Amount Months With Above 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

Tags: mdx, faq, non empty


2007-2015 VidasSoft Systems Inc.