Tricky Sorting
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=23025
Printed Date: 30 Apr 2024 at 12:28pm
Topic: Tricky Sorting
Posted By: twinspar
Subject: Tricky Sorting
Date Posted: 10 Nov 2022 at 11:32am
Hello all:
I am working on a report that returns required vehicle service information. The data source is an SQL Stored Procedure that I cannot modify.
Some services are due by dates, while others are due by an odometer reading. Vehicles can have either or both in their service profile. From the source data, a record describing a meter-based service has a fictitious service due date of 1/1/1900.
I need to group records by due date to list soonest due (or overdue) at the top of the report, yet keep all vehicle records together, so the same vehicle doesn't show up in multiple places throughout the report.
Is there a way to accomplish this through creative grouping or perhaps creating a local variable that, for the purpose of sorting, assigns a legitimate due date (to replace the 1/1/1900) from the earliest of this vehicle's date-based records?
Here's some sample data (apologize for the column shift). Notice transposed dates in required sort.
SOURCE DATA
VehNo SvcCode Type DateDue MeterDue
123 A Date 11/10/2022 0
123 B Date 2/15/2023 0
123 A Meter 1/1/1900 26,750
456 A Date 11/5/2022 0
456 B Date 4/18/2023 0
456 B Meter 1/1/1900 83,256
REQUIRED SORT
VehNo SvcCode Type DateDue MeterDue
456 A Date 11/5/2022 0
456 B Meter 11/5/2022 83,256
456 B Date 4/18/2023 0
123 A Date 11/10/2022 0
123 A Meter 11/10/2022 26,750
123 B Date 2/15/2023 0
|
Replies:
Posted By: kevlray
Date Posted: 14 Nov 2022 at 4:00am
Using a formula. One can get very creative. Unfortunately I do not have the time currently to look into this one. Hopefully someone else can contribute.
|
|