Former Member

### Division by zero in members formulas (BPC HANA) - MDX Error

Hello all,

I am working on BPC 10.1 NW on Hana, and I have a problem with members formulas.

The problem is the division by zero, not by empty.

For example, if my formula is " 1/[NBJO] " ([NBJO] is an account in my dimension) the result in a BPC report is :

- 0 if [NBJO] is empty

- ERROR if [NBJO] is 0 (this is a mdx error in a Excel popup)

It is a problem because the account NBJO can be zero if the data is suppressed (before an optimize) or for a parent node for example.

We have contacted the SAP support and the respond is adding a function to test the NBJO value, because Hana doesn't manage the division by zero.

So, we have modified the member formula "1/NBJO" by "IIF([NBJO]=0,NULL,1/[NBJO])"

The result is ok, I have no error.

Unfortunaltely, I have others formulas with the following syntaxe :

IIF([NBJO]=0,NULL,IIF(SUM(DESCENDANTS([ENTITY].CURRENTMEMBER,20,LEAVES),1/[NBJO])=0,NULL,SUM(DESCENDANTS([ENTITY].CURRENTMEMBER,20,LEAVES),1/[NBJO])))

The objective is to have the sum of ratio and no the ratio of the sum (for a parent node)

But the tests don't work ....and we have the same error in Excel popup. The result of this member formula is good if NBJO is empty, not zero.

Have you a idea please ? Have you already see this problem ? because I have no more idea ...

Best regards,

10|10000 characters needed characters exceeded

• Jun 30, 2017 at 06:48 AM

But what is the business logic to have a sum of ratio?

10|10000 characters needed characters exceeded
• Former Member
Jun 30, 2017 at 09:04 AM

It's a headcount project, so we have indicators about number of days.

We have indicators with ratio of sum, but others are a sum of ratio... these indicators worked fine on BPC 10.0 (without Hana).

But after the migration to BPC 10.1 Hana, the division by zero doesn't work

Bests,