cancel
Showing results for 
Search instead for 
Did you mean: 

Problem using Max formula for record selection

Former Member
0 Kudos

Hi Folks - I have a SQL fleet management database that contains service information on vehicles. I have created a Crystal Report to pull out a list of vehicles and their latest service dates and mileages. I have succesfully created a field in the database - "Last Distance" using a Max formula. However when runing the report I get many lines of max mileages for each fleet vehcile. So I attempted to use the Record Selection Formula, to just pull out the last odometer reading per vehicle - and I'm getting error messages, including "The Field Cannot Be Summarized". This is a Max formula, not a summary. I've also gotten "Formula can't be used because it will be evaluated later", and have tried changing the evaluation timing, but without success. There must be a way to select only the highest value of a field, per vehicle. Any help would be appreciated, thanks.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Mark,

Not quite sure where to start... If you really created a new field in your database for the "Last Distance" you should probably get that out of there...

To get the last service record for each vehicle... There are a couple ways to do this.

1) SQL Command

If you know SQL this will be the cleanest and most efficient way to get the information. It would look something like this.


SELECT
v.VehicleID,
v.VehicleName,
s.ServiceDate,
s.Odometerreading
FROM tblVehicleList AS v
INNER JOIN tblServiceHistory AS s ON v.VehicleID = s.VehicleID
WHERE s.ServiceHistoryID IN (
	SELECT Max(ServiceHistoryID)
	FROM tblServiceHistory
	GROUP BY VehicleID)

This will find the latest tblServiceHistory record for each VehicleID... This example assumes that the ServiceHistoryID is an "identity" field that increments up automatically each time a new record is added.

2) Running Totals

This method will work if working with SQL is totally out of the question.

1- Group your data on the vehicle field

2- Sort your data on the Service Date field

3- Create a Running Total on any field in the details section (that doesn't contain null values)

3a- The Running Total should be setup to as a COUNT type... Should be set to count every record... And reset on the Vehicle Group

If you've done everything correctly to this point, you should have a list of all vehicles separated by their respective service records. You'll also notice that the service record at the top of each grouping has a running total of 1 and contains the info you're looking for.

4- go to the left side of the screen and Right click the details section and choose Selection Expert...

4a- Click the X+2 button next to "Suppress (No Drill-Down)" and enter this formula:


IF {#RTotal0} = 1 THEN FALSE ELSE TRUE

This will suppress all service records that do not have a running total of 1

HTH,

Jason

Former Member
0 Kudos

Hi Jason and Brian -

Thank you for your prompt responses and suggestions. I'll try them at work tomorrow and let you know what works.

Take care,

Mark

Edited by: markbus on Jul 28, 2010 4:43 AM

ido_millet
Active Contributor
0 Kudos

No need for any extra efforts to create and use MAX summaries here. Just group on vehicle, sort the records by service date (ascending), drag any fields you want to show into the Group Footer, suppress the detail section, and you are done.

This is because the Group footer always shows information for the last record in each group.

Former Member
0 Kudos

Hello All - Jason, I'm more of a database user than a programmer, so I wasn't able to do the SQL script. I was able to accomplish the 1-line summary of latest mileages using the Footer formula/grouping suggestion from Brian and Ido. I'm grateful to all of you for your responses and solutions, I couldn't have done it without you. I hope you all have an enjoyable summer!

Best wishes, Mark.

Answers (1)

Answers (1)

former_member292966
Active Contributor
0 Kudos

Hi,

The Max function is evaluated after the Record Selection Formula has returned the records. This is why you get that second message. You cannot use summary functions like Max, Min, Sum, etc. in the Selection Formula.

What you can do is create a group on each vehicle and move your fields into the Group Header or Footer and then you can use the Max formula. Hide the Detail section so you don't see the extra records.

Good luck,

Brian