Crystal Reports - Conditional record sorting

[Former Member]

I'm attempting to sort report records based on multiple fields, but not as simple as Field A - ascending, Field B - descending, etc. My records have fields "machine", "fault", "magnitude", and "date". Machines are given a fault at a certain magnitude on a certain date. I want to sort based first on "magnitude" (descending) and then "machine"' (ascending), "fault" (ascending), and then "date" (ascending). However, the kicker is that a "machine" may have more than one "fault" and possibly assigned a different "magnitude". I want all "faults" assigned to the same "machine" to be sorted one after the other, even if the "faults" are different "magnitudes"

For example, Machine A has a fault assigned Significant. Machine B has two faults: Severe and Insignificant. Machine C has a fault assigned Severe. Machine D has a fault assigned Insignificant. Machine E has two faults both assigned Significant.

The correct order would be:

Machine B - severe

Machine B - insignificant

Machine C - severe

Machine A - significant

Machine E - significant 1

Machine E - significant 2

Machine D - insignificant

Essentially, I want the records to be supported based on "magnitude" but with "faults" regardless of "magnitudes" for identical "machines" to be kept together.

[abhilash_kumar]

Hi,

Is there is a reason why Machine B appears before Machine C despite both having 'severe' faults?

Are these fault values static? Is there a certain fault that should always take precedence over others?

-Abhilash

[Former Member]

*"Essentially, I want the records to be reported based on..."

Machine B is before C because Machines will be in ascending order (after Magnitude descending order) and both Machines have a Severe fault (highest Magnitude value). All fields are static. Severe Magnitude takes precedence. There are four Magnitude values and are based on 0-10. 7-10 is severe. 4-7 is significant. 1-3 is insignificant. < 1 is normal. Machine, Fault, and Date have infinite values, for arguments sake.

The key is that Machine Faults, regardless of Magnitude remain grouped together (this is so my customer does not have to search through the report to find an additional problem on the same machine). But Magnitude is the primary sorting field i.e. if every Machine only had one Fault/Magnitude the report sorting would simply be "magnitude - descending" then "machine - ascending".

I forgot to mention that I am working with Crystal Reports 8.5 and that is a necessity. I also have CR 2016 but the report needs to be generated in 8.5.

[DellSC]

The only way I can think of to do this would be to use a Command (SQL Select statement). How good are you SQL skills? If you would like some help building the query, please go to the Database menu and then "Show SQL Query...", copy the query and paste it here. Also, please let me know what type of database you're connecting to.

For more information about working with Commands, see the blog here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell