Skip to Content
avatar image
Former Member

BPC NW 10 Member Formulas: Set to String & Hierarchy Navigation

Hi BPCers,

Thank you for looking at my question.

I have two questions about member formulas that might be able to help me master a requirement using member formulas. The overall requirement is to count the number of base level descendants of a selected member. E.g. I would select '2017' from my time dimension, and the member formula would return '12'.

The two sub questions are:

1. Is it possible to return string from a member formula? I find it strange that you can use a member formulas to analyse string, but not to write it, so I hope that I have missed something.

E.g. It is possible to check string with an IIF function-> IIF([CATEGORY].[PARENTH1].CURRENTMEMBER.NAME=[CATEGORY].[PARENTH1].[ACTUAL].NAME,<true>,<false>). But, if I simply asked the formula to return the name of the member for the selected dimension (category), then a 0 will be returned. So... [CATEGORY].[PARENTH1].CURRENTMEMBER.NAME would not return 'ACTUAL', instead it returns a '0'. I have attempted to use the various string functions in the MDX library, but to no avail.

2. Is it possible to use the COUNT function in a similar way to the SUM function? In some member formulas, I am using the SUM function to combine the transactional data on descendant members of my selected time period. e.g. SUM(DESCENDANTS([TIME].[PARENTH1].CURRENTMEMBER,[TIME].[PARENTH1].LEVELS(3),LEAVES),([account],[PRODUCT].[PARENTH1].[all_products])). This formula sums the months for my intended accounts. Instead, I would like to COUNT the number of tuples generated, but alas, this too does not work. Is there a function that would achieve this?

If anyone has any experience with the above requirement or one of the sub-questions, I would very much appreciate your explanation.

Kind Regards & Many thanks in advance,

Nick

Tags for google searches: MEMBER FORMULAS, DIMENSION FORMULAS, MDX LIBRARY, SAP BPC, MDX SYNTAX, FUNCTION, MEMBER FORMULA.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 02 at 05:42 PM

    Sample in ENVIRONMENTSHELL

    Counting number of base entities under parent - member formula for account PL250:

    COUNT(DESCENDANTS([ENTITY].CURRENTMEMBER,20,LEAVES),INCLUDEEMPTY)

    Result:

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 29 at 06:19 PM

    "1. Is it possible to return string from a member formula?" - No, not possible, member formulas always return numeric value!

    "2. Is it possible to use the COUNT function in a similar way to the SUM function?" - Yes, COUNT is a valid function!

    It's better to explain what do you want to achieve from the business point of view! Business logic?

    Add comment
    10|10000 characters needed characters exceeded