When comparing BPC to other products, a big factor to consider is the user interface experience. SaaS based
EPM solutions often have significant constraints to the available options when designing the experience users will
interact with, due to the inherent design limitations of those solutions. BPC’s robust Excel interface has an unlimited ability to meet any user experience requirement. In addition to the significant numbers of Excel functions included in the EPM Add-in that can be applied creatively to meet any need, sometimes it is necessary to leverage Visual Basic to create highly specific behavior in the interface.
As always, it is prudent to mention that VB should be applied sparingly and as a last option in the series of choices because of the more technical skill level required to support this functionality. In many organizations, this may not be an issue as VB skills are ubiquitous and VB enhanced workbooks are the norm. In that case, a little VB can open up a whole new world of possibilities in tailoring your experience to any behavior your users could desire.
In this blog, we will discuss how to enable a VB API that can add a lot of flexibility to your reports, input schedules, and any process within the BPC solution. 
Key terms: RefreshActiveReport, RefreshActiveSheet, SaveWorkbookData, SaveWorksheetData, OpenSpecificDocument, and DataManagerRunPackage
Before we jump in and walk through a few API’s, for those not familiar with SAP’s EPM APIs, let’s take a look at the list of tools available:
- Enable Developer ribbon
- Click Visual Basic
- Check tools -> References -> FPMXLClient
- View ->Object Browser -> and choose FPMXLClient library from top drop-down selection
- Double click on EPMExecuteAPI (shown in graphic above)
- In the right column you will see the available API’s
Most common API’s we use are RefreshActiveReport, RefreshActiveSheet, SaveWorkbookData, and SaveWorksheetData – there are others we can explore!
We will walk through a few examples how to use EPMExecuteAPI:
- Get/Set Context Member based on drop-down selection
- Open another file from EPM
- Run a process chain from EPM
- Get/Set Context Member based on drop-down selection
In this example we will use GetContextMember SetContextMember, and GetActiveConnection (shown above)
Here we have a drop-down menu (using Data Validation to build the list). Each item in the list represents a hierarchy (or parent). When the user chooses an item in the list it is assigned a parent level account, which is what the workbook will use when refreshed.
- User makes a selection
- Cell D1 picks up the index from the drop-down
- Cell D22 uses a VLOOKUP to identify hierarchy
4. The code picks up the index number and the context will be set based on designated parent-level account for that hierarchy.
The code is triggered when the workbook is refreshed.
2. Open another document from EPM Workbook
In this example we will use OpenSpecificDocument API.
In this example we will open MyFile. You are not limited to Excel files.
Open a blank workbook and type =EPMExecuteAPI then click on fx to access the functional dialog box.
- The API name: “OpenSpecificDocument”
- The Displayed text: “Open Source Report” (or whatever you want the cell to read)
- Parameters: is the path Reports\Source_Report.xlsx
- Click OK
- Now double click on that cell and the report will appear.
You may be asking the question ‘why not use EPMLink or EPMPathLink()”? You can! EPMPathLink() is used when the context information is required. You can also link to a local or EPM connection. The OpenSpecificDocument API does not manage context nor does it matter what file extension is used.
3. Triggering a Process Chain from EPM Workbook
In this example we will use DataManagerRunPackage API.
To use
1. Similar to above, in excel enter =EPMExecuteAPI
2. Click on fx to access the dialog box
What is missing from a lot of documentation I have come across are examples how to use the parameters. When you look at SAPs documentation in the snapshot of parameters in using this API it isn’t clear. When triggering this AP from Excel you do not use those parameters!
In our example we will point to a folder where the copy package is stored. You can’t include the path in the package name. Also, you do not need team unless you are triggering the logic using VBA.
3. Double Click on Process Chain (users must have security to trigger the package
4. Run Package will launch.
VB can be a powerful, and really unlimited, tool in the arsenal of BPC developers. If you would like to take your user experience to the next level, please reach out to your Column5 representative to discuss how applying tips like this could benefit your solution! Or if you have another VB question, contact us and we will respond shortly!
Related Articles:
Blog Post: Useful Tips of Advanced Functionality in EPM10 Dynamic Formatting
Blog Post: Calculating Column Totals Dynamically - SAP BPC
Blog Post: How to Suppress Send Dialog Box in SAP BPC
Blog Post: Using the Power of Visual Basic in EPM 10 Reporting
Webcast: Taking your HR Planning & Forecasting to the Next Level with DarwinHCM and Business Planning and Consolidation
Darwin Modules are the next evolution in Starter Kits and RDS options. This session is focused on DarwinHCM and how this module can provide a high degree of value and benefits for Human Resource and Headcount Planning BPC Environments. DarwinHCM includes pre-built advanced input forms, report templates, business process flows and optimized logic/business rules that are designed to be deployed in as little as one week at a fixed price. We will walk through the sample business process flows and focus the demonstration on the process from the business user perspective.
Demonstration Highlights will include:
- Global Drivers and Setting
- Adding New Employees
- Transfers, Promotions and Terminations
- Real-Time Reporting & Analysis
Author Bio:
Deborah Silverman has been with Column5 Consulting for almost 5 years as a Senior Consultant. She is one of our most popular blog writers with her decades of software experience. She has assisted in hundreds of implementations and specializes in SAP BPC.
Deborah Silverman, Senior Consultant, US Team
