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

[Former Member]

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.

[former_member186338 · Accepted solution]

Sample in ENVIRONMENTSHELL

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

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

Result:

[Former Member · Accepted solution]

Hi Vadim,

Many thanks, it works perfectly!

Finally, and sorry for not including this addition from the outset, might you know if it is possible to include the equivalent of a 'WHERE' statement in the above formula?

E.g. Included a property filter in the above formula, so that only entities with ENTITY_TYPE = PC are counted? Or even, more than one property, so that ENTITY_TYPE = PC and CURRENCY = GBP are counted only.

I have tried a few variations, both inside and outside the descendants statement but have had no luck (See the attached).

Many thanks again for your help,

Nick

[Former Member · Accepted solution]

Hi Vadim,

I've sorted this issue now. Thank you so much for the help!

The working formula for the above is:

COUNT(FILTER(DESCENDANTS([ENT_MGMT].CURRENTMEMBER,20,LEAVES),[ENT_MGMT].CURRENTMEMBER.PROPERTIES("ENTITY_TYPE")="PC"),INCLUDEEMPTY).

Best Wishes,

Nick

[Former Member · Accepted solution]

And multiple property filters =

COUNT(FILTER(DESCENDANTS([ENT_MGMT].CURRENTMEMBER,20,LEAVES),[ENT_MGMT].CURRENTMEMBER.PROPERTIES("ENT_TYPE")="PC" AND [ENT_MGMT].CURRENTMEMBER.PROPERTIES("STYLE")<>"PLAN"),INCLUDEEMPTY)

[former_member186338 · Accepted solution]

Sorry, but you have to accept my answer and close the question!

[former_member186338]

"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?

[Former Member]

Hi Vadim,

Thank you for your response!

1. Ok, good to know.

2. From a process perspective, we are using a member of the Account dimension to act as a status flag that is written to during a custom package. This flag is called 'MEMO_FLAG' and it indicates that a certain criteria has been met. Obviously, this is transactional data so BPC aggregates it as I move up the hierarchy. I want to use the Member formula to count the number of base level descendants belonging to the selected entity e.g. If I chose 'France' then there might be 10 base level entities, so this member formula would return 10. Originally (had the answer to part 1 been, "yes it's possible"), I was going to get the member formula to return "6 out of 10", which would say that 6 out of the 10 base entities had 'MEMO_FLAG' set to 1.

I appreciate that another solution would be to maintain another dummy account and write '1' as transactional data on every base level entity in, but I'd prefer not to for various reasons.

Might you know if such a counter is achievable use Member Formula syntax?

Many thanks,

Nick

[former_member186338]

I will try to answer if you will not disappear for 3 days 🙂

[Former Member]

Apologies, I was expecting an email notification!