This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!

# NONEMPTYMEMBER in ASO within CASE Statements

April 20 2012 at 4:35 PM
No score for this post

Hi All,

I am facing with Two problems right now when using MDX.

1. I have a very complex formula on my Scenario where i calculate my FX. But, my FX calculation has lot of exceptions on few accounts say 20-30 and each account has a different calculation and for all the remaining There is a generic calculation. I used the below way to calculate

CASE WHEN IS([Accounts].CurrentMember,[Account1]) THEN
([Actual] - ([Actual],[AccountF1]))*([Actual],[FX Rate])
WHEN IS([Accounts].CurrentMember,[Account2]) OR IS([Accounts].CurrentMember,[Account2]) THEN
([Actual] - ([Actual],[AccountG1]))*([Actual],[FX Rate])
and so on...
END
I am retrieving around 2000+ Accounts and it is taking around 3 1/2 Minutes.

I have seen most of the Presentations of not to use CurrentMember but I couldn't get any other way to write this.

2. Now, I thought to use NONEMPTYMEMBER in this to reduce my calculation time and i have written that as

CASE WHEN IS([Accounts].CurrentMember,[Account1]) THEN
NONEMPTYMEMBER [SC_Actual]
([Actual] - ([Actual],[AccountF1]))*([Actual],[FX Rate])
END

This is giving me an error. I am not sure if i can use NONEMPTYMEMBER within a case statement. I have also tried NONEMPTYMEMBER [Account1] and it still throwed me an error. Can i use NONEMPTYMEMBER within a CASE statement?

I tried simply as

NONEPMTYMEMBER [Account1]
([Actual] - ([Actual],[AccountF1]))*([Actual],[FX Rate])
and it didn't give me any errors.

If i cannot use the NONEMPTYMEMBER in CASE statement is there any other way that i can achieve the same?

 Scoring disabled. You must be logged in to score posts. Respond to this message
Responses

1. Re: NONEMPTYMEMBER in ASO within CASE Statements - Tim Faitsch on Apr 20, 8:36 PM
1. Re: NONEMPTYMEMBER in ASO within CASE Statements - Anonymous on Apr 21, 1:39 PM

2. Re: FX Calculations - DanP on Apr 24, 2:49 PM
1. Re: FX Calculations - Anonymous on Apr 24, 4:39 PM
1. Fx usually means ... - Danp on Apr 24, 6:44 PM
1. Fx usually means ... - Anonymous on Apr 25, 4:50 AM
1. YTD should not be formula - DanP on Apr 26, 1:02 PM