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

[Former Member]

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

[Former Member]

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.

[Former Member]

Hi Nick,

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

Thanks,

Suprapto

[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

[Former Member]

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.

[Former Member]

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.

[Former Member]

Hi Rohit,

Thanks for taking the time to do this and include the helpful screenshots;

Can you confirm that the cell references are still using the same cells after you have refreshed the report? Is it the same for new columns also?

I'm just working through your config to ensure it aligns with what I have done to test..

Thanks,

Nick

[Former Member]

Hi Rohit, another couple of questions if I may;

Would you be able to test with the configuration in columns, with nested dimension members? It would mean adding a dimension such as Category/Version on top of your 'Time' dimension in the Cols.

I think this will complicate how you have th configuration of 'Attach to member', as it would be a combination of members firstly, and secondly that combination would change, depending on the user's Context selection. i.e. Although it is BUDGET_2013 now, it will be BUDGET_2014 next year etc., the same can be said for the Time member.

Below is a screenshot explaining how the report is built - it is a little more complex than your example due to the custom nature of the column dimension member combinations. They also need to be very flexible to align with the requirements I have.

Any more input or help you can give would be really appreciated,

Many Thanks,

Nick

[Former Member]

Yes, cell reference remains in the same cell after adding any number of row or a column. Lemme try your next scenario.

Thanks, Rohit

[Former Member]

Here you go my friend and yes this still works.

You are welcome

Thanks Rohit

[Former Member]

Hi Rohit,

Thanks again for taking the time to test, and your screenshot examples are very helpful.

I think that the reason for the functionality not working on my report, is that it is not created completely from the 'Report Wizard' or using the standard 'New Report' functionality.

If you have a minute, have a look at the 'Report Layout Explained.jpg' file so you can see what I mean. It is essentially hacking apart the EPMOlapMember formula to manually insert the member values for which the column should display data.

It would be possible for me to achieve the same layout required above, but I would need to have mabye 5 reports sharing the Row Axis members, rather than currently 1 report. I found the performance implication to be quite significant when you have 5 reports on one workbook, even if they are only 2 or 3 columns each.

Do you have any opinion on my approach to the report build and whether there is another way?

Thanks for your time,

Nick

[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