Crystal Reports - Conditional record sorting

[former_member673635]

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_member673635]

*"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.

[abhilash_kumar]

Ok here's what you need to do:

1. Insert a Group on the Machine field

2. Create a formula called @sort with this code:

SELECT {Fault_field} //replace this with the database field or formula field that displays Fault strings
CASE 'severe' : 1
CASE 'significant' : 2
CASE 'insignificant' : 3
DEFAULT: 4

3. Go to the Insert Summary option > Choose the @sort field to aggregate on and set its aggregation function as "Minimum"

4. Suppress the summary field that CR places on the Group Footer

5. Go to the Group Sort Expert > Select Machine Group as the group to sort ALL and sort it based on the @sort summary field in Ascending order.

-Abhilash

[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