MDX: Joining months with total

Question

I want to create table like this:

      [January] [February] ...other months... [Total for Year]

item1

item2

item3

It's easy to create 2 different queries, for months and total, like this:

SELECT
[Time].[Month].[Month] ON COLUMNS,
TOPCOUNT([Items], 5, [Count]) ON ROWS
FROM [Cube]
WHERE([Time].[Year].[Year].&[2015-01-01T00:00:00])

and

WITH
MEMBER [Total] AS SUM([Count], [Time].[Year].[Year].&[2015-01-01T00:00:00])
SELECT
[Total] ON COLUMNS,
TOPCOUNT([Items], 5, [Count]) ON ROWS
FROM [Cube]

but how to concatenate them or write single one?

Answer 1

You could expand the WITH statement like this:

WITH 
  MEMBER [Time].[Month].[All].[Total] AS --<<THIS IS HOSTED IN SAME HIERARCHY AS THE SET THAT FOLLOWS
    Sum
    (
      [Count]
     ,[Time].[Year].[Year].&[2015-01-01T00:00:00]
    ) 
  SET [mths] AS 
    Exists
    (
      [Time].[Month].[Month].MEMBERS          
     ,[Time].[Year].[Year].&[2015-01-01T00:00:00]
    ) 
  SET [concatenatet_set] AS 
    {
      --<<THE FOLLOWING CAN BE BROUGHT TOGETHER  AS THEY HAVE THE SAME "DIMENSIONALITY" I.E. FROM THE SAME HIERARCHY
      [mths]
     ,[Time].[Month].[All].[Total]
    } 
SELECT 
  [concatenatet_set] ON COLUMNS
 ,TopCount
  (
    [Items]
   ,5
   ,[Count]
  ) ON ROWS
FROM [Cube];

Here is the script I have used to test the above idea against AdvWrks:

WITH 
  MEMBER [Date].[Calendar].[All].[Total] AS 
    Sum
    (
      [Measures].[Internet Sales Amount]
     ,(
        [Date].[Calendar].[All Periods]
       ,[Date].[Calendar Year].&[2007]
       ,[Date].[Calendar Quarter of Year].&[CY Q1]
      )
    ) 
  SET [mths] AS 
    Exists
    (
      [Date].[Calendar].[Month]
     ,(
        [Date].[Calendar Year].&[2007]
       ,[Date].[Calendar Quarter of Year].&[CY Q1]
      )
    ) 
  SET [concatenatet_set] AS 
    {
      [mths]
     ,[Date].[Calendar].[All].[Total]
    } 
SELECT 
  [concatenatet_set] ON COLUMNS
 ,TopCount
  (
    NonEmpty
    (
      [Product].[Subcategory].[Subcategory]
     ,(
        [Date].[Calendar Year].&[2007]
       ,[Date].[Calendar Quarter of Year].&[CY Q1]
      )
    )
   ,5
   ,[Measures].[Internet Sales Amount]
  ) ON ROWS
FROM [Adventure Works]
WHERE 
  [Measures].[Internet Sales Amount];

It results in the following which seems reasonable: