Compare the Reporting Benefits of Two Key SAP Business Planning and Consolidation Functions

A classic dilemma for reporting analysts who are trying to add a Microsoft Excel formula to an EPM report is whether to use EPMCopyRange or the EPM Local Member.

• The EPMCopyRange feature is used to repeat the content of a range of cells to the bottom (columns) or side (rows) of a report. The range begins after the row axis and below the column axis.
• EPM Local Member allows you to create a new member for adding a formula or calculation to your current report. These Local Members behave like any other member in the report but contain an Excel formula or EPM function.

Each approach has the following pros and cons.

 

EPMCopyRange Pros

 

Inserting a formula as part of the report:

Let’s take a cost center expense report as an example (Figure 1). After the column axis you want to input Cost Center Property, Account Property, Vlookup on Cost Center Property, and Vlookup on Account Property respectively. This is easily done using EPMCopyRange. As shown in Figure 1, in cell D1 you define the CopyRange, and in rows D7 to G7, you define the formula and format. This is done in a matter of minutes. Using Local Member instead is much more cumbersome as you need to add the details manually and define the formatting separately for each local member using the EPM Formatting Sheet.

Ease in formatting:

Even though this point is covered above, it does warrant a separate mention as a pro for EPMCopyRange. Formatting is easily done by formatting the cell range that is used in the EPMCopyRange. For example, in Figure 1 the format in the range D7 to G7 is replicated in the cells below it. Complicated conditional formatting can also be done on the cell ranges. Achieving the same effect using the EPM Formatting Sheet could become complicated, and it requires an additional step after you add the Local Member.

Complex Excel formula:

If you have used a Vlookup, Index Table, or SumIf on a Local Member, you are aware this can be cumbersome. Sometimes users face issues with these not working in Local Member. This is because it is critical to get the ordering of Local Members correct when you use complex Excel formulas. If you do not get it right you could face issues with the report, such as the Local Member not being populated or #N/A being populated. But this is easy when you use EPM CopyRange, as shown in Figure 1.

 

EPM CopyRange Cons

 

Linking a formula to a dimension member:

While using EPMCopyRange it is tough to repeat a formula based on a dimension member. This ability is the biggest pro of the Local Member (i.e., repeating formulas for specific dimensions). One-off repetitions can be handled in EPM CopyRange also using an IF formula, but I suggest using Local Member if there are many repetitions. For more details refer to this point in the pro section for the Local Member.

 

Local Member Pros

 

Linking a formula to a dimension member:

A Local Member is specifically helpful if you want to repeat a formula after a specific dimension. As shown in Figure 2, if you need EBIT% (% of Revenue) every time you populate EBIT, then using a Local Member helps. It populates this information every time EBIT is populated.

Note: For this to work, click the Edit Report button in the Local Members tab to open the Report Editor functionality. Select the Member radio button in the Attached to section as shown in Figure 2.

 

Local Member Cons

 

Placement for the Local Member:

Unless you go to the Edit Report screen and attach the Local Member to a specific Member, Member Combination, Dimension, Column Axis, Row Axis, or Local Member as shown in Figure 2, the report randomly picks what it thinks is the best Attached to selection when analysts build a report. This can be confusing for beginners, and it can be cumbersome even for experts who understand this as they have to go into each Local Member and update the ‘Attached to’ option to make sure the Local Member populates in the correct position.

Formula Bar in Local Member:

In the formula bar in Figure 2 notice the formula =EPMMEMBER([ACCOUNT].[PARENTH1].[EBIT])/EPMMEMBER([ACCOUNT].[PARENTH1].[TOTREVENUE]).

This can sometimes be a problem, especially when you are trying to use this for variances. For example, in Figure 3 you use the Local Member to compare the 2017.01 Estimate versus the Actual. But in February the month changes to 2017.02 (Figure 4) and the Local Member goes away as the formula is dependent on the category and time.

Formatting:

Formatting can be tough while using the Local Member as you need to manually update the EPM Formatting Sheet with the format for each Local Member. This can be cumbersome when there is a shortage of time to prepare a report.

 

Conclusion

 

I believe that EPM CopyRange provides more flexibility to users as it has more pros than cons. Local Member is a more out-of-the-box solution and is normally the first go-to for first-time users. However, I would like finance users to explore the many benefits that EPMCopyRange provides. If introduced early on, EPMCopyRange is more intuitive to finance users than Local Members. When you move from being a user to a super user, you may find that EPM CopyRange is a super feature of SAP BPC 10.1. For financial reporting users, EPMCopyRange eases a lot of design and formatting issues without requiring help from an SAP BPC subject matter expert (SME) or a developer. It promotes self-sufficient usage and also the use of SAP BPC as a reporting tool because of its flexibility feature.