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