on 02-06-2017 7:00 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.