When users open a workbook in the SAP Enterprise Performance Management (EPM) add-in, they automatically receive data for their assigned cost centers. Users do not have to change any report selections; therefore, the EPM add-in enables the SAP system to quickly provide the information they need. This flexible solution also enables you to automatically determine dimension members because you can use this technique on any dimension, such as cost centers, profit centers, or products. The automatic selection of members by users results in more efficient reporting and also in better control over what data they can access.

Most companies require automated reporting solutions for ease of use and efficiency. For this scenario I first lay out the steps to set up the dimension property values and the report. I then explain each step in detail.

In my example I describe a scenario based on SAP Business Planning and Consolidation (SAP BPC) 10.0. SAP BPC 10.0 uses the Excel SAP EPM add-in. To set up this functionality, follow these steps:

• Use a dimensional property (such as REPORTUSER) to assign a user to individual cost centers
• Use the EPMUser function to determine the logged-in user
• Use the EPMDimensionOverride function to read the current user and then select cost centers based on the REPORTUSER property in the cost center dimension
• Create a new report with the cost center and account dimensions in rows, and time in

Before walking you through these steps, I briefly explain how EPM reports work with regard to member selection. When you execute an EPM report, the members are determined via the user’s security, the EPM Context, and the report axis selection relationship. For example, if users have read access for stores in the Southwest and Southeast regions of the United States, then they can view any store’s data in those two regions. However, they cannot see sales for a store in the Northwest or Northeast regions.

In a typical scenario I assume the EPM Context is set to Southeast manually by the user, and the report row axis is set to Context (EPM Context) Member and Children. The report then displays all the stores in the Southeast region along with the Southeast region total. The goal of this scenario is to automatically control which stores are displayed based on a dimensional property instead of the user changing the EPM Context and the row axis Member and Children selection.

Create a New Dimension Property and Set Up EPM Reports for Member Selection

Step 1. Use a dimensional property such as REPORTUSER to assign a user to individual cost centers. Log in to the Web client. In the Home tab’s Start page, select Planning and Consolidation Administration. Choose Dimensions and highlight the CostCenter dimension. Now choose Edit Structure and then select Add Property. As shown in Figure 1, enter a property ID and Name (e.g., REPORTUSER), and the Number of Characters (e.g., 50).

Figure 1

Add a new property to a dimension

Click the OK button and then the Save button. To access the members, choose Members of Dimension: CostCenter. In the member screen enter BPC users into the REPORTUSER column as shown in Figure 2.

Figure 2

Cost center dimension user assignments

To record the REPORTUSER assignments, click the Save and Process button to update the underlying SAP NetWeaver Business Warehouse (SAP BW) master data table.

The EPMUser Function

Step 2. Use the EPMUser function to determine the logged-in user. Open Excel and then choose the EPM tab. Log in to the environment and model in which you are working.  Models in SAP BPC represent transaction data. Typically, users use an expense model and a sales model. In cell A1, enter the EPMUser function with your model name as the Connection name. In my example I use CostPlan (Figure 3).

Figure 3

The EPMUser function

After you click the OK button, the user’s login ID is displayed in cell A1 (Figure 4).

Figure 4

An Excel file with the EPMUser function

I discuss the user ID in cell A1 in the next step.

The EPMDimensionOverride Function

Step 3. Use the EPMDimensionOverride function to read the current user and then select cost centers based on the ReportUser property in the cost center dimension. In cell A2, insert the EPMDimension function by typing EPM and then selecting the EPMDimension function from the drop-down list to display its parameters (Figure 5).

Figure 5

The EPMDimensionOverride function without parameters

In the ReportID parameter enter the report ID enclosed in quotation marks (Figure 6).

Figure 6

The EPMDimensionOverride function with parameters

In the Dimension field, enter the Dimension ID in quotation marks.  In my example I used the “CostCenter” dimension. In the Members parameter, enter the REPORTUSER property = the user from cell A1 in quotation marks (e.g., “REPORTUSER=“&A1&””). Click the OK button.

The function returns the text Expansion of CostCenter Overriden in cell A2 (Figure 7).

Figure 7

The EPMDimensionOverride in cell A2

Now you are ready to add a report that uses the EPMDimensionOverride function.

Create a New Report That Uses EPMOverride

Step 4. Create a new report with the cost center and account dimensions in the rows and time in the columns. With your cursor in cell A5 of the Excel file, for example, click New Report.  In the EPM Report Editor add CostCenter and the Account dimensions into the rows and Time in the columns (Figure 8). Drag and drop the dimensions into the row or column axis in the Excel file.  

Figure 8

The EPM report editor

While in the EPM Report Editor, left-click the ACCOUNT dimension to access the EPM - Member Selector screen (Figure 9). Under Selected Members on the right, you can see that the Relationship Member and Children has been chosen for the Member Name Indirect Expenses.

Figure 9

EPM Member Selector for the account dimension

To use these selections for the Account Dimension, click the OK button for the EPM Member Selection dialog (Figure 9). This action returns you to the EPM Report Editor (Figure 8). To run the report, click the OK button for the EPM Report dialog. As a result only the assigned cost center (Store 1) for the current user appears in the rows in the Excel file (Figure 10).

Figure 10

An EPM report for user BPC-01

If you log on as another user, such as BPC-02, you can see that only Store 2 appears in the rows (Figure 11).

Figure 11

An EPM report for user BPC-02

If you have a user assigned to more than one store, then you see all their stores. If you change the REPORTUSER assignment for Store 4 to REPORTUSER BPC-03 (Figure 12), then the report should fetch data for both Stores 3 and 4.

Figure 12

Cost Center Dimension with two stores assigned to user BPC-03

As a result, when the BPC-03 user runs the report, both Stores 3 and 4 have been selected (Figure 13).

Figure 13

Report results for a user assigned to two stores

To prevent the users from changing the report selection to a store they are not assigned to, turn on the EPM worksheet or workbook security.

Worksheet Protection

Step 5. Turn on worksheet protection. To turn on worksheet protection, go to Tools > Sheet Options. In the Protection tab, select the Protect Active Worksheet radio button, enter your password in the Password: and Confirm Password: fields, and click the OK button (Figure 14).

Figure 14

The EPM - Sheet Options Protection tab

After you click the OK button, the report selections cannot be changed. As you can see in Figure 15, if users attempt to type in a different store in cell A5, they get an error message. Also, the Edit Report option is not available, and the row axis is grayed out.

Figure 15

A workbook with sheet protection turned on

Charles "Tim" Soper

Charles “Tim” Soper is a senior education consultant at SAP. For the last 17 years, he has been teaching SAP classes on a variety of topics in financial and managerial accounting, business warehouse, business consolidations, and planning. He is the planning and consolidation curriculum architect and has written SAP course manuals. He also works as an SAP Senior Educational Consultant. Early in his career, he worked for Eastman Kodak Company in Rochester, NY, as a senior financial analyst. He has an undergraduate degree in economics and an MBA in finance from the University of Rochester.

You may contact the author at Charles.Soper@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.