Local member in EPM report to calc % of total

[Former Member]

Hi,

I need to create a report that looks like this.

The percentage must be calculated as the account per row divided by the total shown in the row at the bottom.

I tried to create the local member with excel cell references (i.e. B4/B11) but that breaks when I expand one of the accounts shown, i.e. the B11 reference stays and so the calculation uses the wrong account.

I then had a look on the EPM Add-in Academy and found a video showing a solution that uses an EPMRetrieveData function in the local member. I replicated that in my environment (BPC 10.1 NW, EPM Add-in SP 20 .NET4) and all I get is an error message saying the function cannot be used. I tried this in my original report and then also tried to replicate the report from the video 1:1 and got the same error in both cases.

Would anyone have a solution for this report?

Thanks,

Arnold

[Shrikant_Jadhav · Accepted solution]

Hi Arnold,

Kindly share the local member formula.

Or you can populate it in another cell and refer as in local member formula.

Shrikant

[Former Member · Accepted solution]

Hi Shrikant,

the formula is

=B6/EPMRetrieveData(;EPMContextMember(;"ACCOUNT");B$2;B$3)

note: I have greyed out the company name displayed in E3 on the screenshot only, in the report E3 is the EPMOlapMemberO formula.

Thanks,

Arnold

[former_member186338 · Accepted solution]

Never use ";" in local member formula - use US standard - ","

Vadim

[Former Member · Accepted solution]

Hi Vadim,

with that knowledge even my initial approach works. I thought I had to use the separator from Excel which in my case is ";" instead of ",".

Thanks,

Arnold

[former_member186338 · Accepted solution]

For local members you have to write formula using US standard in keywords and delimiters! It's a mistake done by EPM developers in past...

Vadim

P.S. But hidden top row is better

[Former Member · Accepted solution]

Hi,

why is a hidden top row better?? I always get confused as I work both on German and UK and US based systems, so the delimiter is something I get wrong on a regular basis.

Thanks,

Arnold

[former_member186338 · Accepted solution]

Hi Arnold,

"why is a hidden top row better?"

Because EPMRetrieveData will create additional data query...

Vadim

[Former Member · Accepted solution]

In VBA in Excel, the formula argument (parameter) separator is always a ",". I guess it is how BPC interprets the EPM functions (through VBA). Anyway, with this in mind, you should be less confused in selecting the separator.

[former_member186338 · Accepted solution]

"In VBA in Excel, the formula argument (parameter) separator is always a ","" - incorrect

I am using ";" argument separator in my local settings:

Executing VBA code:

Debug.Print Range("A1").Formula

Debug.Print Range("A1").FormulaLocal

Result:

=CONCATENATE("DDDD","KKK")

=CONCATENATE("DDDD";"KKK")

FormulaLocal will show formula in line with local settings!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Isn't BPC using formula behind the scenes?

Same as with entering a formula in the formatting sheet - should also be with "," and not with ";" or does that depend local settings? I was convinced that formatting sheet 'content' formula and local members do require "," when typing them in directly, regardless of the local settings, because the BPC 'programming code' uses them in a VBA-like modus with formula functions.

Is that incorrect? (now that we go on that road , I'm curious to see the finish line ;-))

[former_member186338 · Accepted solution]

For local member - yes, unfortunately you have to use US format.

What do you mean by formatting sheet 'content' formula? Local format has to be used on the formatting sheet!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I would grade point for that if I could :-), for now I can only 'like' it.

I never used the formatting sheet formula with different format settings.

thanks for clarifying this.

christophe

[former_member186338]

Hi Arnold,

What do you have in the Position column? Just members? Summe Passiva - is it a member or a local member?

Vadim

[Former Member]

Hi Vadim,

Summe Passiva is a member (node) of the account dimension.

BR,

Arnold

[former_member186338]

Then Christophe proposal  is the simplest!

Vadim

[Former Member]

🙂 thx Vadim

[Former Member]

Hi,

why can't you retrieve the 'Summe Passiva' also as the first row of you report and hide it. That way, you can Always refer to that row as it won't be replaced.

kind regards,

christophe

[Former Member]

Hi Christophe,

I was hoping for a solution where I would not have to hide rows. I will use it if there is no other way.

Thanks,

Arnold

[former_member186338]

Solution with EPMRetrieveData will work but the report will be slower then with hidden first line!

Vadim