hi,

I have met a problem in the following mdx problem, when I write:

select {IIF(ISEMPTY([Measures].[mymeasure]),0,1} on columns,
{[date].all} on rows from [my_cube];

It does not work coz the following error:
The function expects a tuple set expression for the 1 argument. A string
or numeric expression was used.

But if I write it as a calculated member such as:

with member [Measures].[mycalmeasure]
as IIF(isempty([Measures].[mymeasure]),0,1)
select [Measures].[mycalmeasure] on columns, {[date].all} on rows
from [cube_orders];

This calculated member works fine. I think these two statements are the
same but the results are totally different. Would you please tell me the
reason of it? Thanks a lot!

The error message helps explain the issue - MDX select query axes should
have an MDX set expression. In the 1st query, the "columns" expression
is numeric, whereas in the 2nd query it is a dimension member, which can
be cast to a set.

http://msdn2.microsoft.com/en-us/library/ms146002.aspx
...
<SELECT query axis clause> ::=
[ NON EMPTY ] Set_Expression [ <SELECT dimension property list
clause> ] ON

Set_Expression
A valid MDX set expression.
...
http://msdn2.microsoft.com/en-us/library/ms145491.aspx

A set consists of zero or more tuples.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thank you Deepak!

But now I got a more complex problem.

When I modify my MDX query a little as:

select {IIF(ISEMPTY([Measures].[mymeasure]),0,[Measures].[mymeasure]} on
columns, {[date].all} on rows from [my_cube];

There is no errors but the results [mymeasure] still contain nulls!
And when I use calculated member as:

with member [Measures].[mycalmeasure]
as IIF(isempty([Measures].[mymeasure]),0,[Measures].[mymeasure])
select [Measures].[mycalmeasure] on columns, {[date].all} on rows
from [cube_orders];

The nulls in the results [mymeasure] disappeared. Why only calculated member
can remove the nulls? Would you please tell me the reason of it? Thanks a
lot!

Not sure what your next level of complexity will be, but I can't
reproduce the nulls at this level! So, could you cite a sample Adventure
Works query?

0,[Measures].[Order Quantity])} on columns,
{[Date].[Calendar].[All Periods]} on rows
from [Adventure Works]
-------------------------------------------------
Order Quantity
All Periods 274,776

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi, Deepak

Thanks and yes I can. Refer to the Analysis Services Tutorial 3 sample
database, see the following statement:

select {IIF(ISEMPTY([Measures].[Internet Sales-Order
Quantity]),0,[Measures].[Internet Sales-Internet Sales Count])} on columns,
{[Order Date].[Date].[Date]} on rows
from [Analysis Services Tutorial]

And go to record 47, the result is still null !!! It seems the ISEMPTY
does not work at all !!!

But if you use WITH MEMBER ... Then the null will be converted to 0.
Would you please tell me why that happens? Thanks again.

"Deepak Puri" wrote:

Here I want to add a little details to my last post

when you use

select {IIF(ISEMPTY([Measures].[Internet Sales-Order
Quantity]),0,[Measures].[Internet Sales-Internet Sales Quantity])} on columns,
{[Order Date].[Date].[Date]} on rows
from [Analysis Services Tutorial]

The result is like:
Internet Sales-Order Quantity
1 xxx
2 xxx
.........
47 null
.........

when you use

with member [Measures].[CalMeasures]
as IIF(ISEMPTY([Measures].[Internet Sales-Order
Quantity]),0,[Measures].[Internet Sales-Order Quantity])
select [Measures].[CalMeasures] on columns,
{[Order Date].[Date].[Date]} on rows
from [Analysis Services Tutorial];

The result is like:
CalMeasure
1 xxx
2 xxx
....
47 0
....

Actually, the ISEMPTY() does work - but not the in way you may want. The
expresssion:

{IIF(ISEMPTY([Measures].[Internet Sales-Order
Quantity]),0,[Measures].[Internet Sales-Internet Sales Count])}

is computed once, as a query axis set expression, and the resultant set
becomes the results column axis. In that context, if ISEMPTY() returns
true, then there will be a query error, because 0 is not a set
expression.

On the other hand, the calculated member is re-computed in the context
of each query results cell.

If you need more details, you can consult a book like "MDX Solutions":

http://www.wiley.com/WileyCDA/WileyT...471748080.html

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

hi, Deepak

Thank you so much. If the expression is computed once in the select
statement, then what cell value of [Measures].[Internet Sales-Order Quantity]
does it use to compute the ISEMPTY() result? Does it use the first one or a
random one? Thanks.



"Deepak Puri" wrote:

By the way, I had a brief review of chapter 4 in the book you mentioned and
did not find the details about my problem.

Would you please give me the related page numbers so I can refer to? Chapter
4 is a very long chapter including context, solving orders and etc. Thanks

You can refer to the discussion of "The Execution Stages of a Query",
in particular to the section on "Cell Context When Resolving Axes" (Page
104).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***