In a live Q&A on SAPinsider Online, Financials Expert advisor Peter Jones discussed changing requirements for financial reporting and shared best practices advice for enterprise performance management (EPM), SAP Business Planning and Consolidation, and more. Below is an edited selection of questions from SAP customers.
Q: You’ve written a few articles about the EPM add-in. Any quick notes or updates on the EPM add-in for FI/CO users as well as SAP Business Planning and Consolidation customers, and where it can help update financial reporting?
Peter Jones: Absolutely. There are a number of interesting opportunities for using the EPM add-in for non-SAP Business Planning and Consolidation InfoProviders. We can also see that SAP is starting to make inroads into integrating the EPM add-in with the other reporting tools available.
For example, there will be a change around Q2 of 2015 where the EPM add-in will become a part of the Analysis for Office component. I’m not sure which one will be the main focus of this effort, but I can say that this will become the main front end for BPC activities.
Q: When working on EPM reports, we have to use a lot of Excel functions and macros for complex reports. These sometimes seem to interfere within the EPM reporting area. Are there any suggested guidelines on how to use both EPM functions and Excel functions in a report or input template? Is SAP planning to add more functions to the EPM tool?
Peter Jones: When I’m working with business users, one question seems to always come up: “How do I do a specific function or process within the EPM add-in?” My answer: Always remember that the EPM add-in is an Excel spreadsheet. There are many activities within the EPM add-in that you accommodate by using basic Excel functionality. This is very different than if we were working with SAP BusinessObjects or SAP Business Explorer (SAP BEx) components — in these cases, we mostly look to the actual toolset to give us the approaches to solve specific issues. In the case of the EPM add-in, the solution is to use Excel. So, to answer your question, there are definitely quite a few times when we use Excel functions.
I have used quite a few macros, VBA, vlookups, etc. within the EPM add-in and, yes, there have been times where these solutions impact the reporting area. For example, I have had issues keeping the formatting correct when I’m using some of the macros and VBA, but after tweaking the code I’ve been able to resolve the issue — maybe not to the extent that I would like, but definitely to achieve the required results.
In another situation, the VBA code was locking up the Excel spreadsheet, but adjusting the timing of the different calls of the VBA code helped fix that issue. I have also run into times when the Excel formula is more complex than the EPM add-in can handle and turns into a “local formula.” For example, I was able to get an IF/THEN statement to convert to a local member formula, but I added a bit of additional Excel code to do a search on values column by column. In that case, the EPM add-in would not convert that formula to a local member formula, so I had to go with the Excel formula and adjust to make sure that it covers the entire column’s values. It didn’t look as pretty as a local member formula would look, but it did the job.
I can’t say that there are best practices or suggested guidelines for the integration of EPM functions and Excel functions. But I would say that using the EPM features would be the first layer I would look to for solutions. EPM functions and the FPXML functions would be the second layer, and then finally look to Excel for the solution. For example, you may need to use reference information in a report for calculations or formulas. I would position the reference information in the same worksheet so that I could use the EPM functions rather than putting it in another worksheet and then having to use an Excel reference worksheet-to-worksheet to reference the data. This way, you use EPM functions before reverting to Excel functions.
Q: Our users are asking more often for drill-down capability to the details. What is your best practice for that when using SAP Business Planning and Consolidation?
Peter Jones: In this case, I’m thinking you are talking about the idea of drill-through versus drill-down — drill-down being the ability to double-click on a hierarchy, for example, and see the lower-level values show up in the report. This feature is very mature in all of the reporting components of SAP systems, except for some limitations within Crystal Reports to accommodate hierarchies for allowing drill-down.
In terms of drill-through, the functionality within SAP Business Planning and Consolidation with the EPM add-in is pretty good, and is about the same as we would see in either SAP BEx or other SAP BusinessObjects components. The set-up is not really available for the business user, as many of the other functions are, and this may have a bit of a drawback, but overall the drill-through ability is very good.
I will say that the standard drill-through from an EPM add-in component to the SAP BEx-level components is a bit of a challenge. There are two different reporting toolsets to get comfortable with and to understand the nuances of. So, initially the drill-through starts with the user executing the function from the EPM add-in, and then the drill-through report is an SAP BEx web-based report.
Therefore, for the first-layer drill-through, you need to understand EPM and the SAP BEx web. If you decide to go with a second-layer drill-through, if you are heading to SAP ECC you will have to be familiar with the SAP ECC reporting components as well.
I find the challenge for this feature is where the data is available — knowing that we may have summary data in SAP Business Planning and Consolidation for the EPM add-in, and the more detailed data in SAP BW. The next layer is the challenge. Where’s the very detailed data — in SAP BW in a DataStore object or in SAP ECC? Once that decision is made, then the set-up or design of the drill-through can be achieved.
There is also a limited amount of drill-through if you use the EPM add-in against non-SAP Business Planning and Consolidation objects such as SAP BW InfoCubes or other providers. But it is possible to execute a drill-through to the first layer of information from the EPM add-in.
Gain additional guidance from Peter Jones and other Financials Expert authors in the anthology Best Practices for Financial Reporting in SAP.