Include Empty Values in SSAS MDX query to use in SSRS

Question

I have a cube derived from one fact table that has a key and three other attributes

fact_event (eventid, ageband, location, type)

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

SELECT 
    [Event].[Location].Members ON COLUMNS,
    [Event].[Type].Members ON ROWS,
    [Event].[AgeBand].Members 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
    {
      [Event].[Location].Members *
      [Event].[Type].Members *
      [Event].[AgeBand].Members
    } ON ROWS
  FROM [Events]

But when it is performed like this rows and columns that do not contain a value are removed. So for the age band "[0-30]" I might have "New York", "London", "Sydney" but for "[31-60]" 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.

Clint Good Clint Good · Accepted Answer · 2011-05-11T12:18:51

Create dimensions based on the attributes you want to slice by. Once this is done ensure that you have "Show Empty Cells" checked in the query designer (or don't use NON EMPTY in MDX query). The query should end up like

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

Include Empty Values in SSAS MDX query to use in SSRS

1 Answers