MDX: Aggregates over a set

0
votes

What I am trying to achieves looks very simple, yet I cannot make it work. My facts are orders which have a date and I have a typical time dimension with the 'Month" and 'Year' levels.

I would like to get an output which lists the number of orders for the last 6 months and the total, like this:

Oct 2009   20
Nov 2009   30
Dec 2009   25
Jan 2009   15
Feb 2010   45
Mar 2010    5
Total     140

I can create the set with the members Oct 2009 until Mar 2010 and I manage to get this part of my desired output:

Oct 2009   20
Nov 2009   30
Dec 2009   25
Jan 2009   15
Feb 2010   45
Mar 2010    5

Just I fail to get the total line.

2
mdx

2 Answers

2
votes

You can achieve this by adding the ALL member to the set and then wrapping it all in the VisualTotals() function

SELECT
  ... on COLUMNS,
  VISUALTOTALS (
       {[Month].[Month].[Oct 2009]:[Month].[Month].[Mar 2010] 
       , [Month].[Month].[All] } 
  ) ON ROWS
FROM <cube>
1
votes

here is one possible solution for Adventure Works DW Demo Cube. The query selects the last 6 Order Counts and add a sum on the date dimension:

WITH MEMBER [Date].[Calendar].[Last 6 Mth Order Count] AS 
aggregate( 
ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(6)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])

)
SELECT  {[Measures].[Order Count]} ON COLUMNS
, {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(6)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])
,[Date].[Calendar].[Last 6 Mth Order Count]} 
ON ROWS
FROM [Adventure Works]