Lakhs Crores Indian Comma Separation in number ->SAP ABAP, Flex/AIR & Excel

[Former Member]

Acknowledgements: I owe a lot to many posts in STACKOVERFLOW & SCN

__________________________________________________________________________________

Common requirement in India is Indian comma separation

Unfortunately Excel, SAP, Flex etc offer only Thousand separator

COBOL had great flexibility in offering choice

SDN has many messages and some solutions on this very issue;

My solution(s) combines Indian preference + leading sign.

Indians think lakhs and crores;  10 lakhs = 1 million 1 crore = 10 million

Indians write cheques in this convention and top management always complain about the extra mental effort

to read the figures esp in INR. Top management need to feel figures as they prefer.

SAP has a lot to say on "ergonomics"; hopefully this may find place in Standard SAP

where all reports like FBL3N FBL1N FBL5N etc speak crores and lakhs for the very large Indian SAP user base.

I faced need from senior managers in a Flex Application and later implemented in ABAP

with EDIT MASK for amounts in Indian Style with BONUS leading sign

Even total comes in crores lakhs and leading minus

If ABAPers use edit mask '==ZLAKH'  in any field catalog even WRITE you will see that it works 100%

ABAPers try YJNCTEST76 and try -1234567898.9 as input

The conversion exit needs very good understanding of regular expression

https://help.sap.com/abapdocu_70/en/ABENREGULAR_EXPRESSIONS.htm

ABAP Code

1. Create a dummy domain ZLAKHS copy of WERT13N but with conv exit ZLAKH

    The edit mask ==ZLAKH will for any type 0,1,2 or 3 decimals

2. Input conversion is dummy

FUNCTION conversion_exit_zlakh_input.

*"----------------------------------------------------------------------

*"*"Local Interface:

*"  IMPORTING

*"     REFERENCE(INPUT)

*"  EXPORTING

*"     REFERENCE(OUTPUT)

*"----------------------------------------------------------------------

   MOVE input TO output.

ENDFUNCTION.

3, This is the real code! Can be used for Integer, Quanity & Values

    Hidden assumption User is NON-EURO - that is . is decimal and , thousand separator

FUNCTION conversion_exit_zlakh_output.

*"----------------------------------------------------------------------

*"*"Local Interface:

*"  IMPORTING

*"     REFERENCE(INPUT)

*"  EXPORTING

*"     REFERENCE(OUTPUT)

*"----------------------------------------------------------------------

* https://help.sap.com/abapdocu_70/en/ABENREGULAR_EXPRESSIONS.htm

   WRITE input TO output LEFT-JUSTIFIED.

   REPLACE ALL OCCURRENCES OF ',' IN output WITH ''.

   FIND '-' IN output.

   IF sy-subrc = 0.

     REPLACE ALL OCCURRENCES OF '-' IN output WITH ''.

     CONCATENATE '-' output INTO output.

   ENDIF.

   REPLACE REGEX '(\d+)(\d{3}.*)$' IN output WITH '$1,$2'.

   DO.

     FIND REGEX '\d{3,},.+' IN output.

     IF sy-subrc <> 0.

       EXIT.

     ENDIF.

     REPLACE REGEX '(\d+)(\d{2},.+)' IN output WITH '$1,$2'.

   ENDDO.

ENDFUNCTION.

4. Usage in WRITE

REPORT yjnctest76 MESSAGE-ID zmsgs.

DATA: p_nrm(20) TYPE c,

       p_ind(20) TYPEc.

PARAMETERS: p_input TYPE salk3.

START-OF-SELECTION.

   WRITE p_input TO p_nrm LEFT-JUSTIFIED..

   WRITE p_input TO p_ind LEFT-JUSTIFIED USING EDIT MASK '==ZLAKH'.

   WRITE: /, p_nrm, p_ind.

5. Usage in SALV ABAP (any ALV method supports EDIT MASK)

zjnc_tblr_column->set_edit_mask( '==ZLAKH' ).

__________________________________________________________________________________

Flex/AIR is where this innovation of Indian comma separation started

This is done by custom item rendering; Adapted from Java

See code below for AS3(Flex/AIR that can be adapted) (it does Right justify as well)

Right justify a "missing" feature in Flex DataGrid

AS3 Flex/AIR Code where all the above originated ( I needed 0 decimals but Desi convention)

        <s:GridColumn id="dmbtr" headerText="Amt (INR)"     dataField="dmbtr"    >

          <s:itemRenderer>

            <fx:Component>

              <s:GridItemRenderer>

                <s:Label id="MyText" top="9" left="7" alpha="1"

                     text="{bizpopGlobals.instance.leftPad(data.dmbtr,10)}"

                     fontFamily="Lucida Console" textAlign="right"/>

              </s:GridItemRenderer>

            </fx:Component>

          </s:itemRenderer>

        </s:GridColumn>

    public function leftPad(inp:Number,size:int):String {

      var lstr:String=Math.round(inp).toString();

      var pat3:RegExp = /(\d+)(\d{3})$/;

      lstr = lstr.replace(pat3, "$1,$2");

      var pat2:RegExp = /(\d+)(\d{2},.+)/;

      var pat1:RegExp = /\d{3,},.+/;

      while(lstr.match(pat1))

        lstr = lstr.replace(pat2, "$1,$2");

      var strLen:int = lstr.length;

      var padLen:int = size - strLen;

      if (padLen <= 0)

        return lstr;

      var myspaces:String="                    ";  // 20 OK

      return myspaces.substring(0,padLen)+lstr;

   }

__________________________________________________________________________________

To keep Excel download consistent I searched Indian comma separation and found

http://exertia.wordpress.com/2006/04/23/displaying-lakhs-and-crores-in-excel/

The lakhs part which is what is mostly needed and 1 custom format is BOTH +ve and –ve friendly

with NO decimals

[>99999]##\,##\,##0;[<-99999.99]-##\,##\,##0;##,##0

With 2 decimals

[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00

Please use when needed for INR figures in PHPEXCEL2007 and ABAP2XLSX  and DOI

__________________________________________________________________________________