SAP BPC Member Formulas issue

[Former Member]

Hello,

I have implemented a MDX-formula which looks like [PERCENTAGE]*[AMOUNT];SOLVE_ORDER=2

Now it works like that:
1. Sum all percentages from all companies

2. Sum all amounts from all companies

3. Multiply sums

How I want it to work:
1. Multiply percentage and amount for each company

2. Sum multiplied data from step 1 from all companies

Is there any way to do it?

[former_member186338 · Accepted solution]

Hi Oleksandr!

No, the dimension formula will never work like you want

You have to create a script to store the multiplication result for each base company (may be in default.lgf):

*WHEN ACCOUNT

*IS AMOUNT

*REC(EXPRESSION=%VALUE%*[ACCOUNT].[PERCENTAGE],ACCOUNT=AMOUNTPERC)

*ENDWHEN

*WHEN ACCOUNT

*IS PERCENTAGE

*REC(EXPRESSION=%VALUE%*[ACCOUNT].[AMOUNT],ACCOUNT=AMOUNTPERC)

*ENDWHEN

And you can create special member to calculate percent:

PERCENTAGECALC

IIF([AMOUNT]=0,NULL,[AMOUNTPERC]/[AMOUNT])

This formula will show effective percent on parent company...

Vadim

[Former Member · Accepted solution]

"No, the dimension formula will never work like you want" - that's a shame

Won't additional lines in default.lgf load the system unnecessarily?  I think I will proceed with creating additional report and reproducing the formula in excel, since we will use it only in one report.

[former_member186338 · Accepted solution]

"that's a shame" - ??? Why?

The dimension member formulas are calculated when requested by report for each member used in the report. It's by design! And dimension member formulas are used to calculate ratios, it's the only way to get correct figures on parents.

Please read my article:

And simple calculations in default.lgf will not seriously affect saving performance (we use calculations like this for years...)

Vadim