Our webinar – Top Reports for Growing Companies was held on Wednesday 22nd September 2021.
Okappy Portal+ is all about providing key information to decision makers in a timely and easy to understand format so the best strategic decisions can be made for the company.
During the webinar we explored

the key reports which are available within Okappy
how to download information into Microsoft Excel or Google Sheets
combining data from different reports

We will also demonstrated :

advanced dashboards so you can see key metrics at a glance
different reports to provide insights and ideas for growing and improving your business

Following is the text of what was discussed.

Okappy is a business 2 business connected workforce management platform which applies social and market networking technology to a real business need. The need to communicate and collaborate with your employees who are often at different locations (and now more than ever). With your subcontractors and with your customers.
Okappy helps the most forward looking and dynamic companies increase transparency, reduce duplication, costs and errors and increase efficiency. Ultimately making a significant contribution to bottom line profits.
With Okappy, you can connect to your employees, customers and subcontractors. Send and receive jobs and see the status of those jobs as they’re updated by your engineers and subcontractors in real time.
We also have a suite of reports which helps you see what is happening in your business. They can be downloaded to your favourite spreadsheet application enabling you to manipulate your data and you can even create dynamic dashboards in Google Sheets and some version of Office 365 which updates as your jobs update.
It’s this last area I’m talking about today. Which reports are available and how to use them to answer questions and define your strategy.

Over the next 20 minutes I’m going to show you which standard reports are available and things to consider.
I’ll show you how to create additional reports from your jobs or invoices.
We’ll export a couple of reports to Microsoft Excel and demonstrate how to combine reports to provide more advanced information.
I’ll create a Google Sheets spreadsheet which pulls information from Okappy and then demonstrate how you can build complex dashboards which update in real time
Finally, I’ll touch on some ideas for reports you could use to grow your business.

The majority of the reports are available from the report tab at the top right of the screen.
If you click into reports, you’ll see a list of reports on the left. Grouped by the main sections within Okappy – Jobs, Invoices, Tracking (vehicle and device details) and Other.
I don’t propose to go through every report, but key reports include the all jobs report and then variations on that such as completed jobs by customer.
You have the invoices reports which includes invoices received (from supplies as well as our invoices to you). You can view your quotes and then view your invoices raised again with variations on that.
The vehicle / devices reports are more for our customers that use the tracking functionality. Within that section you have reports which show all the location messages and alarms sent from any of your devices. You can view working times (when the devices first switched on to when they last switched off) and you can see speeding and routes taken.
If you have our installed devices then you can also get more advanced reports such as driving times, idling times and stationary times.
Then we have the other section which contains reports such as All activity, Customers, Subcontractors and Employees. You can also view documents saved for customers, subcontractors or employees, Timesheets, Notes, Login attempts and messages sent.
You can also create a search report based on keywords for your jobs, invoices and messages
Running reports
Most reports are generated in the same way.

Simply choose the relevant report from the list on the left
Enter your report options which typically includes the start and end date for the report, but could also include additional options depending on the report
Press the Generate report button

That will create your report.
Just to note, we have help now within the reports which provide more information about each report and what they contain along with a link to our support pages.
Once your report is run, you can order it by any of the fields and also filter the information in the same way that you can on the job dashboard and invoice dashboard.
Again just to note, the browser will remember any filters so if you go to another screen and then come back, the same information will be there ready for you.
At that point you can export the data to Excel, either as a download or depending on your Excel version you can create a dynamic export.
You can create a Google Sheet with the data, or you can email the report.
Some of the reports also have additional options such as the invoices for customer report which allows you to create a Tradex file for upload into Causeway trades invoice portal
There was a few report which don’t have options. Those are the

Latest activity report
Vehicle / device details report
Ignition on today report
Customers report
Subcontractors report
Employees report

If you click on the option on the left, that goes straight into the report as the date and time is not relevant i.e., the latest activity or the customer report.

To export a report to Excel

Generate your report
Click the Excel icon
Click Download file.

You can then open the file in Excel and manipulate the data.
You can also copy the formula and then use that as a data source in Microsoft Excel. Unfortunately it’s not available in all versions and is not available on Apple Macs so I can’t demonstrate it here. But we do have step by step instructions on our support site so I’ll include that at the end.

