SQL functions in SRS

• paucann

  SSC Eights!

  Points: 912

  More actions

  January 28, 2009 at 11:32 am

  #200532

  Hi,

  I have a decimal field (8,5) that I want to clip to 2 decimal places.

  Here's the snippet of SQL - CONVERT(money, r_TicketDetailView.ActualRate) AS Rate

  This works fine from SQL Management Studio but, when I slot this into my SQL on my SRS report, it's ignored and prints 5 decimal places. Does SRS use regular sql functions like CONVERT? If not, how can I get around this? Also, the field can't be altered to (8,2) in the table so that is not an option.

  thanks,

  Paul

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  January 28, 2009 at 11:38 am

  #934046

  You can make it stored procedure then SSRS must execute it because control is passed to the relational engine. But SSRS still rejects stored procedures with many local temp tables based on execution duration.

  Kind regards,
  Gift Peddie

• TKD-BB

  SSC Eights!

  Points: 993

  More actions

  January 28, 2009 at 12:11 pm

  #934076

  If you are only concerned about how the data is displayed, and are not looking to only use the two decimal place value for calculations, then I would suggest simply changing the Format on the field where you display the data. Depending upon how you want it to look you could use the following Format codes

  N2

  D2

  C

  Which would display as

  1,234.56

  1234.56

  $1234.56

  Hope that helps.

• paucann

  SSC Eights!

  Points: 912

  More actions

  January 28, 2009 at 1:12 pm

  #934111

  Thanks,

  No wonder this site has just broken the million mark. Every problem I've had so far has been answered correctly and promptly. Maybe they're not difficult questions for you but I thank you anyway!

  Once again, thanks for your help!

  Paul