Skip to Content
0

How to use STRTOMEMBER MDX in a member formula with SAP BPC NW

Feb 06, 2017 at 07:00 PM

235

avatar image

Hi experts,

I need to know how use STRTOMEMBER in a member formula with SAP BPC NW 10.

Actually I have following dimensions:

- OG_PC (Entity type) with INTCO Property

- PARTNER_PC (Interco type)

I need to create a member formula in PARTNER_PC which realized following calcul:

When I request a level in my OG_PC dimension, I need to see all my partner datas without my own contribution.

For exemple:

Site 001 (which is a parent in my OG_PC hierarchy) realize intercompany exchanges with:

Site 001(I_Site 001 in PARTNER_PC) = 10

Site 002 (I_Site 002 in PARTNER_PC) = 20

Site 003 (I_Site 003 in PARTNER_PC) = 30

So, Total intercompany exchanges = 60

But I need to see only part of my intercompany which is realized outside my step.

=> 60 - 10 = 50

I can't use OWNERSHIP model because I have > 250 sites in this case.

I think about create a member formula which realized the following calcul:

([PARTNER_PC].[TOT_PARTNER_PC])-((STRTOMEMBER([OG_PC].CurrentMember.Properties("INTCO"))))

With this calcul I could request my OG_PC dimension (on Site 001) and my PARTNER_PC dimension (on calculated member) to have the expected amount.

But when I refresh my report, I have the following error:

code: UJO_READ_EXCEPTION_000
severity: error
description: MDX statement error:"Invalid MDX command with <num_val> - <num_val>"
log id: 051MgMCN7kQxattyZ00YSW
DATAVALUE:Invalid MDX command with <num_val> - <num_val>

I know that STRTOMEMBER is supported in my SAP BPC NW version, I can find it in MDX library.

Is there something in my syntax which is wrong?

Thanks for help.

Alexandre

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Vadim Kalinin Feb 06, 2017 at 07:43 PM
0

Just to understand - can you show your OG_PC hierarchy?

How parent can have intercompany? INTCO is a valid property on a base level members...

Show 1 Share
10 |10000 characters needed characters left characters exceeded

P.S. Please show some report with OG_PC and PARTNER_PC in row axis and the result you want to have...

0
Vadim Kalinin Feb 06, 2017 at 09:26 PM
0

And a few comments about STRTOMEMBER in NW MDX:

Just test:

STRTOMEMBER("[PARTNER_PC].[I_Site001]") - this syntax will work!

STRTOMEMBER("I_Site001") - will NOT work, not full name!

STRTOMEMBER("[PARTNER_PC].["+"I_Site001"+"]") - will NOT work, "+" is not supported!

Show 1 Share
10 |10000 characters needed characters left characters exceeded
0
SAMUEL Alexandre Feb 07, 2017 at 10:33 AM
0

Hello,

thanks for your answer.

My SAP BPC NW is an Oracle version.

I have tried syntax like you show me and results are:

([PARTNER_PC].[TOT_PARTNER_PC])-([PARTNER_PC].[I_Site001]) => ok

(STRTOMEMBER("[PARTNER_PC].[I_Site001]")) => ok

([PARTNER_PC].[TOT_PARTNER_PC])-(STRTOMEMBER("[PARTNER_PC].[I_Site001]")) => ko

With this final syntax I have the following error:

code: UJO_READ_EXCEPTION_000
severity: error
description: MDX statement error:"Invalid MDX command with <num_val> + <num_val>"
log id: 051MgMCN7jQxeoM}bXNlz0
DATAVALUE:Invalid MDX command with <num_val> + <num_val>

It seems like it's not possible to realize calcul with STRTOMEMBER.

In Attachment you can find a report like I hope results:

In dimension OG_PC, base level for Site 001 have realized intercompany exchanges with:

- Inside Site 001 => I_Site 001 = 2

- With Site 002 => I_Site 002 = 10

- With Site 003 => I_Site 003 = 18

Finally, Site 001 has realized intercompany exchanges for 30.

In the last column, I need to see intercompany exchanges realized with all partners without I_Site 001.

Expected calculs are:

Total Partners - I_Site 001 (INTCO property of the member which is called in OG_PC dimension):

30 - 2 = 28.

How can I realize such calcul with member formula?

Thanks for help

strtomember.png


strtomember.png (43.8 kB)
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Feb 07, 2017 at 10:54 AM
0

Yes, looks like STRTOMEMBER has very limited support at least on your BPC SP level (not provided by the way). Just tested something like:

([PARTNER_PC].[TOT_PARTNER_PC])-(STRTOMEMBER("[PARTNER_PC].[I_Site001]")) - also not working.

But I do not understand why not to use standard intercompany elimination methods instead of reporting with dimension member formulas?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Just use very simple US Elimination rule to eliminate intercompany within each group!

http://www.sap.com/documents/2015/08/bae50353-557c-0010-82c7-eda71af511fa.html

0
SAMUEL Alexandre Feb 08, 2017 at 01:42 PM
0

Thanks for documentation,

I have just read it, it gives me an answer for a part of my case.

I'm looking for another solution.

Thanks

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Sorry, but you will not solve your issues using dimension member formulas.

0

"answer for a part of my case" ... what about another part? Can you explain?

0