dynamic currency symbol

Question

in ssrs, i want to define the currency symbased based on a code in the dataset.
ie sometimes the amount is dollar, euro, etc. a flag would indicate which currency symbol to use.
any idea how to implement this?

Answer 1

i finally gave up using the custom format of the textbox, and reset it to general with no formatting. in the expression I manipulated the data to be:
fields!symbol.value & formatnumber(fields!cost.value,0)
and is is displaying correctly

Answer 2

Hi @david berlinger ,

It would be great if you could provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic.

(3) Desired output based on the sample data in #1 above.

Regards，

Zoe

Answer 3

create table ref (key tinyint identity(1,1),desc varchar(20), symbol nchar(3),exchange_rate decimal(9.2))
insert into ref (desc,symbol)
values ('dollar','$',0.33).('euro','€', 0.5),('nis','₪',1.00)

create table data(item varchar(50),cost money,currency tinyint)
insert into data(item,cost,currency)
values ('hotel',1046.78,1),('plane fare',862.21,2),('car rental',2450.00,2),
('lecturer',10,900,3),('discount',-100,1)

i want in SSRS to display the cost with the appropriate currency sigh and formatted with commas (1,046 rather than 1046), negative numbers in red and a specified format

the cost column should be able to be totaled with the appropriate monetary symbol as I want a parameter "currency display "to allow the user to chose the currency to be displayed (from table ref). I would write code to convert all currencies to the chosen currency.
the quest is how to format the text box to change the currency symbol based on the parameter chosen or the data in the "data" table

Answer 4

Hi @david berlinger ,

With the data you provided, I did some local test. Hope it could give you some ideas.

1.Add the dataset to the report with you query.

select *, cost/exchange_rate as newcost  from data  
inner join ref  
on ref.[key]=data.currency  

2.Set the property of the text box with currency-custom.

3.Set the color font of the text with

=IIF(InStr(Fields!newcost.Value,"-"),"red","Black")  

The report will be shown like this:

If I misunderstand your meaning, please incorrect me.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.