BPC EPM Scaledata formatting with Dimension member property and formula - SAP BPC 10.1 NW
- Created by Krish Raju.
Background: Few of the BPC Financial reports are to be reported in 1000s and millions. This can be achieved using simple dynamic formatting using the Excel standard conditional formatting using the Excel custom formatting function (#,##0.0,,). There are few limitations in using this format. The cell values are displayed based on the scaling in 1000s, but the actual underlying values are not changed.
For example in the below Excel formatting for in 1000s, the cell displays correctly but the actual cell values is still the full numeric value. This causes issue while converting the worksheet to standard Excel for circulation. If you copy the report and paste it in Excel, the scaling is lost.
Suggested Solution - use EPMScaleData with Dimension Member formatting: There are many formatting options available within EPM FormattingSheet function and can also be implemented for specific Dimension member property. This helps to retain the Local member formula without being overwritten by the dynamic formatting
This solution works well in BPC 10.0 / 10.1 and EMP SP18. I am sure this will still work in the higher EPM SP level as well.
Benefits of the formatting option.
- Scaling is automatically applied in EPM report for the specific Olap member dimension and property. Scaling is automatically applied to member & children even if drill-down in EPM reports
- EPM Local members and formulas in rows and columns are not disturbed.
- EPM Report copied and pasted to a new Excel report as value still retains its scaling.
- Scaling and report is accurate as the scaling parameters Dimension members are fixed based on page axis values.
Steps to implement the EPM Scaledata formatting
- In the EPMformatting sheet Add Dimension member and property for Account dimension.
- Update the EMPScaledata formula in the “Use” column in the Dimension member / Property section of the formatting sheet.
- Update Scaling values in the report
Step#1 – Update Dimension member property
Make sure the formatting is Active by checking the box in the formatting sheet.
Account member property FIN_STMT = PL / BS used to exclude Local members from the default formatting, Local members uses Excel formula and in few cases % formatting.
In the Dimension/ Member formatting section of formatting sheet. Click “Add Member property”, go to Property Selection tab.
Select fist set of Dimension and property
Click “Add Multiple selection”
Select next set of Dim and property
Click Add to Multiple Selection (if necessary)
Go to Multiple selection overview
Click OK
The Dimension member and property selection is displayed in the formatting sheet.
Number formatting - In the Data column of the formatting sheet, update number and font format
Step#2 - Update the EMPScaledata formula in the “Use” column in the EPMFormatting Sheet.
In the EPMFormatting Sheet in “Use” column of the formatting sheet, copy the below formula and adjust the “empmememberid” cell references based on the actual cell in the report sheet. – For example the scaling factor is in cell D1 and cell A6 is Audit dimension in the report.
EPM Scale data formula: (Content = =(epmscaledata(, $d$1, epmmemberid($a$6), epmmemberid($a$7), epmmemberid($a$8), epmmemberid($a$9), epmmemberid($a$10), epmmemberid($a$11),epmmemberid(b$17), epmmemberid(b$18), epmmemberid(b$19), epmmemberid($a20))))
Note: It is recommended to include all the Dimension members used in the report in the scale formula. If the dimendsion members are not in the formaula, the report values are derived based on the EPM Context members even if this is defined in the Page axis of the report.
For example, in the below report all the page axis Dimension dell referenced in the formatting formula.
Step# 3- Maintain Scaling factor: In the Excel report, maintain scaling factor for 1000 and Million in one of the cell (cell D1 in this example) with drop-down selection option.
Step#4 – Apply Dynamic formatting setting in the Edit report options tab
As explained earlier this formatting is applied to all Olap member values. Local Member values are derived in the report and hence it will use the original format or specifically defined format. This is to prevent overwriting special format used for ratio percentages.
Krish Raju
