To view outstanding jobs by customer, employee and job type on the job dashboard

1. Click the filter
2. Select Outstanding jobs
3. Type in the customer name, job type and engineer’s name in the filter box

Check out the video below to see how it’s done.

Outstanding jobs per customer type and engineer

To view outstanding jobs by customer, employee and job type as a report

• Go to the Reports screen

• Click Jobs and then All jobs
• Enter a date range covering a period when the jobs were added
• Click Generate report

You can then type in the customer name, job type and the name of the engineer the job was assigned to.

To create a dashboard in Google Sheets,

• Click the Google Sheets icon
• Click Copy formula
• Click Create a new sheet in the alert which pops up
• In the new Google sheets, select a cell and paste the copied formula

• Highlight the cells containing all the data which has been pulled down from Okappy

• Click Data
• Click Pivot table

• Check New sheet
• And then click Create

• Click into the pivot table and then select the following options in the pivot section on the right of the sheet
  • Click Add in the Rows section
  • Select customers
  • Click Add in the Columns section
  • Select Assigned
  • Click Add in the Values section
  • Select ID and then in the Summarise by option, select COUNT
  • Click Add in the Filters section
  • Select Status and select the following statuses: No status, On site and Work done
  • Click Add in the Filters section again
  • Select Type
  • Select the relevant job type you want to see the data for

To have Google Sheets update the pivot table automatically.

• Go to tab which contains the original downloaded data
• Select the cell where you originally pasted the formula from Okappy
• Find the part of the formula which contains ToDate

=IMPORTDATA(“https://www.okappy.com/myautoalert/reports_export.jsp?ReportType=All+jobs&FromDate=01-01-2019+09:00&ToDate=29-08-2019+17:00&key=15569099553381b50cfe22c0a3167e174cef4dccd8&ForCID=-1&invoiceType=raised&UserID=2&description=-1″)

• Replace the data and time with &TEXT(TODAY(), “mm-dd-yyyy 23:59”)

=IMPORTDATA(“https://www.okappy.com/myautoalert/reports_export.jsp?ReportType=All+jobs&FromDate=01-01-2019+09:00&ToDate=”&TEXT(TODAY(), “mm-dd-yyyy 23:59″)&”&key=15569099553381b50cfe22c0a3167e174cef4dccd8&ForCID=-1&invoiceType=raised&UserID=2&description=-1″)

To create a dashboard in Google Sheets,

• Click the Excel icon to download the report to your computer
• Open the file

• Select rows 9 onwards

• Click Data
• Click Pivot table
• In the PivotTable wizard check
  • Microsoft Excel List or database
  • Click Next
  • Ensure the range contains the data you have just downloaed
  • Click Next > 
  • Check New sheet, and
  • Click Finish

• Drop the Customer into the rows section
• Drop Assigned into the columns section
• Drop ID into the Data section
• Drop the Job type into the Page section
• Drop the Status into the Page section

• Right click within the pivot table
• Click Field settings
• Change Summarise by from Sum to Count

• To select outstanding jobs only, click the Status field
• Select No status, on site and Work done

Note: The ability to select multiple options is not available on older versions of Microsoft Excel

For further information about the reports you can generate from within Okappy, check the reports section of our support site.  Alternatively, check out the questions and answers in our forum.