MDX: Aggregates over a set

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.

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>

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]