Hey, @rrand-1186​!

​

The best report I can think of to get starting and ending odometer values would be an IFTA report. I believe that if you took out the states and ran it for a specific time frame, that should give you your starting and ending odometer. 🤔

Hope this helps! :)

​

Best,

Joy @FleetNavSystems-1081

I think this will help @rrand-1186​! I took a solution that I worked on in another thread with @jjake-8255​, and re-shaped it to this report specifically. Here's how it works.

FIRST, SEE IT IN ACTION

Here's the example on a real database. I've used the modified TimeCardWithOdometers.xlsm (attached at the bottom of this post), entered database settings for the API connection on the "Settings" tab, and then uploaded it to Geotab to pull for a date range. I took out some of the loading times and obfuscated customer data, so you can see how it runs live.

NOW, HOW DOES THIS WORK?

This works by going around some of the built-in Geotab Reporting. I go into more detail in another thread on Group Id found here: https://community.geotab.com/s/question/0D52J0000884kNNSAY/group-id-column-in-the-device-report?language=en_US.

Essentially, I'm making a separate connection to the API to look up Odometer Data for the Vehicle and Date, then populating this into the Excel file. This is extremely powerful, in that you can get data that you won't normally have access to inside a single report, but there are a few caveats to be aware of.

WAIT! WHAT ARE THE DRAWBACKS?

There are a few issues to be aware of using this approach, so keep them in mind.

• You have to enter your database credentials on the "Settings" tab to make a connection to the database through the API. This goes no further than the Excel sheet, but I still recommend creating a separate, read-only, low-priority account for this to stay secure.

• This lookup is database and Excel intensive. Essentially we're asking Geotab for dozens, hundreds, or even thousands of mileage readings across a period of time. It takes a while, and it's not something you want to run all the time, since it will slow your database. That's the loading time you see when I open the Excel file above.

• Time Zones are hard to correlate in Excel. I use the local system time offset and convert to GMT. If you're running the report for a different time zone than your local system time, you may get some error in the odometer values because of mismatched time zones. In an ideal world we should cross-reference the time zone in the report to the correct offset and use that, but this iteration is not that advanced.

Let me know if you have any issues, and if this gets you closer to what you're looking for. Thanks for your time, and have a great day!

Scott Applefeld

ASR Solutions

0692J00000DAw3eQAD.gif

0692J00000DAw3PQAT.xlsm