Include Empty Values in SSAS MDX query to use in SSRS

Question

I have a cube derived from three dimension tables and a fact table

fact_event (date, location, type) dim_location, dim_type and dim_time

I want to use reporting services to create a report with the following layout

SELECT 
    [Location].Members ON COLUMNS,
    [Type].Members ON ROWS,
    [Time].[Hierarchy].[Month] ON PAGES
  FROM [Events]
  WHERE
    [Measures].[Event Count]

Reporting Services obviously likes the query flattened and the measure in the column such as

SELECT
    [Measures].[Event Count] ON COLUMNS
    {
      [Location].Members *
      [Type].Members *
      [Time].[Hierarchy].[Month]
    } ON ROWS
  FROM [Events]

But when it is performed like this rows and columns that do not contain a value are removed. So for January I might have "New York", "London", "Sydney" but for February I only have "New York", "Sydney"

I would like to have an MDX query that would return a 0 for London for that month.

The same goes for the event type as some event types mightn't occur for a particular month but I would like a row of zeros to appear for that month.

I can get the required data by reverting back to sql but it would be a lot more preferable to use the cube. The SQL query that gets me everything I want is

SELECT d.[Month], t.[Type], l.[Location], COALESCE(es.EVENT_COUNT, 0) AS EVENT_COUNT
  FROM 
  (
    SELECT DISTINCT [Month]
      FROM @Dim_Time 
  ) d
  CROSS JOIN @Dim_Type t
  CROSS JOIN @Dim_Location l
  LEFT JOIN
  (
    SELECT [Month], [Type], [Location], 
        COUNT(*) AS EVENT_COUNT
      FROM @Fact_Event
      GROUP BY [Month], [Type], [Location]
  ) es ON (d.[Month] = es.[Month] AND t.[Type] = es.[Type] AND 
           l.[Location] = es.[Location])

I've included a script to populate some temp tables with appropriate data to demonstrate the above query

declare @Dim_Time table ([Month] datetime)
declare @Dim_Location table ([Location] varchar(10))
declare @Dim_Type table ([Type] varchar(10))
declare @Fact_Event table ([Month] datetime, [Location] varchar(10), [Type] varchar(10))

insert into @Dim_Time values ('1 Jan 2011')
insert into @Dim_Time values ('1 Feb 2011')
insert into @Dim_Location values ('New York')
insert into @Dim_Location values ('Sydney')
insert into @Dim_Location values ('London')
insert into @Dim_Type values ('Good')
insert into @Dim_Type values ('Bad')

insert into @Fact_Event values ('1 Jan 2011', 'New York', 'Good')
insert into @Fact_Event values ('1 Jan 2011', 'New York', 'Good')
insert into @Fact_Event values ('1 Jan 2011', 'New York', 'Bad')
insert into @Fact_Event values ('1 Jan 2011', 'Sydney', 'Good')
insert into @Fact_Event values ('1 Jan 2011', 'Sydney', 'Good')
insert into @Fact_Event values ('1 Jan 2011', 'Sydney', 'Good')
insert into @Fact_Event values ('1 Jan 2011', 'Sydney', 'Bad')
insert into @Fact_Event values ('1 Jan 2011', 'London', 'Good')
insert into @Fact_Event values ('1 Jan 2011', 'London', 'Bad')
insert into @Fact_Event values ('1 Jan 2011', 'London', 'Bad')
insert into @Fact_Event values ('1 Feb 2011', 'New York', 'Bad')
insert into @Fact_Event values ('1 Feb 2011', 'New York', 'Bad')
insert into @Fact_Event values ('1 Feb 2011', 'Sydney', 'Good')
insert into @Fact_Event values ('1 Feb 2011', 'Sydney', 'Good')
insert into @Fact_Event values ('1 Feb 2011', 'Sydney', 'Good')
insert into @Fact_Event values ('1 Feb 2011', 'Sydney', 'Bad')
insert into @Fact_Event values ('1 Feb 2011', 'Sydney', 'Good')

Any assistance is appreciated.

Meff Meff · Accepted Answer · 2011-05-10T08:43:54

Use the "Toggle non-empty" button in the SSRS query designer.

http://64.4.11.252/en-us/library/ms403829(SQL.110).aspx

Looks like a dataset with a green funnel underneath it.

Toggle between showing and not showing empty cells in the Data pane. (This is the equivalent to using the NON EMPTY clause in MDX).

Include Empty Values in SSAS MDX query to use in SSRS

1 Answers