Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Tricky Sorting Post Reply Post New Topic
Author Message
twinspar
Newbie
Newbie
Avatar

Joined: 20 May 2016
Online Status: Offline
Posts: 1
Quote twinspar Replybullet Topic: Tricky Sorting
    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
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.