Group Expert function forcing changed sort throwing totals off

[tdrBates]

Would it be possible to Group By Route and Peak and the attached SQL Query?

When I “group by the Peak field” using Crystal Report Group Expert this forces a change in the sort and throws off the number of “Ontime, Late, Missing_Data”.

My thought was to add the “Group By Clause” within the SQL Command Query” rather than using Crystal Report Group Expert to prevent altering the sort.

I have attached the SQL Command Query, the BRT OTP report, and the BRT OTP by time of day. I am pretty sure the sorting difference is what is throwing the numbers off.

Any suggestions would be appreciated.

I have also pasted the sqlQuery_Command below. How can you Group By Route and Peak in the query below without changing the Order By clause?

SELECT
day_type_vs.description,incident_log.incident_date_time, incident_log.sched_time, incident_log.transit_date_time, incident_log.block_id, incident_log.current_route_id,incident_log.direction_code_id,
incident_types.incident_name, incident_types.incident_desc, incident_log.route_id, incident_log.vehicle_id, incident_log.driver_id, incident_log.deviation,
direction_codes.direction_description, timepoint_name.tp_lname, incident_log.incident_log_id, timepoints.tp_loc_x, timepoints.tp_loc_y,
timepoints.tp_radius, day_type_vs.description, trip_timepoint.seq_num, garage.garage_description, incident_log.tp_id, trip.end_tpid, trip_timepoint.tp_id,
trip.start_time, trip.end_time, trip.trip_id_external,
trip.trip_id,
(incident_log.incident_log_id % 2) as "modulo",

dow = CASE day_type_vs.service_days
WHEN 64 THEN 'Sunday'
WHEN 1 THEN 'Saturday'
WHEN 62 THEN
CASE datepart(dw, incident_log.transit_date_time)
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
END
END,
peak = CASE WHEN (datepart(hh, trip.start_time) + datepart(n, trip.start_time)/60) >= 3.5 AND (datepart(hh, trip.start_time) < 6) THEN 'Early AM'
WHEN datepart(hh, trip.start_time) >= 6 AND datepart(hh, trip.start_time) < 9 THEN 'AM Peak'
WHEN datepart(hh, trip.start_time) >= 9 AND datepart(hh, trip.start_time) < 15 THEN 'Mid Day'
WHEN datepart(hh, trip.start_time) >= 15 AND datepart(hh, trip.start_time) < 18 THEN 'PM Peak'
WHEN datepart(hh, trip.start_time) >= 18 AND datepart(hh, trip.start_time) < 22 THEN 'Evening'
ELSE 'Late Night'
END

FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
INNER JOIN incident_types incident_types ON incident_log.incident_type=incident_types.incident_type
INNER JOIN direction_codes direction_codes ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints ON incident_log.tp_id=timepoints.tp_id
INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version


INNER JOIN trip_timepoint trip_timepoint ON ((incident_log.trip_id=trip_timepoint.trip_id) AND (vehicle_schedule.sched_version=trip_timepoint.sched_version)) AND (incident_log.tp_id=trip_timepoint.tp_id)
INNER JOIN garage garage ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip ON ((vehicle_schedule.sched_version=trip.sched_version) AND (incident_log.trip_id=trip.trip_id)) AND (incident_log.current_route_id=trip.route_id)
INNER JOIN day_type_vs day_type_vs ON (vehicle_schedule.day_type_vs=day_type_vs.id)

WHERE (incident_types.incident_name='BUNCH-E' OR incident_types.incident_name='BUNCH-L' OR incident_types.incident_name='BUNCH-N' OR incident_types.incident_name='EARLY' OR incident_types.incident_name='GAP-E' OR

incident_types.incident_name='GAP-L' OR incident_types.incident_name='GAP-N' OR incident_types.incident_name='LAYOVER' OR incident_types.incident_name='LATE' OR incident_types.incident_name='NORMAL' OR incident_types.incident_name='NORMAL-E' OR

incident_types.incident_name='NORMAL-L' OR incident_types.incident_name='NORMAL-N')
AND (incident_date_time>= {?StartDate} AND incident_log.incident_date_time<={?EndDate})
AND trip.trip_id_external IS NOT NULL
AND (incident_log.current_route_id = 801 OR incident_log.current_route_id = 803)

ORDER BY incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,trip_timepoint.seq_num,incident_log.tp_id,
CASE WHEN (incident_log.incident_log_id % 2) = 0 THEN
trip.start_time ELSE incident_log.incident_date_time
END,
incident_log.incident_date_time;

 

[lbass]

I only see the command attached. What are your group fields and what are the results you are hoping to see? I see you want to group by Route and Peak, but want to maintain some other order. Not sure how the transit datetime fits into this.

It would be helpful if you showed simple mockups of how the report currently appears and how you expect it to appear with labels to show groups and fields/formulas.

You appear to be grouping on strings that will, of course, be ordered alphabetically, unless you use specified order. You could instead group on numbers and then change the name display, if the use of strings is the issue.

-LB

 

[tdrBates]

Crystal Reports by Route and by Route and Time-Period are attached.

The driving force behind the reports is the Order By/Sort that's built into the "SQL Command".

As you can see when I use Group Expert to group by "Peak" this throws the sort out of whack and therefore the numbers
for both reports don't match.

The Report By Route are the correct numbers. The Report By Route and Time-Period are incorrect numbers.

 

[tdrBates]

Did not see the Report by Route attached.

Here it is.

 

[lbass]

Ok, that helps. Next step is to unsuppress the detail sections to see what is contributing to the totals. Your Group 2 is based on transit datetime (widen that field so you can see the time), but your peak formula is based on trip start time. I don't know what the transit datetime means, but I'm guessing that trip start times might appear in more than one transit datetime or vice versa. It might help to explain what the transit datetime is. Next step is to see if this is the issue.

-LB

 

[tdrBates]

Details unsuppressed and transit datetime widen. Reports Attached.

Thanks Ibass for any help with this.

 

[tdrBates]

Details unsuppressed and transit datetime widen. Reports Attached.

Thanks Ibass for any help with this.

 

[lbass]

Your report is difficult to follow with so many formulas and nested formulas. On a side note, I am confused by your use of times without reference to dates. If you format your datetimes to show the dates, it appears that many of the incidents defined as late are actually earlier than the scheduled date.

Your peak group is clearly forcing some records to appear in more than one group (because they meet the criteria of more than one group), thus inflating counts. If you test by inserting some running totals in group footer sections, using distinct counts, you can see that the subgroup totals when added together give a result larger than the distinctcount for the higher order group. This even occurs in your 'correct' report.

I don't think I can add more to this. I suggest you work with a smaller dataset and observe how the groupings are affecting the data. Since I can't actually run the reports here, I can't see the impact of some of those choices, and too, I don't know the logic underlying various fields. I would also check to make sure that the dates associated with the datetime fields are irrelevant--it would surprise me if they were. Sorry I can't be more helpful.

-LB

 

[tdrBates]

LB

Thanks For looking into this!

-tdr