BPC 11.1 Creation of CMFs with summation all the account members with a common property value

[former_member648038]

Dear BPC Experts,

I am looking for a solution on the following problem:

I have a member formula requirement that should sum all the account members with a common property value.

Suppose I have 100 account members in my account dimension and I have 1 property named as "CATAS_IND" (Catastrophic Indicator) in it.

Now this “CATAS_IND” property has a value “CATASLOSS” (Catastrophic Loss).

“CATASLOSS” value is attached to 30 members in the dimension.

Now, the member formula to be created is:

Non-Catastrophic Loss ([NONCATASLOSS]) = Total Loss ([TOTALLOSS]) – Catastrophic Loss (Summation of account members with property “CATAS_IND” = CATLOSS)

We have tried few syntaxes but are unsuccessful:

1. [NONCATASLOSS] = [TOTALLOSS] - [ACCOUNT].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATLOSS"
2. [NONCATASLOSS] = [TOTALLOSS] - SUM([ACCOUNT].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATLOSS")
3. [NONCATASLOSS] = [TOTALLOSS] - SUM(DESCENDANTS([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATAS_IND"),LEAVES)

I am on BPC 11.1 NW system on BW/4 HANA.

Can someone please help us crack this requirement using member formula and also please provide the right syntax to be used.

[former_member186338]

Just tested:

SUM(FILTER([ACCOUNT].MEMBERS,[ACCOUNT].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS"))

With ENABLE_HANA_MDX = X

Not working!

Looks like HANA MDX don't support FILTER at all.

Call SAP and open support case.

[former_member648038]

Hi Vadim,

I have tried to create the Member Formula using the same syntax which worked for you, but I am getting an error as below:

Error Log:

Internal error : no result set 2020-03-23 12:28:02
Some errors occured while processing the dimensions 2020-03-23 12:28:02
Process of the dimension ACCOUNT failed 2020-03-23 12:28:02
End validating member formulas 2020-03-23 12:28:02
Start validating member formulas 2020-03-23 12:27:54
End validating the members hierarchy 2020-03-23 12:27:54
Start validating the members hierarchy 2020-03-23 12:27:54
The members changes are allowed by BPC components 2020-03-23 12:27:54
Checking if the BPC components allow the members changes 2020-03-23 12:27:54
End of validating ID and value of members 2020-03-23 12:27:54
Start validating ID and value of members 2020-03-23 12:27:54
Start of the validation of members before process 2020-03-23 12:27:54
Start of processing the dimension ACCOUNT of environment 2020-03-23 12:27:54
Sorting the dimensions before the process 2020-03-23 12:27:54
Processing the a set of dimensions of the environment 2020-03-23 12:27:54

I really have no idea why its not working, can you please throw some light what could be the reason for this.

Thanks.

[former_member186338]

I think this is due to BPC11.1 and BW4HANA. On my 10.1 everything works fine.

Upgrade to the latest SP and test. If you will get an error - contact SAP support.

But I have told you from the very beginning - additional hierarchy is a better solution!

[former_member186338]

Working fine for me:

Test in the copy of ENVIRONMENTSHELL

TFORM - account with member formula.

I have created property CATAS_IND in ACCOUNT dimension and filled it.

Formula:

SUM(FILTER([ACCOUNT].MEMBERS,[ACCOUNT].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS"))

[former_member186338]

Test was done on BPC 10.1 on BW 753 SP03 on HANA db

[former_member186338]

You can try to use SUM(FILTER(...

But the correct approach is to create a new hierarchy and a parent for CATASLOSS

[former_member648038]

Hi Vadim,

We have used the following syntaxes for the problem mentioned in the below mail chain, but unsuccessful.

1. [NONCATASLOSS] = [TOTALLOSS] - [ACCOUNT].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS"
2. [NONCATASLOSS] = [TOTALLOSS] - SUM([ACCOUNT].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS")
3. [NONCATASLOSS] = [TOTALLOSS] - SUM(DESCENDANTS([ACCOUNT].[PARENTH1].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS"),LEAVES)
4. [NONCATASLOSS] = [TOTALLOSS] - SUM(FILTER(DESCENDANTS([ACCOUNT].[PARENTH1].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS"),LEAVES));
5. [NONCATASLOSS] = [TOTALLOSS] - SUM(DESCENDANTS([ACCOUNT].[PARENTH1].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS"),MEASURES.PEIODIC)
6. [NONCATASLOSS] = [TOTALLOSS] - SUM(FILTER(DESCENDANTS([ACCOUNT].[PARENTH1].CURRENTMEMBER.PROPERTIES("CATAS_IND")="CATASLOSS"),MEASURES.PEIODIC));
7. [NONCATASLOSS] = [TOTALLOSS] - SUM(FILTER([ACCOUNT].[PARENTH1].ALLMEMBERS,[ACCOUNT].[PARENTH1].PROPERTIES("CATAS_IND")=“CATASLOSS”))
8. [NONCATASLOSS] = [TOTALLOSS] - SUM(FILTER([ACCOUNT].[PARENTH1].ALLMEMBERS,[ACCOUNT].[PARENTH1].ALLMEMBERS.PROPERTIES("CATAS_IND")=“CATASLOSS”))
9. [NONCATASLOSS] = [TOTALLOSS] - SUM(FILTER([ACCOUNT].[PARENTH1].ALLMEMBERS,[ACCOUNT].[PARENTH1].CURRENTMEMBER.PROPERTIES("CATAS_IND")=“CATASLOSS”))

Any help in achieving the correct syntax to crack this requirement is much appreciated.

Thanks.