Format Currency in Table Header

Question

Format Currency in Table Header

2Tall

SSChampion

Points: 14644
More actions
April 28, 2008 at 3:15 am

#187061

Hi. I have been asked to add a field to an existing report. The field contains a currency value. I created a new Dataset then added the required field to the Report (Table Header). I have tried various options to get the output to format to UK Currency Symbol '£'.
Below is an extract of the Report Expression(s) + SQL Query(s) variations that I have tried (only included partial select statement as this part works)
Report Expression:
Option 1:
=Sum(Fields!FreightValue.Value, "ShippingTerm")
Report Expression Option 2:
="Customs Value: " & First(Fields!FreightValue.Value, "ShippingTerm")
Option 2 above returns: Customs Value: £0.00??
SQL
SQL Query Option 1:
SELECT Shipments.FreightValue
Report Output: 100000.0000
SQL Query Option 2:
SELECT Shipments.FreightValue
(Format Report Property) Format: C0
Output: $10,000
SQL Query Option 3:
SELECT '£' + CONVERT (varchar(12),Shipments.FreightValue,1)AS FreightValue
Report Output: #Error
The sql for option 3 runs OK and returns £10,000.00 in Management Studio + when I execute the DataSet in Visual Studion.
Any ides?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

JohnG-529958 Old Hand Points: 366 More actions · Answer 1

Hi Phil,

Have you tried using your Query Option 2 and selecting the cell of the table holding the field then going to properties and changing the Language from Default to English (UK)? For some reason all reports I create default to US and I need to explicitly specify the language of all date and currency fields to get them to display correctly. I believe it's to do with the language SQL was installed with.

Sorry if this is obvious and not the solution - I'm still very new at this but your description sounds much like an issue I get a lot. Though if this is the solution I'd have expected someone else to have picked this up already. The fact that your Query Option 2 is returning the dollar value does look like the table is picking up the wrong language settings.

Hope this helps,

John

2Tall SSChampion Points: 14644 More actions · Answer 2

Hi John. Another newbie like me!! It appears that this requirement has gone away so problem solved!! 🙂

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

barkha.singh SSC-Addicted Points: 417 More actions · Answer 3

Can also try setting report Language property to =User!Language

Barkha.

JohnG-529958 Old Hand Points: 366 More actions · Answer 4

Thanks Barkha,

I'd recently noticed that if I opened the rdl file in notepad there was a setting at the bottom of

for all my reports.

I'd tried changing this to

or variants of

but it just complained when loaded into the editor.

Having followed your suggestion and set the report properties to English (United Kingdom) I now find this setting in the rdl file is

Why didn't I think of this - of course it makes complete sense that if MS are using UK and United Kingdom in the editor they will be storing this as GB in the rdl files! I give up...

Thanks for your response - that'll save me a fair bit of location setting as I have been setting language per cell whenever I have a cell with currency in it.

Cheers,

John

roger.suddaby Newbie Points: 7 More actions · Answer 5

Thanks very much! I struggled for ages with the FormatCurrency function in SRS2008 and no matter what I did, it totally ignored the local settings on the machine (UK or US). It would appear that if you don't make the report language =User!Language then everything else is just totally ignored! :Whistling:

Why I love Microsoft so much. Still trying to convince myself that Reporting Services are easier and more flexible than Crystal Reports - but so far not convinced...

Thanks again.