CODEWITHSUNDEEP
sqlssrs-2012reportbuilder
whitz11
whitz11

Reputation: 229

summing a duration field

I have a report looking at the number of working days lost through sickness. When trying to create a line graph in report builder no data pulls through on the chart. I am trying to sum the duration. The duration being the amount of time someone has been off sick. I can put this into excel and it works fine.

    cast(case when ContractType = 'Parttime' then 0.5 when ContractType = 'Fulltime' then round(DATEDIFF(hour,[Begin],[End]) / 24.00,0) End as varchar)  as Duration

enter image description here

enter image description here

The field returns the following -- eg 0.500000 being a half day

 0.500000
 1.000000
 4.000000

Now I'm aware you can't sum a varchar, have tried casting as numeric and still get the same result. When I cast to int, the 0.500000 turns to 1 which I don't want. Incidentally even when its cast as int I still get nothing showing on the chart.

Upvotes: 0

Views: 36

Answers (1)

whitz11
whitz11

Reputation: 229

Ended up summing the duration field in my query and casting to float. Which now works in reporting services. Still not entirely sure why it wouldn't work the other way but at least got it working.

Upvotes: 1

Related Questions