CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
Hassan Mustafa
Hassan Mustafa

Reputation: 11

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

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

Upvotes: 0

Views: 424

Answers (3)

Mike Petri
Mike Petri

Reputation: 605

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

Upvotes: 0

Hassan Mustafa
Hassan Mustafa

Reputation: 11

![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

Upvotes: 0

Apurv Gupta
Apurv Gupta

Reputation: 860

use

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

in your expressions

Upvotes: 0

Related Questions