Customized Thousand Separator in Crystal Report

Crystal Report Provides an easy to use default thousand separator for numeric Fields. You can use it by checking Thousand Separator from Field Properties.

However, when you want your own kind of thousand separators you have to go through more complications. You will have to use Formula Fields of Crystal Report.

Using Formula Editor, convert your Numeric Field to text; i.e. You can use this Formula


ToText ({YourReportField}, "##,##,##,##0.00", 2)

You can get Report Summation as well in your own Format using the following Formula.


ToText (sum({YourReportField}), "##,##,##,##0.00", 2)

In case of group summation, you need to use this formula.


ToText (Sum ({YourReportField},{YourGroupByField}), "##,##,##,##0.00", 2)

After creating those Formula Fields just drag and drop those fields accordingly in report.

Thanks
A Rahim Khan

About these ads
    • Manojkumar
    • May 4th, 2011

    this formula display with dollar symbol i want rupess symbol
    please tell how

    • Right Click on Report Field Object and then click on Format Object, Customize Number Format, Click on Currency Symbol, Put your own kind of Currency Symbol. Hope this will work.

        • Crystal report currency filed
        • November 3rd, 2011

        Bro its Text field not Number field to format currency.

        Please do help me..
        I am also facing same problem.

    • gaya
    • October 8th, 2011

    how will i set indian currency format.. please help

    • create a formula field, use this formula

      ToText ({YourReportField}, “##,##,##,##0.00″, 2)

      replace “YourReportField” with your desired data field. drag this formula field in to report.

        • Kaustubh
        • November 7th, 2012

        Thanx a lot….! It works absolutely Correct……!!!!

    • gaya
    • October 10th, 2011

    hi,
    i cant save that formula… there showing error… please explain

    • What is the version of crystal report ? this should work. share me screenshots. my email khan.rahim at gmail.com. i will try to sort out.

    • Rani
    • October 10th, 2011

    my field name is SumofiRate2.. that field showing the result is 1,029,950.00….how can i format this field into indian currency format… please help

    • Rani
    • October 11th, 2011

    thanks for your help.. i did that

    • Rani
    • October 13th, 2011

    Hi,
    how to get data from xml file using crystal reports VS2010?
    Please explain

    • ketan
    • January 31st, 2012

    StringVar Amt;
    StringVar s;
    StringVar digit;
    NumberVar N;
    Amt:=Replace(Totext(9999.00),”,”,””);
    digit:=right(Amt,3);
    s:=left(Amt,len(Amt)-3);
    N:=len(s);
    Select N
    case 1:
    s & digit
    case 2:
    s & digit
    case 3:
    s & digit
    case 4:
    left(s,1)&”,”& right(s,3) & digit
    case 5:
    left(s,2)&”,”& right(s,3)& digit
    case 6:
    left(s,1)&”,”& mid(s,2,2) & “,” & right(s,3)& digit
    case 7:
    left(s,2)&”,”& mid(s,3,2) & “,” & right(s,3)& digit
    case 8:
    left(s,1)&”,”& mid(s,2,2) & “,” & mid(s,4,2) & “,” & right(s,3)& digit
    case 9:
    left(s,2)&”,”& mid(s,3,2) & “,” & mid(s,5,2) & “,” & right(s,3)& digit
    case 10:
    left(s,1)&”,”& mid(s,3,2) & “,” & mid(s,6,2) & “,” & mid(s,9,2) & “,” & right(s,3)& digit
    case 11:
    left(s,2)&”,”& mid(s,3,2) & “,” & mid(s,5,2) & “,” & mid(s,7,2) & “,” & right(s,3)& digit
    default:
    s;

    • ketan
    • January 31st, 2012

    numbervar RmVal:=0;
    numbervar Amt:=0;
    numbervar pAmt:=0;
    stringvar InWords :=”Indian Rupees”;

    Amt :={#RTotal0};

    if Amt > 1000000 then RmVal := truncate(Amt/1000000);
    if Amt = 1000000 then RmVal := 1;

    //if RmVal = 1 then
    ////InWords := InWords + ” ” + towords(RmVal,0) + “Million”
    ////else
    if RmVal >= 1 then InWords := InWords + ” ” + towords(RmVal,0) + ” million”;

    Amt := Amt – Rmval * 1000000;

    if Amt > 100000 then RmVal := truncate(Amt/100000);
    if Amt = 100000 then RmVal := 1;

    if RmVal = 1 then
    InWords := InWords + ” ” + towords(RmVal,0) + ” hundred” ;
    //Else
    //If RmVal > 1 then InWords := InWords + ” ” + ToWords(RmVal,0) + ” Lakhs”;
    //
    //Amt := Amt – Rmval * 100000;

    if Amt > 0 then InWords := InWords + ” ” + towords(truncate(Amt),0) ;

    pAmt := (Amt – truncate(Amt)) * 100;

    if pAmt > 0 then
    InWords := InWords + ” and ” + towords(pAmt,0) + ” paise only”
    else
    InWords := InWords + ” only”;
    PROPERCASE (inwords)

    • ketan
    • January 31st, 2012

    numbervar RmVal:=0;
    numbervar Amt:=0;
    numbervar pAmt:=0;
    stringvar InWords :=””;

    Amt :={View_POP_POOrder.POTotalAmount};

    if Amt > 10000000 then RmVal := truncate(Amt/10000000);
    if Amt = 10000000 then RmVal := 1;

    if RmVal = 1 then
    InWords := InWords + ” ” + towords(RmVal,0) + ” crore”
    else
    if RmVal > 1 then InWords := InWords + ” ” + towords(RmVal,0) + ” crores”;

    Amt := Amt – Rmval * 10000000;

    if Amt > 100000 then RmVal := truncate(Amt/100000);
    if Amt = 100000 then RmVal := 1;

    if RmVal = 1 then
    InWords := InWords + ” ” + towords(RmVal,0) + ” lakh”
    Else
    If RmVal > 1 then InWords := InWords + ” ” + ToWords(RmVal,0) + ” Lakhs”;

    Amt := Amt – Rmval * 100000;

    if Amt > 0 then InWords := InWords + ” ” + towords(truncate(Amt),0) ;

    pAmt := (Amt – truncate(Amt)) * 100;

    if pAmt > 0 then
    InWords := InWords + ” and ” + towords(pAmt,0) + ” paise only”
    else
    InWords := InWords + ” only”;
    “INR” + PROPERCASE (inwords)

  1. SSSSSUUUUUUUPPPPPEEEEERRRR It really works.

    • Aparna
    • August 27th, 2013

    can anyone give a solution for the same(indian number system format) but in rdlc… am stuck..
    please help..

    • Pathak Trilok M
    • October 22nd, 2013

    How TO insert latest rs symbol in the crystal report ??? what to write to type that symbol in place of the dollar sign ????

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 73 other followers

%d bloggers like this: