<img src="https://certify.alexametrics.com/atrk.gif?account=3HHNq1DlQy20Y8" style="display:none" height="1" width="1" alt="">

SAP Analytics
Thought Leadership


Posted by
Deb Silverman
Deb Silverman
on Wed, Apr 13, 2016 @ 11:04 AM

When comparing BPC to other products, a big factor to consider is the user interface experience. SaaS basedAuthor_Bio-.png
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.  Customize-Ideas-Identity-Individuality-Innovation-Personalize-Co-000070198643_Large.jpg

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

graphic showing EPMExecuteAPI within Excel Interface

  • 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
  1. Get/Set Context Member based on drop-down selection

graphic showing SetContextMember on EPM Excel Interface

graphic showing GetActiveConnection on Excel Interface

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.

Selecting Balance Sheet as Functional Area

  1.  User makes a selection
  2. Cell D1 picks up the index from the drop-down
  3. Cell D22 uses a VLOOKUP to identify hierarchy

The code picks up the index number and will be set based on hierarchy      4. The code picks up the index number and the context will be set based on designated parent-level account for that hierarchy.

function code from previous example

The code is triggered when the workbook is refreshed.

2. Open another document from EPM Workbook

Graphic showing OpenSpecificDocument

In this example we will use OpenSpecificDocument API.

In this example we will open MyFile.  You are not limited to Excel files.

Opening a new excel file

Open a blank workbook and type =EPMExecuteAPI then click on fx to access the functional dialog box.

 type =EPMExecuteEPI in blank workbook  

  1. The API name: “OpenSpecificDocument”
  2. The Displayed text: “Open Source Report” (or whatever you want the cell to read)
  3. Parameters: is the path Reports\Source_Report.xlsx
  4. Click OK
  5. 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.

Using DataManagerRunPackage API

To use

1. Similar to above, in excel enter =EPMExecuteAPI

2. Click on fx to access the dialog box

EPMExecuteAPI 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.

Triggering Logic to Process Chain

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

view webcast

deb_silverman.pngAuthor 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

Topics: Excel, BPC (Business Planning & Consolidation), best practices for epm, API, VB


Recent Posts

Posts by Topic

see all

3  S T E P S

To Enhance Your EPM Performance:

 1.Get Your Ultimate Guide to Improving SAP EPM Performance

ebook 2

 Learn what could be contributing to your poor performance and how to diagnose common problems. Get tips that will empower you and your team to improve the  performance  of your system in order to get the most out of your SAP EPM Investment.

Get the e-Book


2.  Test Your BPC Performance 

Benchmark 2

Get a BPC performance report card and custom report to identify performance issues you may not even know you have.

Test my BPC


  3. Get Your EPM Assessment  


Our assessment delivers a complete, best practice roadmap for you to follow.  We’ll work together to assess what you need to pivot your financial forecasting, planning, reporting and analysis to handle the compressed requirements from the volatile COVID-19 environment. 

New Assessment