1

How do I write the follow SSRS Calculation below in SSRS expression

Actual Calculation example below =((deldate+deltime) –(orddate+ordtime))*24

deldate     delTime  orddate    ordTime  *24
13/01/2015  14:25   14/01/2015  13:55    23.50
20/01/2015  12:00   20/01/2015  17:02    5.03

This worked in SQL Toad but not in SSRS

(TO_DATE(TO_CHAR(A.DELDATE,'DD/MM/YYYY') ||' '|| TO_CHAR(A.DELTIME,'HH24:MI'),'DD/MM/YYYY HH24:MI') - TO_DATE(TO_CHAR(A.ORDDATE,'DD/MM/YYYY') ||' '|| TO_CHAR(A.ORDTIME,'HH24:MI'),'DD/MM/YYYY HH24:MI'))*24 AS "DiffinHours",
Nite Cat
  • 533
  • 2
  • 8
  • 12

1 Answers1

2

Change TO_CHARs to Format, TO_DATEs to CDate, and use a DateDiff instead of subtracting. To get hours in decimals, find the difference in minutes and divide by 60.0:

=DateDiff(DateInterval.Minute, 
          CDate(Format(Fields!deldate.Value, "MM/dd/yyyy") & " " & Format(Fields!deltime.Value, "HH:mm")), 
          CDate(Format(Fields!orddate.Value, "MM/dd/yyyy") & " " & Format(Fields!ordtime.Value, "HH:mm"))
         ) / 60.0
stubaker
  • 1,928
  • 1
  • 23
  • 25
  • I'm getting a run time error in expression - conversion from string "29/01/2015 14:00" to type 'Date' is not valid. – Nite Cat May 08 '15 at 20:13
  • I took a guess based on the above date formatting that your system/report settings needed to be in dd/mm/yy format. If these settings are actually en-US, you'll need mm/dd/yy; updated my answer. – stubaker May 08 '15 at 20:33
  • same problem - [rsRuntimeErrorInExpression] contains an error: Conversion from string "00/03/2015 21:45" to type 'Date' is not valid. – Nite Cat May 08 '15 at 20:42
  • You'll want to take a look at the data from your source query there; I'm not sure how a field of type date could be created without a month. – stubaker May 08 '15 at 20:47
  • Ok - this seems to be partially working however, why do I get #error on some results and some weird number format if over 2 or 4 days? – Nite Cat May 09 '15 at 21:54
  • Again, I'd double check the raw data from your source query specifically the data types. All the expression does is append the date and time parts of two `DATETIME` fields. You could also try adding extra columns to specifically check that the output of each `CDate` is what you expect. – stubaker May 12 '15 at 15:35
  • This is ok now - Just had to change the date format to 'dd/mmm/yyyy' Thanks for your help stubaker. – Nite Cat May 12 '15 at 18:53
  • Hi stubake- I have another question how do I add an IFF conditional format to the above i.e. IFF greater than 48 flag "Red" else "white" – Nite Cat May 13 '15 at 13:11
  • If you're looking to change the text color, go to Text Box Properties > Font > and click Fx next to Color to set an expression like `=IIF(Fields!myfield.Value > 48, "Red", "White")` – stubaker May 13 '15 at 18:04
  • It doesn't like Fields!myfield.value not sure why – Nite Cat May 14 '15 at 10:39
  • You would need to replace `myfield` with your field name. Or you could also use `Me.Value`: `=IIF(Me.Value > 48, "Red", "White")` – stubaker May 14 '15 at 17:13
  • This does not work with numeric expression values in SSRS 2008 – Nite Cat May 15 '15 at 14:11
  • Another option is `=ReportItems!textbox1.Value` where textbox1 is the name of your textbox – stubaker May 15 '15 at 17:59