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
__________________________________________________________________________________