To export a report to Google Sheets

Generate your report
Click the Google Sheets icon
Click Copy formula

You can then click the Create a new Google sheet link in the alert or if you have one open already, go to that sheet now.

Choose an empty sheet
Select a cell
Press CRTL V to insert the formula into that cell.

Google Sheets will then connect to Okappy and download the report.
It will also keep the report updated so every time you open the sheet it will pull back the latest information from Okappy.
One thing to note is that there is no setting available with Google Sheets as to when the data will be updated. It’s normally when you open the sheet but if you open it a couple of times then Google tries to be smart and won’t re-request the data.
The only way to force Google sheets to update (that I know of) is to change the formula slightly and then change it back at which point Google sheets will recalculate and pull in fresh data from Okappy.

Use VLOOKUP when you need to find things in a report or a range by row. For example, look up a customer email address for a job.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
Say we want to get a list of jobs and add a column with the customer email address (I’m assuming it has not already been included in the job details).
In that case, lets use the Completed jobs report over the last month.

Go to report
Select Completed jobs
Enter the date from the start of August 2021 to today
Download that report

Now if it was just a few companies, I could just go the connections screen and search for them. But in the real world, you’re likely to be doing jobs for lots of different customers so it would be good to automate the process.
The jobs reports contain details of the jobs, not necessarily all the customer details.
If I want to see all the customer details, then I’d need to look at a separate report.
For that I’d go to Other and select the Customers report to get all my customer details.
Download the customer report so I now have two files.
Vlookup will work across different files, but for ease im going to move both files into one spreadsheet

So I click the tab, select Move or Copy
Select the other spreadsheet or create a new book
Check Create a copy
Click Ok

If I’ve created a new book, then I’ll need to do it for both spreadsheets.
Once I’ve combined both tabs into one spreadsheets, i save it.
Go to the complete jobs tab. You can see I’ve got all my job details but no customer email.
So to pull in the customer email, i first add another column. Lets call it email and highlight it to make it clear.
Then start typing the Vlookup formula. I want to use the customer name in column F.
I’ll choose all the columns which contain my table in the customers tab. You can select a particular range but doing it this way is a bit easier and also ensures the formula will work even as new jobs are added.
Next I want to choose whicch column to pull back.
I choose column 1 initially as it just helps to see that the formula is pulling back what I expect as my new column should contain the same names as what is in column F.
If you get #NA at this stagefirst thing to check is that the data actually existing in the table you’re looking at. Copy the name and ensure it is there.
Some other things to check is that the format of the data is the same. To check for that, select each column. From the File menu choose format, cells, and set them both to text.
Another common cause of vlookup not working is if the table is not sorted. In this case i’ve pulled the information from Okappy so it is already in alphabetically order. If not, click the relevant column and sort it.
Now i’ve got the data in my jobs table and im happy its correct. Go back and check which column you want to include. In this case its column D, or the fourth column
So I update my formula to pull back column 4 instead of 1.
And there you go, i have my contact email for each job
Other formulas that you might want to use include HLOOKUP which looks across a table rather than down. You can use the index match formula or you can use pivot tables.

You can create a dynamic dashboard in both Excel and Google Sheets.
As mentioned before, Excel requires specific versions. Google Sheets is a bit friendlier to use and the option is not available on Office 365 for Mac. So I’ll be using Google sheets to demonstrate today.
Here’s some examples of dashboards that some customers use. But there’s many more. Always keen to hear what dashboards you create, so ping us on Twitter, LinkedIn or in the comments once we publish the video of this webinar.
If I want create a dashboard showing my top customers

Go to Reports
Select Invoices
Choose Invoices raised

Let’s run the report over the year and include archived invoices. You can see your list of invoices and click on each to view their details.
Now click the Google Sheet icon and press the Copy formula button.
Click Create a new google sheet in the info alert or use a sheet you already have open. Just to note you would need to have a Google account to open Google Sheets and ideally be logged in.
In your new sheet,

Select an empty tab
Choose a cell
Press CTRL V to import the formula copied from Okappy

