Return Greatest Value

[f0rg3tfu1]

Hey guys,

This is really easy, but I cant remember how I did it before. Im running crystal 10 off of a SQL server.

In our accounting system, if a person gets a pay raise, their timesheet gets entered into the database as two different timesheets. One has the higher pay rate, and one the lower.

I just want to filter my records to return records with the highest pay rate in the database. I tried

Empl_lab_info.Hrly_rt = maximum((Hrly_rate))

but im still getting an error.

Thanks to anyone who can help!!

 

[lbass]

Assuming you have a group on {table.employee}, then you would go to edit selection formula->GROUP and enter:

{Empl_lab_info.Hrly_rt} = maximum({Empl_lab_info.Hrly_rt},{table.employee})
//where {table.employee} should be replaced with your group field

You would likely need to use running totals in calculations once you have used group selection, since the non-group selected records will still contribute to inserted summaries.

Another approach would be to create a SQL expression {%maxrt}:

(select max(AKA.`Hrly_rt`) from Empl_lab_info AKA where
AKA.`Employee` = Empl_lab_info.`Employee`)

You would substitute your exact field name for "Employee" and I have assumed here that the fields are all from one table. Leave "AKA" as is, since it represents an alias table name. Then go to edit selection formula->RECORD and enter:

{%maxrt} = {Empl_lab_info.Hrly_rt}

With this latter approach, you can insert summaries on the field.

-LB