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?

In your query [Customer].[c Hier Ty3 Bg 1 3].&[Australia], is not a
column, it is a member on the row axis.

Trying to read between the lines, I am guessing that what you are saying
is that the user has not specified a filter for the [Customer].[c Hier
Ty3 Bg 1 3] hierarchy. If this is the case then what you would want to
do is to return the default member (which is usually the 'all' member).
You can do this using the DefaultMember function :

[Customer].[c Hier Ty3 Bg 1 3].DefaultMember

eg.

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].DefaultMember}
* 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


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1146093317.737607.121150 (AT) g10g2000cwb (DOT) googlegroups.com>,
Ratnerg (AT) gmail (DOT) com says...

Darren

Thank You! That will work... continued question...

That could give me the functionality I desire except that unlike
placing a "NULL" in a sql statement, this requires that I choose some
dimension to pull the "ALL"/Default member from. In my particular
application any combination of four dimensions in the cube could be
pulled back and placed on axis 1. Assuming a user only chooses three
dimension, I would need to choose a dimension from my cube that was not
one of the chosen three to pull the ALL member from. While not a huge
amount of code, it does require that I intelligently choose a dimension
so as not to get an error that the dimension has already been chosen on
axis 1. Is there a way to get the same functionality without calling a
specific dimension?

Thanks for your help!
Garrett

Hi Garrett,

No, I can't think of a 'generic' way of doing this with straight MDX.
From the structure of your example MDX, it looks like you might if using
AS 2005. If a .Net stored procedure might be one way to come up with a
re-useable solution.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1146458126.258556.304770 (AT) j33g2000cwa (DOT) googlegroups.com>,
Ratnerg (AT) gmail (DOT) com says...