how to sum time column and avoid null fields in ssrs 2008

0
votes

I have column of time values and i want to sum these values but when one field is null an error is shown #error ,, i used iif (isnothing(attTime),0, attTime) condition but this integer error then i used 00:00 but it appears 12 hour and sum 12

this code that i used

= Right("0" & Sum(CInt(Left(CDate(Fields!timeAtt.Value.ToString()).ToString("hh:mm"),2)), "DataSet1") + Floor(Sum(CInt(Right(CDate(Fields!timeAtt.Value.ToString()).ToString("hh:mm"),2)), "DataSet1") / 60),2) & ":" & Sum(CInt(Right(CDate(Fields!timeAtt.Value.ToString()).ToString("hh:mm"),2)), "DataSet1") Mod 60

so please i want to sum the fields and avoid the null fields

3
sql-serverreporting-servicesssrs-2008
DO you have control over your database query ? If yes then post your query ? And it is also not clear what you are trying to do by your expression to complicated. Can you explain with example . May be there simple solution what you are trying to achieve. – Mahesh
"select hoursAtt from attendanceTable" so i want sum hours as total but but null fields prevent me – Hassan Mustafa
Can't you change your query to select hoursAtt from attendanceTable where hoursAtt is not NULL ? – Apurv Gupta
No.. i want to show the empty field – Hassan Mustafa

3 Answers

0
votes

use

iif (isnothing(attTime),"00:00", CDate(Fields!timeAtt.Value.ToString()).ToString("hh:mm"))

in your expressions

0
votes

![TimeAtt columns][1]

<table>
<tr>
  <td>
    
  </td> 
  <td>
    TimeAtt
  </td> </tr>
  <tr> <td>
    
  </td> <td>
    10:50
  </td> </tr>
 <tr> <td>
    
  </td>  <td>
   11:00
  </td> </tr>
  <tr><td>
    
  </td><td>
    12:12
  </td> </tr>
 <tr> <td>
    
  </td>  <td>
    Null
  </td> </tr>
  <tr><td>total</td><td>#Error</td>
  </tr>
</table>

and this is the query = Right("0" & Sum(CInt(Left(iif (isnothing(Fields!timeAtt.Value.ToString()),"00:00", CDate(Fields!timeAtt.Value.ToString()).ToString("hh:mm")),2)), "DataSet1") + Floor(Sum(CInt(Right(CDate(iif (isnothing(Fields!timeAtt.Value.ToString()),"00:00", CDate(Fields!timeAtt.Value.ToString()).ToString("hh:mm"))),2)), "DataSet1") / 60),2) & ":" & Sum(CInt(Right(CDate(iif (isnothing(Fields!timeAtt.Value.ToString()),"00:00", CDate(Fields!timeAtt.Value.ToString()).ToString("hh:mm"))),2)), "DataSet1") Mod 60

0
votes

It seems this might be simpler if you handle the underlying query. Will this work?

SELECT ISNULL(hoursAtt,'00:00:00') AS hoursAtt FROM attendanceTable