How to create a custom dashboard report in MyGeotab

Going beyond standard reports can take you to a new level of insight and capability. Personalized reports provide a deeper look at the metrics which matter most to your business. 

What are custom dashboards and reports?

Get a deeper understanding of your fleet with a customizable dashboard that shares essential data such as fuel consumption, maintenance or compliance trends in a single, cohesive view. 

Custom dashboard reports are easy-to-use and present data in a more systematic and centralized way. They help managers with answering complex business-related questions and organizing their fleet efficiently, thereby improving operations. 

What are the benefits of using a custom dashboard report?

While standard performance reports like the Risk Management Report, Customer Visits Report or Watchdog Report are essential to making decisions for everyday business. Personalized reports help managers better understand the patterns and trends in their fleet which are impacting their bottom line.

Benefits of using a custom dashboard report:

1. Get quick, meaningful insights.
2. See only the data you want and none of the data you don’t want.
3. Evaluate your company’s fleet with better clarity.
4. Make critical decisions based on facts, not instinct.
5. Keep track of the latest trends.
6. Make your job easier.

How often should you check a custom dashboard report?

It may be realistic to check a custom dashboard report on a daily basis, or quarterly, depending on the use case. A best practice is to schedule runs and refresh times so you are always working with the latest intelligence. 

How can I create a custom dashboard report?

The Dashboard section of MyGeotab is the front page of your fleet management portal. You can pull any data from your portal on demand. 

However, putting the important metrics in one central location is a great way to save time and makes your job easier.

There are four easy steps to setting up a new dashboard report:

1. Determine what you want to measure.
2. Export the Exceptions Report into MS Excel.
3. Create an Excel chart.
4. Import your Excel file into MyGeotab.

Follow the instructions in this post to quickly master this process.

Designing a MyGeotab Custom Report

Step 1: Determine what you want to measure

First, decide what you want to measure. Your metrics will be determined by your company goals, such as driver safety, fleet optimization, or sustainability. What is most important to your business? 

There are many different things you could target, such as:

• Counting the number of times a particular rule was broken
• Seeing how many miles/kilometers the fleet drives every day
• Determining the maximum speed
• Setting up a driver scorecard
• Evaluating the fleet’s risk level
• Top seat belt rule offenders

Step 2: Export the Exceptions Report into MS Excel

In this example, we’ll start with something simple: identifying the top seat belt offenders.

First, you’ll need to export an exception report into Microsoft Excel. 

In your MyGeotab database, go to Rules & Groups, and click Exceptions. Under Options, select your date range, your vehicles, and your rule (in this case: Seat belt). Click the Apply changes button.

Custom reports only allow a maximum of 1,000 rows in Excel, so select a number of vehicles or a date range that does not exceed this limit.

You will see a list of exceptions. To export your file, click Report, select Details, click Standard and the file will download to your computer.

Step 3: Create an Excel Chart

Open the downloaded Excel file. Ensure that you are viewing the Summary tab. This Summary tab contains a PivotTable that we will use to build this MyGeotab Dashboard report.

Click on the PivotTable. The PivotTable Fields box will appear on the right side of the screen, as shown below.

Let’s refine the view to show the count of seat belt violations. Uncheck the field boxes, leaving only Name and Incident Count checked. The PivotTable will update automatically.

Click on the PivotTable again. On the Excel toolbar, under PivotTable Tools, click Analyze, then PivotChart.

From the options provided, select the type of chart you would like to display on your Dashboard (column, pie chart, bar graph, etc). 

In this example, we are using the default Clustered Column. Click the OK button. The PivotChart is inserted into the Excel report.

You can import this new Dashboard report into your database. If you want to stop at this point, save your file in Excel in either a .xlsx or .xlsm format, and jump ahead to Step 4.

Optional: Format Your Excel Chart

At this stage, it is best to make a few formatting changes to personalize the chart. This only takes a few minutes and makes your chart more user-friendly and visually appealing.

1. Simplify the table by removing the extra boxes. This is easy to do. Just right-click and delete the Total boxes in the chart.
2. Sort the data so it displays from left to right, starting with the highest number of incidents. Find the Name box in the PivotTable, then click the small dropdown arrow on the right side of the cell. Select More Sort Options. Now, click Descending (Z to A) by: and select Total Incidents. Click OK. Our PivotChart is now sorted by our top seat belt offenders.
3. If you have a large fleet, you may only want to display the top 10 or 20 vehicles in the category. To do this, click the dropdown arrow in the Name cell, then select Value Filters and Top 10. Input your value and click OK.
4. Click on the PivotChart, then, under PivotChart Tools, click Design. Mouse over the Chart Styles to preview, or click to select and change your chart. You can even change the color of the chart by right-clicking on one of the columns.
5. Save your file in Excel in either a .xlsx or .xlsm format. We now have our completed Dashboard report, and are ready to bring it back into our database.

Step 4: Import your Excel file into MyGeotab

In your database, go to Administration > Reports > Report Views, then click Create Custome Report. Drag and drop your file onto MyGeotab, or click to browse and select the file from your computer.

Once the file is uploaded, you can modify the report settings. Find your report in the Report View tab where you uploaded it and click its name to find the following options: 

1. Report view tab — Switch the toggle for “Show report in drop down list” to On.
2. Dashboard tab — Switch the toggle for “Dashboard graphic options” to On. If you want the report to display on your own Dashboard, set “Display on my dashboard” to On as well. Under Dashboard viewers, you have to choose whether you wish to add this report to the dashboards of all company users or only for users in a certain group or groups.
3. Email report tab— On this tab, you can setup a reoccurring email to go to individuals or groups of users on your database.

The option to change your report is on these tabs. Most commonly you will be asked to select your Date range, Refresh period, and Next run date. 

• In this case, we are looking to run this as a weekly report on our dashboard. Click the Dashboard tab to see those options.
• Under Display options, make sure Group Dashboard Viewers is set to “All in Company Group”.

• Scroll down to Report Data Configuration and choose the Date Range “Last week”, the refresh period will be “Weekly,” and the next run will be this coming Monday at a time in the morning. The report will continue to automatically refresh every Monday morning at the same time.

• The report will show all vehicles in the fleet by default. If you want this report to display data only for a specific group of vehicles, specify that group by using the “Belonging to” dropdown and selecting the group, by default it will show “Everything”.
• Under Additional Report options, click in the Exception rules box and select the Seatbelt rule.
• Finally, scroll up to the top and rename your report by double-clicking the title of the report and rename it“Seatbelt Violations” for example. Click Save.

Your new custom report is now available for viewing on your Dashboard!

For more information, check this video: Custom Reporting Basics Video 

Dive deeper into data

Dashboard reports are very powerful tools that can be used to observe trends like fuel economy, vehicle stops, and complex scorecards. Using the steps outlined above, you can now create your own beautiful, easy-to-understand dashboard reports in MyGeotab.

Visit the Geotab Community to ask questions, or post your own success tips or stories to help other Geotab users.

Originally published on August 11, 2016.