BPC EPM Customising without VBA

With VBA programming its possible to do almost anything you want within a Microsoft Office product such as Excel. However it's not always desirable to do so and not something I would recommend to my clients because even withe the best documented code it can be difficult to support after the consultant who wrote it has moved on.

BPC EPM add in does have an excellent function EPMExecute API() that allows the addition of buttons to run specific packages and functions without the requirement to save the Excel sheet as an .xlsm or .xltm file.

EPMExecuteAPI is a standard EPM function that allows you to add links to BPC reports and input forms which can call specific items from the menu without the need to add a VBA module to the spreadsheet as it calls the code directly from the EPM add in library.

Refer to the section headed EPMExecuteAPI in the the EPM user guide

The general syntax is shown below

To find out exactly which functions can be called using the EPMExecuteAPI formula refer to the library.

In case you are completely unfamiliar with vba reference libraries follow these steps

From a Excel worksheet - press Alt + F11 to open the visual basic window

Click Tools/References from the menu and add the library FPMXLClient as shown below

Now press F2 or select view Object Browser from the view menu and select the FPMXLClient library - navigate to the class IEPMexecuteAPI to view each of the members of the class

You can now add a formula to any cell in the Excel sheet to call any one of the members of this class - view the description of each member to see what parameters are required to call each correctly

Example 1

Add a button to call a specific data manager package

From data manager run package menu we can see the packages that exist , in this example we will add a formula to the excel sheet that calls one of the packages highlighted below.

Check the syntax required for the method that you want to call, in this case DataManagerRunPackage

Now add a formula to a cell in the Excel sheet

Provided that the syntax of the the formula isexactly correct then when you double click on the cell in the sheet that contains the formula it will execute the package

Example 2

Add a button to call a data manager link package

Package links are a bit more complicated because the parameter that is required is the technical id of the the package link so you will need to refer back to the back end table UJD_PACKAGE_LINK in order to find the the id

The package link Sample Link exists as shown from the data manager run package link menu

To find the ID of this package, log into the SAP GUI and execute the SE16 transaction to view the content of the table UJD_PACKAGE_LINK

Copy the link ID and paste it into the formula in the Excel sheet

These formula links can be extremely useful in guiding your users around the tasks required in the planning process from within a single input for, or report and making sure that tasks are executed in the correct order. They can also be useful as an alternative to using defualt logic, for example have an input form with a button that runs a script to recalculate values by executing a specific script. Default logic would ve executed whenever data was saved from any input form in the same model , but these buttons can be targeted to execute only the relevant script for each input form.