Improve SAP BPC Reports Using Local Member

SAP Business Planning and Consolidation (SAP BPC) 10.1 resolves some difficulties finance users have had in setting up advanced finance reports. It includes multiple solutions, one of which is a user-friendly feature called Local Member. Any finance user who has prepared a report using SAP BPC has needed to pull data from SAP BPC while also setting up dynamic Microsoft Excel formulas. Given that EPM is an Excel Add-in, it is fairly apparent that this is something that users can do. What is not apparent is how users should go about doing it. This blog is intended to alleviate that problem.

What Is a Local Member?

Local Member is an EPM feature that enables users to set up Excel formulas in their reports and to repeat them at the intervals they desire. For example, Figure 1 shows a variance from a budget report. This is a common report for finance users that is very easy to create using the Local Member formula. The benefit of a Local Member formula, as you can see, is that it is part of the report and it can repeat itself logically every time budget numbers are pulled.

How to Create a Local Member Formula

There are two ways to create a Local Member formula: New Report/Edit Report and Options/Activate Local Member Recognition.

1. From New Report/Edit Report: When you want to create a Local Member formula in a new report, click the New Report button. When you want to add a Local Member formula to an existing report, click the Edit Report button and the dialogue box EPM – Report Editor opens as shown in Figure 2.

This has a tab called Local Members that you can use to create a Local Member formula. The beauty of a Local Member formula in EPM 10.1 is that it is very user friendly and dynamic. It is easily available in the Report Editor. You can adapt to expanding rows and columns depending on the report output and also have the ability to place a Local Member before or after a member, member combination, dimension, column axis, row axis, or Local Member.

First, select the Enable check box, which enables the creation of a Local Member. Enter a Name, which displays on the list of Local Members on the left side of the pop-up. Then enter a description that will be displayed on the report. In the formula bar input the formula for the local member. The remaining options are a fairly exhaustive list of where you can place the Local Member. In my example, I placed the Local Member after the Dimension Category, Member Budget, as shown in Figure 2.

2. From Options > Activate Local Member Recognition: This approach for creating a Local Member is simpler than creating one using the New Report/Edit Report option. Local Member can also be set up by going to the Options button. Check the Activate Local Member Recognition check box (Figure 3).

After that, type a formula as shown in Figure 4 (Cell K14) and a Local Member is generated in the report (% variance columns H and L in Figure 5).

Figure 4 — Add the formula 

 

After the Local Member is created though, users need to go to the Edit Report screen and check all the parameters of the new Local Member. In Figure 6 you can see that a new Local Member, LocalMember001, was created and the Description of BUDGET was given. You can change these to whatever you prefer. To do so use the Report Editor and type over the Name and Description on the selected Local Member in the Local Member tab. Then you can refer to the Local Member later.

In Figure 7 I renamed it to % Variance. Notice that as the Local Member is attached to the Dimension category and Member BUDGET, it populated the % Variance Local Member automatically after the 2017.01 Variance from Budget Local Member that I created for the example shown in Figure 5. Note that in Figure 4 I input the formula after 2017.02, but it automatically generated the formula for 2017.01. That is because the Local Member recognized that I am trying to get that variance of Budget and Actual in 2017.02, so it is relevant to auto-populate the same for 2017.01.

Conclusion

The objective of this blog is to explain the use of Local Member and show how to create it using the two available options. Analysts can play around with the options and discover different ways to use them after learning the methods.