MDX: Filter a member that doesn't exist?

Question

I'm new to MDX, so I assume this is a newbie question.

Existing dimension: status Existing Measure: count

Existing Query:

SELECT 
  NON EMPTY [status].CHILDREN ON 0,
  NON EMPTY Measures.count ON 1
FROM [objects]

I want count the records with status='C'. But sometimes, there're no records that match that criteria. So if I do:

SELECT 
  NON EMPTY [status].[C] ON 0,
  NON EMPTY Measures.count ON 1
FROM [objects]

I get this error:

*MDX object '[status].[C]' not found in cube 'objects'*

I would like a query that returns 0 in that case. Which is the right way to do that query?

Thanks!

Answer 1

The best idea, which is what most client applications do, is to query the metadata to find out what members exist in a dimension and then only issue queries for members that exist.

The following article lists the various ways of getting at dimension metadata link text

the other "hack" might be to do the following:

SELECT
  NON EMPTY StrToSet("[status].[C]") ON 0,
  NON EMPTY Measures.count ON 1
FROM [objects]

But that will still not give you a 0, instead it will give you cell set with no columns. And it's not really an approach that I would recommend.

Answer 2

I assume from your question that you don't actually have a Status dimension table in your datasource. Instead, you have a dimension defined that is using a column in the Fact table. This works great, but it doesn't allow you to define a fixed set of members for that dimension...you're basically stuck with the facts that you have.

The solution, the way I see it, would be to create a separate table called DimStatus, and pre-populate it with all of the valid statuses that might be used by your facts, and have the fact table reference the Status dimension table.

Then things will work the way you want them to. You can automatically hide unused members, or you can include all of them, or a set of just the ones you want.

One caveat is that it will show (null) instead of 0 if there are no matching facts. You can get around this with a simple IIF(ISEMPTY()):

WITH MEMBER
    [ZeroCount] AS
        IIF(ISEMPTY([Measures].[Count]), 0, [Measures].[Count])
SELECT [ZeroCount] ON COLUMNS,
[Status].Members on ROWS
FROM [MyCube]

This will show all statuses and either the count, or a zero. Unfortunately it requires changes to your data source and cube, so hopefully that's an option for you.

I played with some queries and I don't think it's possible to do this more easily. You can't create a set with invalid members--that would create invalid tuples, and nothing would work properly. I also tried the "hack," and didn't get it to work as expected.