Skip to Content

In the SDN Forum post Scaling in reports BPC7NW 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.

 

image

 

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

 

 

image

 

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.

 

image

 

In the EvDre control panel notice the definition of the FormatRange.

 

image

 

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.

 

image

 

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.

 

image

 

After clicking on OK, the value 12345 will be scaled by 1000 resulting in a display as 12.

 

image

 

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.   

 

image

 

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.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. David Fletcher
    Hi Jeff,

    Just a couple observations;

    The original purpose of the “Scale” property in an “Account” type dimension is to enable scaling with the use of the “evgts”, (get scaled value) function. The bulk of BPC customers and the preloaded temples still use this function. Thankfully, Outlooksoft did a great job documenting this in the online help.

    In the SAP document, “How to Maximize Advanced Formatting for BPC” it presents and infers the “Scale” property not only indicates the scale of the value but the type of value, (percent, scale value, ratio …).

    For example, the “scale” property would maintain values for multiple scales, (i.e., 1, 100, 1000 …). You can also maintain a value such as “Percent” to indicate the value is a percentage or perhaps “Ratio” to indicate a ratio vale.

    In the formatting section;
    Criteria:  Account.Scale=% Format: 12.3%
    Criteria:  Account.Scale=%2 Format: 12.34%
    Criteria:  Account.Scale=1000 Format: 1,234,567,890
    Criteria:  Account.Scale=Ratio Format: 12.3

    It is also possible to use the “Scale” property within a conditional format or a formula to present the scaled value. (Used in a formula will usually address rounding issues.)

    Thanks!
    David Fletcher

    (0) 

Leave a Reply