SAP Analytics
Thought Leadership

We all encounter game-changing challenges, so here’s my story of how a simple task we do every day has taught me that things are not always as easy as they seem. 

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.

Need to calculate a grand total dynamically?  Shifting of Local Members giving you a headache?  I previously used Indirect() functions, but I discovered with larger reports this put a drag on reporting performance.  To sum from the first report column to the last report column I was using something like this  =SUM(EPMPOSITION(1):INDIRECT(ADDRESS(ROW(),COLUMN()-1))), which is not considered best practice but I got results!

The SAP definition: “EPMALLMEMBERS is used to perform a calculation (such as sum or average) on all the members displayed in a row or column axis, whether the axis contains one or several dimensions”

HOW?  Here are some examples:

BPC has always been a flexible tool with many “hooks” through Excel and the database layer enabling all manner of customizations to increase usability to customers’ exact specifications. The risk of customizing is the potential of that customization being an impediment to an upgrade. One popular access point provided by SAP/OutlookSoft was the “MNU_Commands” hook.

This blog is part of a series on reporting techniques to help you get more value from SAP BPC.

DROP-DOWN LISTS help make a worksheet more efficient by provided a controlled list of options that make the report or input template respond to preconfigured options.

To create the list use Excel’s Data Validation tool. We are going to take this a step further by populating the list with EPM reports on a ‘List’ tab within the workbook.  In the snapshot below we are using drop-down list for Company, Cost Centre, Program, and Account. Each drop-down list will have its own report to populate the list.

COST CENTER LIST: A list of cost centers is created using fixed members that had been input. In this example, the dropdown has a friendly label that is the result of concatenating ID & Description, but the report will only use the dimension ID’s referenced on the list tab by using Excel’s VLOOKUP function to match the concatenated friendly label to the ID from the right row.

The formatting strategy chosen for a report or input schedule is one of the most important decisions made in development because it influences performance, complexity and the “fun factor” for development.  I say this with humor because the report may take 4 hours to build and 8 hours to format!  There isn’t room enough to go into all aspects of formatting in detail, however the understanding of choices we have is critical and can certainly influence the overall life cycle (maintenance and productivity).

The EPMFormatting sheet is used to format dynamic reports and input templates.  It is an Excel sheet that can be copied and changed for multiple worksheets and reports.  Dynamic formatting means the formats are applied to levels of a hierarchy, types of members or column and page axis dimensions.