Skip to Content
avatar image
Former Member

Dynamic referencing for Local Member Formula in BPC 10 Reporting (NW)

Hi,

Is it possible for a local member to dynamically reference correct columns, for example if a column was inserted by the user in between the preconfigured Local Member formulas?

Background

Users will be manipulating reports substantially, which may include inserting columns and typing their own formulas or data in

Image 1 is the example of the predelivered report; comparing The Period April 2012 Actuals (Col J), vs Period April 2012 Budget (Col K), with a local member for the variance in Col L.

This can be seen in 'Predelivered Local Member Use.jpg'.

Requirement

If a user adds a column between the data sets that are being compared in the Local Member formula, i.e. between J and K, I would like the Variance Local Member formula to use the new columns, rather than being fixed at the EPMPOSITION(n). Hopefully if you are following what I mean, you will recognise that hard-coding to EPMPOSITION(1) and EPMPOSITION(2) will now be redundant, as a column has been inserted between 1 and 2.

The new local member formula should read EPMPOSITION1 and 3.

to update accordingly. RAther than referencing Columns J and K still, it would actually refernece J and L, with the varaince sat in Column M.

When you insert the column, excel handles the column shifts correctly, but after the refresh, it references the incorrect columns:

This can be seen in 'After New Column Insert and Refresh.jpg'

What I have tried so far

I have tested both with and without the 'Use excel cell references' option ticked, changing the Formula to use Excel formula (as pictured).

I tested using 'Use Position Axis' tick box on/off in Sheet Options, both do not work as I hoped

Thanks for any input you can give. I'm nervous this may be a limitation of LocalMembers having static formulas and not updating like normal Excel functionality would.

Other options I have are not using a local member, and potentially using the EPMCopyRange function to create the variance column without local members - any advice on this would be great!

Thanks a lot,

Nick

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Sep 13, 2012 at 04:14 PM

    I would agree with Nick here. Using Cell references of derived values into the Column Axis (in the same report) seems faster than creating multiple reports with shared Row Axis. I think the actual impact can be seen, if one is using Formatting, as the Formatting sheet probably has to analyze each and every sheet individually.

    So as of now, facing the same issue which Nick is facing as far as Local Member is concerned.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 05, 2012 at 11:37 AM

    Hi Nick, this works fine for me. Attached is one of the example I just tried. I just had to made local member recognition ON and create directly the local member in excel sheet. Local will automatically be created in the background with a kind of formual in screen shot. Let me know if this works for you. Thanks.


    Eg Local member.jpg (148.0 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Nick, I would need to see the whole construction to understand what best we can do to overcome this reference issue. Not sure but face of it that should have work perfectly.

      Also, EPM 10 is quite stong from the performance perspective. I have report with 6 reports in a worksheet and it is expending reasonably good.

      Thanks, Rohit

  • avatar image
    Former Member
    Oct 04, 2012 at 09:30 AM

    Hi Nick,

    Do you already solve about this local member issue ? Because i'm facing the same thing.

    Thanks,

    Suprapto

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Suprapto,

      We have used the Position in the column axis for reports. If users add a column inbetween data columns it would give variances on the new column. At the moment there is seemingly no way to change that.

      Nick

  • avatar image
    Former Member
    Aug 12, 2013 at 01:33 PM

    I have a problem and would like you to help me. I would like to put a formula epm within a local member, only an error occurs. The formula is: =EPMSaveComment(K19;;EPMMemberID(H16);EPMMemberID(H17);EPMMemberID(F19);EPMMemberID(E19);EPMMemberID(D19);EPMMemberID(E12))

    and the error it gives is: # Error - Unable to apply formula local member.

    Does anyone know how to solve?

    Obs: The formula works when writing in cell.

    Add comment
    10|10000 characters needed characters exceeded