In the SDN Forum post BPX community member Doodwala asks "How is the scaling done in EvDRE?" This blog explains how to satisfy a very common reporting requirement to be able to display BPC data scaled in thousands (or by some other factor such as millions) within a BPC for Excel report defined using the EvDRE function. As I will explain the solution is actually very easy to implement.
From the BPC Administration client, begin by verifying your system contains the property called "SCALING" on the account dimension.
This scaling property field should be defined with a length of 1 byte. If for some reason your system does not have this property, then add it to the account dimension, or any other dimension that fits your business requirements.
Next maintain your dimension members and assign this property a value which represents a yes or no value. With this setup, yes means to use scaling and no means to not use scaling for each dimension member. In practice you can use any value you want to represent the logical condition, some possible suggestions are shown below.
Property Value | Yes condition | No condition |
"Y" or "N" | Y | N |
"1" or "0" | 1 | 0 |
"X" or "blank" | X |
|
Let's begin by creating a report or input schedule using the EvDRE function. Input =EvDRE() into a cell in the worksheet and click on the Refresh Workbook icon from the BPC for Excel toolbar. When the BPC - EvDRE Builder wizard opens, check the option "Allow formatting" as shown below.
In the EvDre control panel notice the definition of the FormatRange.
It is in the format range block where you will define the scaling factor you desire to be used in the EvDRE report. Under the CRITERIA column, add a new row which corresponds to the dimension and property shown above. In this example, I have defined the criteria as: P_ACCT.SCALING="Y". Remember to adjust this to your particular master data setup. Under the FORMAT column input any number which is greater than 10,000 since we want to see the effect of scaling to 1000 in this example. I chose to enter number 12345. Finally, under the column APPLY TO enter DATA to signify this formatting should apply to the data in the report.
Next select the cell where number 12345 was entered under the FORMAT column. Right-click and select "Format cells" from the context menu. Select the category for a "Custom" format. Then define the custom format using "0," which tells the system to scale the formatted cell display by 1000.
After clicking on OK, the value 12345 will be scaled by 1000 resulting in a display as 12.
Note: you can learn more about custom number formats in Excel from the Microsoft Office Online help at this link. For example, if you wanted to scale your data by a thousand and also display one decimal place, you could define a custom number format as "#.0,". If you need to scale your data by a million while displaying one decimal, add two commas after the number "#.0,," or use "0,," if no decimal places are to be displayed.
In my system, test data was entered as follows:
P_ACCT | EvDescription | Amount |
CE0004010 | Personnel Expense | 20,000.00 |
CE0004020 | Wages and Salaries | 10,000.00 |
CE0004030 | Social Contributions | 10,000.00 |
Finally, refresh your BPC report or input schedule to use the new formatting.
As you can see from this example, accounts CE0004020 and CE0004030 have scaling enabled through their member properties, while account CE0004010 does not. Account CE0004000 is a parent of each of these three accounts and it too has scaling enabled. While this example would not be logical from a financial reporting perspective, it is intended to demonstrate how you can control the scaling for each dimension member individually. You might for example need to do this in a report that combines dollar amounts scaled by 1000 and statistical amounts which should not be scaled, such as head count.
Note: This demonstration was conducted using SAP BusinessObjects Planning and Consolidation 7.0, version for NetWeaver. However it is equally applicable for the BPC 7.0, version for Microsoft.
1 Comment
