How can I create a custom calculation that will not error when adding NULL values?

[Former Member]

Hi,

Version: BI 4.0 SP8.3

Database: Microsoft SQL Server 2012 Analysis Services

Question: How can I create a custom calculation that will not produce the following error when adding a NULL value?

I am getting this error:

The MDAS service encountered an error.

Failed to get representation: RuntimeException occurred : com.sap.ip.bi.bics.exceptions.BicsProgrammErrorException: Invalid program status: Invalid new element, level to low: 1 but expected 2

I believe the error is occurring due to the Total Expenses custom calculation (see red arrow below):

Total Expenses is simply adding the values from the other custom calculations above it (see blue arrows above).

The formula is: 'FORMULA/[Activity]/Formula1' + 'FORMULA/[Activity]/Formula4' + 'FORMULA/[Activity]/Formula6'

This formula works great when all three calculations have values. The problem arises when one of them has a NULL value.

How can I create a custom calculation that will not fail when adding NULL values?

Thanks,

Michael

[I_MCA · Accepted solution]

Hi Michael

I'm curious what those custom calcs might be that results in them coming back with a NULL value?

A wild shot, but for each of those custom calcs what if you edited each and added "+0" to the end thus hopefully forcing them to have a number?

Regards

[Former Member · Accepted solution]

Thanks for the response, Ian.

I tried adding "+ 0", to one of those custom calculations, but I received the error message again:

Is there any logic or function that you can include in the custom calculation formula to convert a NULL into a 0 ?

Thanks,

Michael

[I_MCA · Accepted solution]

I've replicated your example using Microsoft AdventureWorks cube. I created a custom calc that adds together Minnesota + Wisconsin + Oregon. All have NULL values and the calculation works correctly without error. However I was using BI4.1 SP05.

Suggest you contact support as they will need to capture logs for this issue. However 4.0 SP08 is no longer supported for fixes. If you can I suggest you install either the latest 4.0 SP10 Patch 6 or 4.1 SP11 Patch 1. If the issue still exists then at least these are product versions where fixes can be released.

Aside: Unfortunately for SSAS there are no functions to convert NULL values to 0, but when using BW as a datasource we do have related functions like NOERR() and NDIV0().

Regards