Skip to Content

Business Case

During implementation of SAP BPC for Indian customers you may be required to format the report numbers in Lakhs and Crores.  The following is the formatting requirement of the numbers in the reports.

Number

Format

1000 (10^3)

1,000

100000 (10^5)

1,00,000

10000000 (10^7)

1,00,00,000

1000000000 (10^9)

1,00,00,00,000

100000000000 (10^11)

1,00,00,00,00,000

You can use the format custom number format string like the one mentioned below to cover Lakhs (105) and Crores (107)

[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##\,##0

But unfortunately these custom number formats are allowed only two conditions (like [>=100000]), so there is no single custom format that will handle all scenarios mentioned above.


Solution

Use custom VBA code to format the cells based on the values in each cell retrieved by the EPM report.

Steps

Step 1:

Create a BPC report. Go to VBA interface in Excel (Alt + F11), go to tools > reference

/wp-content/uploads/2013/12/image002_353252.jpg

Step 2:

Add reference to FPMXLClient

Step 3:

Add a new module to the VBA project

/wp-content/uploads/2013/12/image004_353247.jpg

Step 4:

Add the following VBA Code.

/wp-content/uploads/2013/12/image006_353248.jpg

Code:

‘Author: Badrish Shriniwas Date:31/12/2013

‘Event called after report refresh

Function AFTER_REFRESH()

Dim EPMObj As New FPMXLClient.EPMAddInAutomation

Dim rngData As Range

‘Gets the range of the report data

Set rngData = Range(EPMObj.GetDataTopLeftCell(ActiveSheet, “000”) & “:” & EPMObj.GetDataBottomRightCell(ActiveSheet, “000”))

‘Calls the formatting procedure

Call procFormatCell(rngData)

End Function

‘Formatting procedure

Public Sub procFormatCell(rngData As Range)

Dim rngCell

For Each rngCell In rngData

    Select Case rngCell.Value

    Case Is >= 10000000000000#

        rngCell.NumberFormat = “##””,””00″”,””00″”,””00″”,””00″”,””00″”,””000″

    Case Is >= 100000000000#

        rngCell.NumberFormat = “##””,””00″”,””00″”,””00″”,””00″”,””000″

    Case Is >= 1000000000

        rngCell.NumberFormat = “##””,””00″”,””00″”,””00″”,””000″

    Case Is >= 10000000

        rngCell.NumberFormat = “##””,””00″”,””00″”,””000″

    Case Is >= 100000

        rngCell.NumberFormat = “##””,””00″”,””000″

    Case Else

        rngCell.NumberFormat = “##,###”

    End Select

Next rngCell

End Sub

Step 5:

Report format (after refresh)

/wp-content/uploads/2013/12/image007_353246.png

To report this post you need to login first.

5 Comments

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

  1. abilash n

    Badrish thanks for blog and that too on last day of year 🙂 . Awaiting for more excellent blogs in coming year 😉 ….
    Happy New year wishes

    (0) 
  2. Amlan Chowdhury

    Hi Badrish,

    I was trying the scaling using conditional formatting as had encountered in EPM Academy Sessions which dint seem to work for 10.1 as my Formatting Sheet Option does not take reference of the Active Report Worksheet. Even giving the same cell as reference dint seem to work to scale the data. eg: Suppose ‘D2’ is the selection where scale factor is available on the Report Work Sheet. Hence I tried to apply D2 in the data content for my condition in Conditional Formatting in Formatting Sheet for Columns.

    Apart from this I have a requirement where the Scaling must also apply to Excel Formulas and Local Members if any. This is a completely Static Report requirement which I am unsure of how to achieve. Also whenever the Report Opens, it must check the existing scale factor based upon which the Report must refresh.

    Thank you in advance.

    Warm Regards,
    AC

    (0) 
  3. rishabh gupta

    Hi Badrish,

    Thanks for the blog, I just have a requirement.

    We have multiple worksheet in the workbook and we want to format the numbers in every worksheet.

    Since the code is only working on Activesheet, Numbers are not getting updated on other sheets.

    It works if we pass the worksheet name for example ‘Sheet 1’ hardcoded to GetDataTopLeftCell, However doesnot work if we pass Worksheet Name like ‘Account’.

    Please help us in providing us a solution to get numbers in all worksheets to be updated.

     

    Thanks,

    Rishabh Gupta

    I i

     

    (0) 

Leave a Reply