Improve BPC Formatting with the EPMCopyRange Formula

SAP Business Planning and Consolidation (SAP BPC) 7.5 users were able to perform powerful formatting using the FormatRange formula. Formatting is still important in SAP BPC 10.0 or 10.1, both in terms of consistency and for publishing reports to management and other users. SAP BPC 10.1 has packed the power of FormatRange beautifully into one formula: EPMCopyRange. This feature provides a variety of functionality, including the flexibility to perform row- and column-level formatting, which is lacking in the EPM Formatting sheet. Let’s look at some applications and components of EPMCopyRange that will make the lives of BPC users and analysts a little easier.

EPMCopyRange Components

The EPM Copy Range formula — =EPMCopyRange(REPORTID,ROWS,SOURCE RANGE,copyAllOnReportData) — contains a few components, shown in Figure 1.

Figure 1 The components of EPMCopyRange

• ReportID: ID of one or several reports. A default report is normally 000.
• Rows: Specify where the range will be added. Enter TRUE for row-driven formatting, FALSE for column-driven formatting. Figure 2 shows example of FALSE, where the column format is copied horizontally.
• SourceRange: Specify the cell range containing the content that will be added to the rows or columns.
• copyAllOnReportData: Specify if all content types are added to the report data. Enter TRUE to copy all content types. Otherwise, enter FALSE. The content types of the source cell include all the format settings, formulas, data, fixed text, and data validation settings. You can also leave this blank.

 

 

Figure 2 An example of entering FALSE in EPMCopyRange

EPMCopyRange in Action

EPMCopyRange formatting is useful when working on profit and loss (P&L) or balance sheet (B/S) statements. You might want different formatting on each row or account, but would like that applied to the columns or time, for example. This can also be done using the EPM Formatting Sheet, but can get complicated if you have to manually pick each dimension and input what format that line needs.

With the EPMCopyRange formula, you can simply have a mirror column (source range) before or after the report in line with the P&L that includes the formats and formula that you want copied (see Figure 2). Refreshing the report applies the format to all columns.

EPMCopyRange Column and Row Formatting

Figure 2 highlights column expansion, but Figure 3 shows how you can combine column expansion and row expansion.

A classic example for using combination formulas is in P&L statements. You can format columns based on the accounts and you can still add a monthly, quarterly, or annual variance in the row format. (Tip: You can use EPMCopyRange to populate any Excel formula or EPM formula; try using EPMMemberProperty, Vlookup, Conditional Formatting, or any Excel feature.)

You can add Blank Member to the dimension in the column to add a blank column to perform the variance in columns. Note the precedence: The copy range for row in cell A2 executes first, followed by the copy range for column in cell A1. This works correctly here as the P&L formatting stands and the variances are populated correctly.

Note also that EPMCopyRange overrides the EPM formatting sheet.

Figure 3 Combining column and row expansion

Conclusion

This formula can help ease a lot of formatting stress that analysts have to worry about when working under tough deadlines. This article just scratches the surface of what this formula can do. Analysts should try to use Vlookup and EPMMemberProperty in the row or column expansion and see how easy it is to populate those values in the report. Also, BPC administrators who prepare input templates should use the EPMCopyRange to lock specific cells, which comes in handy when you want to restrict input to certain lines but want to display all accounts on a template.