Monday, March 19, 2012

MDX: Null value to replace column in Query

I have an MDX query that is not doing what I want it to do.

Currently, I have an application that expects to recieve a certain
number of columns in order to make a chart.In SQL, if I was not
requesting data for all of the columns I could replace the column name
with a NULL and still recieve the other information with the in the
appropriate format (Except for that column would have all NULL values).
I cannot get this to work in MDX... an example in SQL which does work
follows:

i.e.
Requesting real information
SELECT id as col1, dog as col2 FROM SQL

Replace column with null but recieve same table format
SELECT NULL as col1, dog as col2 FROM SQL

I cannot figure out how to do this when requesting data with MDX in
Analysis Services. I have looked all through "MDX Solutions" and cannot
find the solution :) Although there was tons of great stuff in there.
TONS!

My MDX query that requests real data for all columns and works fine is
as follows:
SELECT {
[Measures].[YN] ,
[Measures].[YD] ,
[Measures].[YV] ,
} ON 0,

NONEMPTY(
{[Product].[p Hier Ty3 Bg 1 1].&[R104],[Product].[p Hier Ty3 Bg 1
1].&[R706]}
*{[Customer].[c Hier Ty3 Bg 1 3].&[Australia]}
*EXCEPT([Date 1].[c_month].[2004_M01]:[Date 1].[c_month].[2004_M03],
[Date 1].[c_month].[ALL])
*EXCEPT([Customer].[c Hier Ty3 Bg 2 2].Members,[Customer].[c Hier Ty3
Bg 2 2].[All])

) ON 1
FROM Mimir04

But if I want an empty column to replace {[Customer].[c Hier Ty3 Bg 1
3].&[Australia]}, my guess at a solution (coming from SQL and being a
newbie at MDX) was to put a null set in place of this dimension call...
{NULL}. Unfortunately, this means that nothing gets returned

What exactly is the solution to returning an empty column?

Could you please clarify whether you'd like empty rows or empty columns?

Or, best, show an example of how you expect the query result to look like (axes contents and cell data)?

Thank you

No comments:

Post a Comment