Need to calculate totals in report

[Mickey_Cegon]

I have a custom application for Fleet vehicles, and a requirement that I can't seem to meet. We have a Vehicles table, and a Mileages table that is related. We have Business miles, Personal Miles, and Total Miles per mileage record, each associated with a vehicle.

What is needed is that the business users want to report on the Vehicles table, for different date ranges, how many miles were driven. Could be ran with different dates multiple times a day, for different vehicles.

I have other larger reports that I've done by building a custom table, and using scripts to populate that table with all the different calculations needed. But, these reports are only run occasionally, so I can just re-run the scripts for them.

They don't want to have to go through me every time someone wants to run the report to get totals with different date ranges. We have hundreds of vehicles, and lots of different areas may want to do an ad hoc report for date ranges.

Any thoughts? Am I missing something obvious? I'm not a big reporting guru, and I don't want to over-complicate this.

Mickey Cegon

Farm Bureau Financial Services, Inc.

[Michael Fry1]

Try this, on your Vehicle table > in a list view > on the mileage field > right click and use Configure>List Calculations. Then check Total value. This will put totals on that field in the list view, but the Totals calculation should be available in your report too so you can add it. I did something similar on Time Card table and it worked great.

[Mickey_Cegon]

We have the totals on the list view, but I didn't see a way to use that in the report. Here's what I have so far, but they don't want each vehicle mileage listed as a separate line, they want the totals, with the vehicle listed once.

u_vehicle.u_number	u_business_miles	u_personal_miles	u_total_miles
11458	492	0	492
11458	992	0	992
11458	1587	0	1587
11844	508	0	508
11844	328	0	328
11912	1895	105	2000
11912	42	458	500

So, here's what it needs to look like:

u_vehicle.u_number	u_business_miles	u_personal_miles	u_total_miles
11458	3071	0	3071
11844	836	0	836

Mickey Cegon

Farm Bureau Financial Services, Inc.

[Michael Fry1]

Looking at your screen shots, you manuallly created a u_total_miles field, versus using the system to calculate the totals. Then in your report, like a Multi-Level pivot, the totals field will show, and you can group by vehicle number to get totals by vehicle.

[Chris Sanford1]

I'm trying to do something similar, but with a report on the task table and with the summation on 'service offering.price'. Doesn't seem to work. Does this break on dot-walked fields?