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

Calculated Measure Aggregation Seems to differ from MSAS 2K5 and 2000

I'm in the process of upgrading a cube in MSAS 2000 to MSAS 2005 (SP 1). In the existing MSAS 2000 cube, I have the following calculated measure defined:

SUM(Descendants([Term].[Term].CurrentMember, [Term Name]),
SUM
(
PeriodsToDate
([ActivityDate].[Std].[(All)]
,strToMember("[ActivityDate].[Std].&[" + Cstr([Term].[Term].CurrentMember.Properties("CensusDateValue")) +"]"
)), [Measures].[Activity Hours]))

This measure is supposed to sum up all activity up until the "CensusDateValue". it seems to be working fine and Aggregates up correctly even when the Term is a level other then [term name]

I came up with the following AS 2005 MDX that attempts to perform the same calculation(Some of the dimension names and properties have be modified):

SUM(Descendants([Term Dimension].CurrentMember, [Term Dimension].[TermName] ),
SUM(
PeriodsToDate(
[Activity Date Dimension].[Date].[(All)]
,STRTOMEMBER("[Activity Date Dimension].[Date].&[" + CSTR([Term Dimension].[Term Name].CurrentMember.Properties("Census Date SK")) + "]")), [Measures].[User Activity Hours]))

The calculation seems to work fine as long as I have the [Term Dimension].[Term.name] in the rows axis, otherwise it returns null. I suspect this is a difference between how member properties are calculated and aggregated between MSAS2K5 and MSAS200

Does anyone have any ideas or suggestions for troubleshooting?
It's hard to tell what's going on without looking at your cube design. Why don't you debug the problem by picking a row which caused the problem. Break your calculation into individual components and check to see if the component evaluates as expected at the given coordinate.

c#

Hello,

is it possible to get the values for my Report about existing c# functions or classes? And how it works?

Greez Daniel

Are you saying you want to use existing C# functions in your report? Yes, you can do that through custom assembly. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_rdl_3hsy.asp