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)?
