How to format numbers in Lakhs and Crores in SAP BPC Excel reports?

[former_member204026]

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 (10⁵) and Crores (10⁷)

[>=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

Step 2:

Add reference to FPMXLClient

Step 3:

Add a new module to the VBA project

Step 4:

Add the following VBA Code.

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)