Function Count(, 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])
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])
No comments:
Post a Comment