BPC Input Schedule Limitations

[Former Member]

We experienced some limitations in BPC input schedule template when using Excel-based EPM front-end. Due to data volume, we do not want to show rows for all combinations of dimension members (including empty rows) for the context selected in our input schedule. We hide all empty rows in the input template and let user input new rows at the bottom. To help user input new records, we turned on member recognition in the input template. However, we found following issues in the input template:

1. When copy and paste data from another Excel spreadsheet, the input template will automatically correct any invalid dimension member values using the field value in the above row without any indicator for user to see what raw data are changed.

2. When a new line is entered, the full input template is always automatically refreshed. Our planners have to either save one line at a time or only input all dimension members first, then enter key figures. Otherwise, all key figures in the new rows will not be saved.

We are currently using standard BPC 10(SP11) without any customizations. I am wondering how other BPC users overcome this system limitations. What options do we have if we do not want to use web-based input template and not showing all combinations of dimension members? Can VBA, BADI or other configuration/customizations fix these issues?

thanks,

[former_member186338]

Hi Xin,

1. Yes it's a real issue and can be added as an idea to have at least a message box notification in case of correction!

2. No solution and the option Refresh only Expanded and inserted members will not help with member recognition enabled.

In general pasting list of members is dangerous...

B.R. Vadim

[Former Member]

Thanks Vadim!

Is there a way we can use VBA and EPM function to enhance the standard BPC input template?

Xin

[former_member186338]

Hi Xin,

Very little can be done with VBA in general. Some specific input schedule can be customized with VBA, but you don't have methods to interact with member recognition. In EPM 15 patch 2 there are some settings but not sure yiu can use them.

B.R. Vadim

[Former Member]

Hi Xin,

we use some VBA in our input forms.

• as first you need to do 2 things:

open developer(alt+F11)/ Tools references/ enable it

in module declare epm functionalities:

Public EPM_function As New FPMXLClient.EPMAddInAutomation

• you need to know these events, so you can run your code when it is needed:

before_contextchange

after_contextchange

before_refresh

after_refresh

before_save

after_save

- usually declared as functions (if there is a need to stop it with FALSE in case you want to abort)

• then very important is to identify report position within a sheet using epm functionalities:
• as example below, all variables are integers

top_report_cellRow1 = Range(EPM_function.GetDataTopLeftCell(ActiveSheet, "001")).Row

top_report_cellCol1 = Range(EPM_function.GetDataTopLeftCell(ActiveSheet, "001")).Column

bottom_report_cellRow1 = Range(EPM_function.GetDataBottomRightCell(ActiveSheet, "001")).Row

bottom_report_cellCol1 = Range(EPM_function.GetDataBottomRightCell(ActiveSheet, "001")).Column

• very important is to faster your code using:

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.EnableEvents = False

• then you can control when and where is VBA code executed.

For example:

you can control what user has selected in after_contextchange

you can control what is user saving in before_save

you can do silent refresh and save

you can even control user options, run packages using VBA and lot more.

tomas.

[former_member186338]

Hi Tomas,

You provided info about well known methods and events of EPM Excel addin. But what is the relation between all this methods and automatic member recognition? The are no events like before_recognizemember...

Vadim

[Former Member]

Hi, sorry If I didn't full understand, I just reacted on following:

"

Is there a way we can use VBA and EPM function to enhance the standard BPC input template?

Xin"

Maybe I got it to general...

[former_member186338]

If you open an old discussion and want to add some value - please read the discussion from the very beginning, not only the last message without context

Vadim

[blu_taz18]

Hi,

Yes I agree with Vara , you can just use the EPM-> Insert Member

Regards,

Mitch LIm

[former_member190501]

Hi,

Just try following Sheet and User options to overcome issues :

1. Disable Active Member Recognition and save as default from EPM-->Options-->Sheet Options-->General

2. Check  Refresh only Expanded and inserted members from EPM-->Options-->User Options-->Navigation

Performance point of view I will suggest to use EPM-->Insert Members instead of Activating member recognition. If you enable user member recognition for every dimension member insertion of row, system will refresh . Instead if you use Insert members from context system will refresh only once for one or more than one rows based on selected members.

Hope it helps...

regards,

Raju