Search code examples
ssasmdxmdx-query

Aggregate Facts bases on selected Dimension members

How to add a Total Column like in below image.

enter image description here

the Query Structure is as below 

WITH
    MEMBER [Measure].[Total Responses] AS [Measures].[Responses]

    MEMBER [Measure].[Option 1] as CoalesceEmpty([Measures].[Option1],0)
    MEMBER [Measure].[Option 2] as CoalesceEmpty([Measures].[Option2],0)
SELECT
{
    //Add Total Here for Below Dimension Members
    [Date].[Month].&[2018]&[2018-Q4]&[2018-09], //say Month 1
    [Date].[Month].&[2018]&[2018-Q4]&[2018-10], //say Month 2
    [Date].[Month].&[2019]&[2019-Q1]&[2019-01]  //say Month 5 
} ON COLUMNS,
NON EMPTY
{
    [Measure].[Total Responses],
    [Measure].[Option 1],
    [Measure].[Option 2]
} ON ROWS
FROM [Cube]
Solution

  • The Edits are marked with ** in below query

    WITH
  MEMBER [Measure].[Total Responses] AS [Measures].[Responses]

  **MEMBER [Date].[Month].[AllMessages] AS AGGREGATE({[Date].[Month].&[2018]&[2018-Q4]&[2018-12],[Date].[Month].&[2019]&[2019-Q1]&[2019-01]})

  MEMBER [Measure].[Option 1] as CoalesceEmpty([Measures].[Option1],0)
  MEMBER [Measure].[Option 2] as CoalesceEmpty([Measures].[Option2],0)

SELECT
{
  **[Date].[Month].[AllMessages],
  [Date].[Month].&[2018]&[2018-Q4]&[2018-12],
  [Date].[Month].&[2019]&[2019-Q1]&[2019-01]
} ON COLUMNS,
NON EMPTY
{
  [Measure].[Total Responses],
  [Measure].[Option 1],
  [Measure].[Option 2]
} ON ROWS
FROM
  [Cube]