MDX-why the null result is missing?

Hi,friends, this is a newbie quetion about MDX.

I just write the MDX below:

select KPIValue("ADTD")on rows, {[Time].[TimeLevel].[Year].&[2007] } on columns from [TargetCube] where [Agent].[AgentMember].&[2]

And this return the result very well:

2007

ADTD 128

Then I change the MDX to this:

select KPIValue("ADTD")on rows, {[Time].[TimeLevel].[Year].&[2007],[Time].[TimeLevel].[Year].&[2008]} on columns from [TargetCube] where [Agent].[AgentMember].&[2]

And this return the same result:

2007

ADTD 128

It's very strange to me. Because there is no 2008's record in the Cube, I think the result ought to be :

2007 2008

ADTD 128 0

But why the result just makes the 2008's column Missing?

Thanks!

What is the MDX formula for KPIValue("ADTD"); and can you reproduce this problem with a KPI query in Adventure Works?|||

the MDX formula for KPIvalue("ADTD") is just refer to an Measure which Count the records of the facttable. I think this is not the answer. You could reproduce this problem with a KPI query in Adventrue Works.

And not only the KPI query, but every MDX query is like this. It looks like it's a common law of the MDX. You could have a simple test like this:

select ([Measures].) on rows, {[Time].[TimeLevel].[Year].&[2008]} on columns from [Cube] where [Agent].[AgentLevel].&[2]

Because there is no 2008's records in the facttable, and you will get the result like this:

A

not like this:

2008

A 0

but I really want to get the second result, because if there is no 2008's records, the right answer of the query ought to be 0.

Please have a test based on the Adventrue Works., thanks!

|||

"because if there is no 2008's records, the right answer of the query ought to be 0" - well, the "right" answer depends on the scenario, but maybe applying CoalesceEmpty() will help you, like:

With

Member [Measures].[OrdersNoNull] as

CoalesceEmpty([Measures].[Order Count],0)

select

{[Date].[Calendar].[Month].&[2004]&[7],

[Date].[Calendar].[Month].&[2004]&} on 0,

{[Measures].[Order Count],

[Measures].[OrdersNoNull]} on 1

from [Adventure Works]

-

July 2004 August 2004
Order Count 976 (null)
OrdersNoNull 976 0

|||

Puri , you don't catch my mean. Use your example, you get the answer:

July 2004 August 2004
Order Count 976 (null)

And this is also what I want. But I got the answer:

July 2004

Order Count 976

The Agust 2004 column is missing. That's my problem. I run the MDX , and return a result without August 2004 column. If the result is (null), that's OK, that's what exactly I want. My problem is not to change (null) to 0. My problem is just the column is missing.

Thanks.

|||What tool are using to run your MDX query? My results came from Management Studio - so are you saying that you don't get the same results, when you run the above query in Management Studio?|||

Yes, me in Management Studio too. And I also tried the ADOMD.net, the same result.

You could try to only select one Measure like me have done.

Thanks.

|||To be clear, could you post an actual Adventure Works query, with the results you get in Management Studio, so that others can try to reproduce your problem?|||

eh,you could test it at a very simple way.

Just in the MDX you select a Time not existed. For example, if there is from 2000 to 2008's data in the cube, and no 2050 's data in the cube. Then you JUST select the 2050's in the MDX query. Then you will get the result I said. the 2050's column is missing, not the (NULL).

|||

I think it sounds very reasonable though? You dont have any record of the year you specified in your MDX query, then of course the result retured with nothing on that year?

With best regards,

Yours sincerely,