That creates your spreadsheet in the same way as if you had downloaded it to a file.
You might want to change the format of the dates by

Selecting the two columns
Choose Format from the menu bar
Select number and then choose Date

Once you’re happy with the import, we can create a pivot table showing each customer and the sum of all their invoices.

Select the table you’ve imported
Choose Data from the menu bar
Select Pivot table
And I’ll create it in a new sheet

For the rows I want my list of customers. So in the pivot table editor, click the Add button on the Rows section and then choose Customer.
For the values, I would like the sum of all the invoices for each customer. So click Add again in the Values section. Choose Net this time
Then ensure we’ve got summarise by sum selected.
And there you go, we have our table.
Again, I can format the invoice amount by selecting that column, choosing Format from the menu bar, choose Number.
If I want to remove certain customers, I go back to the pivot table editor

Click Add next to filters
Choose Customer again
And untick the customers I don’t want to show

I can also create a nice chart by highlighting the pivot table, clicking Insert from the menu bar and then selecting Chart
The beauty of that is that as we add more invoices, that graph will automatically update subject to the caveat I mentioned earlier.
You can do this in Excel as well in a similar way.
We’ve got some step by step instructions for some other example dashboards on our help and support site, so again I’d urge you to have a look there.

We’ve gathered together some top reports that some of leading customers use to really grow and improve their business
The Gas Safety Check Report was developed by one of our plumbing customers. They were doing Gas Safety checks and wanted to check all the certs done the previous year so they could send out a reminder mailing to all those customers.
Looking at Jobs per day or Jobs per month is useful to see busy times of the week or year. For example if you run the report and October is generally quieter, or if Friday tends to be your quiet day then you’ve got the option of sending out a mailing to all your customers, perhaps with a discount for that month or day.
On the opposite side of the coin, if next month is generally busier then that might prompt you to recruit employees or subcontractors.
Jobs per type shows you which types of jobs you’re doing the most. Again depending on your business, you could have plumbing jobs, electrical jobs or private individual jobs. Creating a jobs per type report shows you how many of each type of jobs you are dong.
If you want to look at the Jobs per location, this is particularly useful for companies who operate nationwide or even globally. Here you can see which divisions are doing the best, or again where to recruit.
Revenue per customer is obvious useful but what about the number of Jobs done by customer. Using both these reports allows you to compare which customers are doing the most jobs vs which are generating the most revenue. Often its not like for like where you could be doing a lot of jobs but not generating as much revenue. By ranking there average revenue per job it might make sense to re-focus your effort perhaps on smaller growing customers where the revenue per job is higher.

There’s lots of information on our help and support pages including help articles, questions and answers and videos.
Here’s some links to some relevant help and support articles:

https://www.okappy.com/support

Dynamically linking to spreadsheets

Reports

Thanks once again for attending our webinar. I hope you found it useful.
Our next topic is going to be about Effectively Implementing Workforce Management, that will be the same time, 2pm on Wednesday 20th October.
Happy to take any questions.

Which versions of Microsoft Excel does it use? – Okappy is compatible with all versions of Microsoft Excel. For the dynamic link you will need to the latest version of Office 365 and that is just on Windows unfortunately.
Is there any limits to how many jobs you can upload? – This use to be an issue with Excel as a spreadsheet could only have a certain number of rows (I think 16,000). On the latest version though you can have 1 million rows so that should be enough for most customers. Likewise with Google Sheets there’s no particular limit although there are limitations in how many rows can be copied at at time or imported from another source such as Okappy. With cloud based versions of Excel or Googles its also worth considering how powerful your computer is as the bigger the spreadsheet and the more calculations it has to do. The more resources it will use which could freeze your computer.
What are the best formulas to use? – this is really down to choice and your own experience as you can often use different formulas or a combination of formulas to get achieve the same results. I use Vlookup a lot and pivot tables are also very useful. I didn’t get chance to demonstrate a pivot table but we have step by step instructions on our support pages.
Can Okappy help create specific reports? – We offer consultancy which can be used to develop specific reports or dashboards in Excel or Google Sheets
Do you cover reporting on any of your training? – Yes our advanced training is tailored to your specific requirements so we have covered reports and more advanced spreadsheets skills in the past