Export and Summarize IFTA Data Using Encompass and Excel

Purpose

This article provides a step-by-step guide for extracting mileage and fuel records from Encompass and summarizing them in Excel using a Pivot Table to assist with IFTA filings. It covers navigating to the IFTA Form, setting parameters, exporting to CSV, and building a Pivot Table filtered by jurisdiction, fuel type, and work month.

When to Use This Article

• You need to prepare mileage and fuel data for IFTA quarterly tax filings.
• You want to export IFTA data from Encompass and organize it by jurisdiction in Excel.
• You need to filter IFTA data by Company Level, Fuel Type, or specific Work Months.

Steps to Follow

1. Navigate to Vehicles Forms IFTA Form in Encompass.
2. Optionally select Company Groups/Levels to filter the data (if not selected, data pulls across all groups).
3. Choose the Distance Type (Miles or Kilometers).
4. Select the Work Months to pull data from for your filing period (required).
5. Click Next, review the data fields, deselect any fields you do not need, and click Export to Excel.
6. Open the downloaded .csv file in Excel.
7. Click Insert Pivot Table and click OK to create the Pivot Table.
8. Drag Jurisdiction into the Rows box.
9. Drag Total Miles, Taxable Miles, and Fuel Total into the Values box.
10. Optionally drag Company Levels, Fuel Type, and Work Months into the Filters box to narrow results.
11. Use the filter drop-downs to display data for specific months, fuel types, or company levels as needed for your filing.

Visual Reference

This article provides a step-by-step guide for extracting mileage and fuel records from Encompass and summarizing them in Excel to assist with IFTA filings. Users access the data by navigating to Vehicles > Forms > IFTA Form, selecting Work Months, Distance Type (miles or kilometers), and optionally filtering by Company Groups/Levels. After exporting the data to an Excel .csv file, the article recommends creating a Pivot Table to organize and summarize key fields such as Total Miles, Taxable Miles, Fuel Total, Jurisdiction, and Fuel Type. Filters can be applied to focus on specific Company Levels, Vehicle Types, Work Months, or Jurisdictions, producing a clear summary for tax reporting purposes. This workflow streamlines the preparation of state mileage and fuel tax filings while ensuring accurate reporting across multiple jurisdictions.

Go to Vehicles > Forms > IFTA Form

---

• Selecting the Company Groups/Levels to include in the form is not required, but is available to choose from. If you do not select specific Company Groups/Levels, the system will pull the data across all groups.
• Choose if you would like to pull the Distance Type in Miles or Kilometers.
• Then, select the work months to pull data from for your filing. This is required.

Click Next when finished with these steps.

The next screen displays all of the data fields that will populate in the Excel file. You can deselect any fields you don't wish to appear in the file. There is no harm, however, in keeping them all selected. Click Export to Excel when finished.

The form will then be downloaded to your device in an Excel .csv file. Open the file.

---

It is recommended to create a Pivot Table to view the data and create a summary. Inside the Excel file, click Insert > Pivot Table.

Click OK. (This pop-up will show that the pivot table will include all of the data between the displayed cells.)

If you have multiple company levels, you can filter for Company Levels. Click, hold, and drag the Company Levels field into the Filters box.

Drag Jurisdiction into the Rows box.

If you file for vehicles with different vehicle types, drag Fuel Type into the Filters box.

Additionally, you could filter the results displayed by Work Months. Drag Work Months into the Filters box.

Finally, drag the Total Miles, Taxable Miles, and Fuel Total* fields into the Values box.
*Fuel Total represents all gallons of fuel the driver has submitted through the ELD app, as well as any gallons that have been added manually by an Encompass admin, or have been imported.

As you drag the fields into their respective areas, you will see the spreadsheet populating that specific data. This data shows the summary by each state or jurisdiction that you can use for your IFTA filings.

You can use the filters and other parameters you set up as you see fit. Click the down arrows to select specific filters in order to display specific data. For example, the data below has been filtered to only display for the month of July.

What Not to Do / Common Pitfalls

• Do not forget to select Work Months before exporting -- this field is required and the export will not include relevant data without it.
• Do not confuse Fuel Total with price per gallon -- Fuel Total represents all gallons of fuel submitted through the ELD app, added manually by an admin, or imported.
• Do not skip clicking Next after setting your parameters -- the field selection screen must be completed before exporting.
• Do not assume Company Groups/Levels are required -- if left blank, data pulls across all groups.

Escalation Path

• If the IFTA Form is not displaying data for a selected work month, verify that trips and fuel entries exist for that period and contact J. J. Keller support at 1-800-327-1342 if the issue persists.
• If the exported CSV file is empty or incomplete, contact J. J. Keller support with the parameters you selected.