0

I have an SSRS report which contains various currency entries in the form of €3340.89 stored as a string in the DB.

How can I add a line at the end of the report with the sum totals of that column?

sd_dracula
  • 3,796
  • 28
  • 87
  • 158

1 Answers1

1

In order to sum them you will need to either:

  • Cast them as Decimal/Money in the SQL statement.

    select CAST(REPLACE('€3340.89', '€','') as DECIMAL(10,4))

  • Use expressions to convert them in the report, then sum the expressions.

My preference would be the former as it's easier to maintain.

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
  • So I have tried it in the expression of the column, `=FormatNumber(Fields!CostValue.Value)` and it displays it correctly in the table (ie: €1000 is 1,000.00) but when I add a cell with the total expression as `=Sum(Fields!CostValue.Value)` is just displays #Error – sd_dracula Mar 27 '14 at 14:06
  • =Sum(FormatNumber(Fields!CostValue.Value)) – Eric Hauenstein Mar 27 '14 at 14:08
  • Though if you have any bad data in the field, you will still get an error. – Eric Hauenstein Mar 27 '14 at 14:14
  • Yes I still get the error even with the correction above. I have tried it with a query that returns just one result, and that is displayed as 1,000.00 which is fine. I will check the values for anything odd and try it via SQL also – sd_dracula Mar 27 '14 at 14:22
  • Hmm when I try to convert in the SQL I get this: Error converting data type varchar to numeric. I use this: `CAST(REPLACE([CostValue], '€','') as DECIMAL(10,2))` – sd_dracula Mar 27 '14 at 14:28
  • Try SELECT ISNUMERIC([costvalue]) and see which entries are the troublesome ones. – Eric Hauenstein Mar 27 '14 at 14:30
  • The only thing to be aware of with Money is that it can introducing rounding errors if you perform arithmetic on it. http://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server – Eric Hauenstein Mar 27 '14 at 14:51