locked
Report builder custom currency format with different symbol RRS feed

  • Question

  • Is it possible to change the currency symbol on a report based on a condition?  We do quotes for South African and Namibian clients.  For South Africa the currency symbol is 'R' and for Namibia it is 'N$'.  The exchange rate is 1:1 so no calculations are needed.  I just need to display the correct currency symbol.

    For example: if 'Adress1 Country' = 'South Africa' display R 100 000.00 
         if 'Adress1 Country' = 'Namibia' display N$ 100 000.00

    I need to keep the currency format.  I.e. the spaces between thousands and always display two decimals.

    I noticed that there is an expression editor for custom format in the Report Builder, but I cannot figure out how to use it.


    Christo Vermeulen


    Tuesday, November 18, 2014 1:37 PM

Answers

  • Use something like this:

    =Iif(Fields!address1_country.Value = "South Africa", "R " + CStr(Format(Fields!revenue.Value, "#,###,###,###.00")),
    IIF(Fields!address1_country.Value = "Namibia", "N$ "+ CStr(Format(Fields!revenue.Value, "#,###,###,###.00")), Format(Fields!revenue.Value, "#,###,###,###.00") ))


    Regards, Saad


    • Edited by Mohd Saad Wednesday, November 19, 2014 9:28 AM
    • Marked as answer by Christo Vermeulen Wednesday, November 19, 2014 9:31 AM
    Wednesday, November 19, 2014 9:27 AM
  • hi Christo,

    you can use format function to format the number, convert to string using cstr and then appended the currency field.

    ideally currency field in conjunction with regional settings will format the string(if the data is flagged as currency field.).

    http://msdn.microsoft.com/en-us/library/ms252080(VS.80).aspx

    regards

    Jithesh

    Wednesday, November 19, 2014 9:21 AM

All replies

  • write expression like this:

    =Iif(Fields!address1_country.Value = "South Africa", "R " + CStr(Fields!revenue.Value),
    IIF(Fields!address1_country.Value = "Namibia", "N$ "+ CStr(Fields!revenue.Value), Fields!revenue.Value ))


    Regards, Saad

    Wednesday, November 19, 2014 6:33 AM
  • Hi Saad

    Thank you for the reply.

    My problem with the expression is that I need to keep the money format.  I.e. the space between thousands and always two decimal places.  I apologize for not making it clear in my question.  Will edit the question. 


    Christo Vermeulen

    Wednesday, November 19, 2014 8:55 AM
  • hi Christo,

    you can use format function to format the number, convert to string using cstr and then appended the currency field.

    ideally currency field in conjunction with regional settings will format the string(if the data is flagged as currency field.).

    http://msdn.microsoft.com/en-us/library/ms252080(VS.80).aspx

    regards

    Jithesh

    Wednesday, November 19, 2014 9:21 AM
  • Use something like this:

    =Iif(Fields!address1_country.Value = "South Africa", "R " + CStr(Format(Fields!revenue.Value, "#,###,###,###.00")),
    IIF(Fields!address1_country.Value = "Namibia", "N$ "+ CStr(Format(Fields!revenue.Value, "#,###,###,###.00")), Format(Fields!revenue.Value, "#,###,###,###.00") ))


    Regards, Saad


    • Edited by Mohd Saad Wednesday, November 19, 2014 9:28 AM
    • Marked as answer by Christo Vermeulen Wednesday, November 19, 2014 9:31 AM
    Wednesday, November 19, 2014 9:27 AM