Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Feb 06, 2017 at 07:43 PM

    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...

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06, 2017 at 09:26 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 07, 2017 at 10:33 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 07, 2017 at 10:54 AM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 01:42 PM

    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

    Add comment
    10|10000 characters needed characters exceeded