none
How to dynamically show the currency symbol

    Question

  • HI,

    I am new to reporting services and I am trying to figure out how to programatically set the currency symbol based on the currency of an Invoice. In Crystal I have the following to get the symbol:

    Select  {JIH_AR_INVOICE_DETAIL_TL_V.CURRENCY}
    Case 'USD': CHR(0036)
    Case 'EUR': CHR(0128)
    Case 'GBP': CHR(0163)
    Case 'YEN': CHR(0165)

     

    Ideally what Id like to have is to concatenate this with the price. My report fiedsl are as follows:

    Fields!SYS_CURRENCY_CODE.Value

    Fields

    !PURCHASE_PRICE.Value

     

    Any suggestions would be appreciated.

     

    Thanks,

    Trish

     


    Trish Leppa
    Friday, March 04, 2011 4:56 PM

Answers

  • Hi Trish,

    As Olaf suggested, the advantage of creating a currency symbol table in the database is we can dynamically add new currencies without changing the reports.

    However, if you still want to concatenate currency symbol with the price in the report level, please refer to the following expression

    =Switch(
    Fields!SYS_CURRENCY_CODE.Value=”USD”, CHR(0036),
    Fields!SYS_CURRENCY_CODE.Value=”EUR”, CHR(0128),
    Fields!SYS_CURRENCY_CODE.Value=”GBP”, CHR(0163),
    Fields!SYS_CURRENCY_CODE.Value=”YEN”, CHR(0165),
    ) & Fields!PURCHASE_PRICE.Value

     

    Thanks,
    Tony Chain


    Tony Chain [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, March 10, 2011 3:21 AM

All replies

  • Hello Trish,

    I would create a table for currency like [Sales].[Currency] in AdventureWorks, additional with currency symbol column.

    Then you can join the table and show the symbol; additional advantage is you can dynamically add new currencies without changing the reports.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Friday, March 04, 2011 5:05 PM
  • Hi Trish,

    As Olaf suggested, the advantage of creating a currency symbol table in the database is we can dynamically add new currencies without changing the reports.

    However, if you still want to concatenate currency symbol with the price in the report level, please refer to the following expression

    =Switch(
    Fields!SYS_CURRENCY_CODE.Value=”USD”, CHR(0036),
    Fields!SYS_CURRENCY_CODE.Value=”EUR”, CHR(0128),
    Fields!SYS_CURRENCY_CODE.Value=”GBP”, CHR(0163),
    Fields!SYS_CURRENCY_CODE.Value=”YEN”, CHR(0165),
    ) & Fields!PURCHASE_PRICE.Value

     

    Thanks,
    Tony Chain


    Tony Chain [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, March 10, 2011 3:21 AM
  • Hi,

     

    We have a limited number of currencies so this worked perfect! Thanks so much!

     

    Just a note for anyone else wanting to use the above, you will need to remove that last comma after the (0165)

     

    Thanks,

    Trish


    Trish Leppa
    Wednesday, March 23, 2011 8:28 PM