BPC EPM Customising without VBA

November 19, 2017

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.

 

 

Please reload

Recent Posts

Please reload

Archive

Please reload

Tags

Please reload