Multiple Deatils sections to Group Section

[GSG]

Hello All,

I am trying to create a single group from the detail section by using "IF" function to get a higher time value in the Group section. Here is the problem screenshot:

I used "IF" as followings:

if {Command.ACTIVITY} =0 then minimum({Command.FIRSTPICKUPTIME}, {command.EVSTRNAME})

If {Command.ACTIVITY} =0 then ({Command.FIRSTPICKUPTIME})

Minimum ({Command.FIRSTPICKUPTIME}, {command.EVSTRNAME})

And for the drop off I used the same approach:

if {Command.ACTIVITY} =1 then minimum({Command.lastdropofftime}, {command.EVSTRNAME})

If {Command.ACTIVITY} =0 then ({Command.lastdropofftime})

Minimum ({Command.Command.lastdropofftime}, {command.EVSTRNAME})

Nothing seems to be working. Please advise I would appreciate the help. Thank you.

[GSG]

Thank you Dell for the reply, much appreciated. I tried to use sort by time but seems like nothing is working. Here is the command (SQL query) that I use to create CR:

Select s.ldate, es.evstrname, es.tripcount, e.activity, Case when e.activity =0 then (case when min(e.actualarrivetime) <min(e.schearly) then min(e.schearly) else Min(e.actualarrivetime) end) when e.activity=1 then (case when max(e.actualarrivetime)<>1 then max(e.eta)else Max(e.actualarrivetime) end) when e.activity =8 then (sum(e.dwelltime)) end as time from events e, eventstrings es, schedules s where es.schid=s.schid and e.schid=s.schid and es.evstrid=e.evstrid and s.ldate=20200407 and activity in (0,1,8) and es.providerid=2 and es.tripcount >0 group by s.ldate, es.evstrname, e.activity, es.tripcount

When I use sort by the time, last dropoff which is based on activity 1 shows zero values and when I remove the time from the sort order, first pick up shows 0 values whereas the last drop off shows time.

I am so confused about it. I want to have time in the first pickup, last drop off and out of service time. Please advise. Thanks

[DellSC]

Is your report already grouped? Do you need to group by first pickup and last drop-off or just sort by them?

I would do this a different way. Assuming that you're already grouping by the field in the Run column, instead of trying to group on these fields, I would add a sort ascending by first pickup and a second sort under that on last drop-off.

If that doesn't give you what you're looking for, please provide an explanation (not formulas) for how you want the sort to work.

-Dell