Calculated Member - #VALUE! in the Grand Total

Hi all

This is a simple question on MDX.

We have created a calcualted member that basically compares an existing measure to an attribute, and displays a certain value based on what's in the attribute (simple case statement)

The calcualtion works fine against the proper dimension, but for Grand Total displays #VALUE!. What do I have to do to make the grand total actually display a grand total of this calculated member in the cube?

Thanks in advance

Moe

Hi Moe,

Can you give an idea of what the MDX for the calculated member looks like?

|||

Hi Deepak

Here's the code for the calculated member. Please let me know if you need anything else.

CREATE MEMBER CURRENTCUBE.[MEASURES].[calc - Spare Cost]

AS case when [Repairs].[Repair Status].&[Done] then [Measures].[ARC Buy Price]

when [Repairs].[Repair Status].&[Not Done] then 0

else -1 end,

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [ARC Buy Price] },

VISIBLE = 1;

|||

If the intent is to return the value of [Measures].[ARC Buy Price] associated with the [Repairs].[Repair Status].&[Done] member, maybe something simpler like this will work:

CREATE MEMBER CURRENTCUBE.[MEASURES].[calc - Spare Cost]

AS iif([Repairs].[Repair Status].CurrentMember is [Repairs].[Repair Status].&[Not Done],

0, ([Repairs].[Repair Status].&[Done], [Measures].[ARC Buy Price])),

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [ARC Buy Price] },

VISIBLE = 1;

|||

Hi Deepak

Thanks for the reply.

The code sample you sent me gives me a value in the Grand Total now, but I am confused as to what this Grand Total actually is .

To simplify my problem, I created the following example based on Northwind:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]

AS iif(

[Orders].[Customer ID].CurrentMember = [CHOPS], 5, 1),

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [Orders Count] },

VISIBLE = 1 ;

The above code gives me a Grand Total of 1! is this correct?

Thanks again

Moe

|||

Hi Moe,

I'm not clear how to run your sample code - could you provide an Adventure Works equivalent? Also, what should your results layout look like - and how many members of [Repairs].[Repair Status] are there (I assumed that there are just 2)?