How to report on non-business hours only?

[vhagerty]

I am using CR9 with Track-It 6.5 and I need to produce a report that shows certain call types by certain technicians for all non business hours (m-f 5pm-8am and all day sat and sun)

Can anyone help?

Thanks!
Ginny

 

[synapsevampire]

In the report-edit selection formula->record place:

(
dayofweek({table.datetime} in 7 to 1
)
or
(
dayofweek({table.datetime} in 2 to 6
and
(
hour({table.datetimefield}) in 17 to 24
or
hour({table.datetimefield}) in 0 to 8
)
)

-k

 

[vhagerty]

This seemed to work wonderfully! However... I need to put in the utctolocal ({tasks.opendate}) in there somewhere, and being the newbee I am to this I just cant figure that out.

Can you help there too?

Ginny =)

 

[synapsevampire]

I don't use any UFL's, however just replace your {table.datetime} with the functions' use, as in

(
dayofweek(utctolocal({tasks.opendate})) in 7 to 1
)
or
(
dayofweek(utctolocal ({tasks.opendate}) in 2 to 6
and
(
hour(utctolocal ({tasks.opendate})) in 17 to 24
or
hour(utctolocal ({tasks.opendate})) in 0 to 8
)
)

-k

 

[vhagerty]

That didnt seem to work. The closest I have come is by grouping by month the {tasks.opendate} using (@gmt to local) in the details then filter by technicial and work order type. Using the (@gmt to local) gets me the times in local time.

When I use your original suggesion I opened the selection expert and put it as a formula under {tasks.opendate}. It's not seeming to do any of the filters. Maybe I've confused it?

Below is everything that shows up in the selection expert. I dont know if that will help or not.

{TASKS.TYPE} = "Problem Call" and
{TASKS.OPENBY} in ["ABERTRAM", "ASAMPSON", "BCOY", "EMORAN", "GENERIC", "HELPDESK", "JRAMIREZ", "KTALIAFE", "MMACGILL", "RPAYNE", "VHAGERTY", "VOLSON"] and
(dayofweek({tasks.opendate}) in 7 to 1)
or (dayofweek({tasks.opendate}) in 2 to 6
and (hour({tasks.opendate}) in 17 to 24
or hour({tasks.opendate}) in 0 to 8))

Thanks!
Ginny =)

 

[vhagerty]

Can anyone else help me with this? I need to show all calls M-F after 5pm and before 8am, and all calls on Sat and Sun. I need to factor in the utctolocal so my calls show in my local time. What about and If/Then type thing? I'm grasping at straws here.

Ginny