If you like crunching your numbers in a spreadsheet, you can do that by downloading your reports in XLSX/CSV format. When you download your reports, we’ll also send you 3 CSV files of raw data. Make sure you’ve set the date range you require and that you’ve applied any relevant filters before you click the download icon.
You will receive a link via email from which 4 reports can be downloaded;
Availability data (CSV)
Booking data (CSV)
Downtime data (CSV)
The report (XLSX)
Using the XLSX spreadsheet
The XLSX and CSV files contain the same data set, but the XLSX spreadsheet contains additional worksheets built on top of the raw data to provide a better summary and useful information at a glance.
When you open the XLSX file, you'll see an Introduction which contains a basic description of the contents of the file and how to use it, along with links to supplemental information that we've provided to help you make the most of the spreadsheet.
The Introduction is followed by a Table of Contents, which allows you to easily navigate between the worksheets.
The list of worksheets (highlighted in green) contain the following pivot tables:
Resources Scheduled: bookings for individual resources per day, in hours
Resources Overtime: booked overtime for individual resources per day, in hours
Resources Waiting List: waiting list for individual resources per day, in hours
Resources Unscheduled: unscheduled time for individual resources per day, in hours
Resources Availability: availability for individual resources per day, in hours
Resources Utilization: utilization of an individual resources per day, in hours
Resources Downtime: downtime for individual resources per day, in days
Projects Scheduled: bookings assigned to a project per day
Projects Waiting List: waiting list for individual projects per day
Clients Scheduled: bookings assigned to a client per day
Clients Waiting List: waiting list for individual clients per day
Those sheets are derived from raw data contained in the last three worksheets in the workbook:
Data - Bookings: bookings aggregated by each Resource, Project, Client and day
Data - Availability: availability, overtime and unscheduled time for each resource by day
Data - Downtime: aggregated downtime by each Resource, Downtime type and day
Introducing pivot tables
Pivot tables are tables that recalculate automatically based on the filters and metrics that you select and the raw data contained in the workbook. The worksheets marked in green contain the pivot tables that we've configured for you. You can modify them or create pivot tables from scratch by choosing one of the data sheets as the data sources.
Once you're satisfied with the data, you can copy/paste the data into other applications for further styling and editing.
Pivot tables are supported in Microsoft Excel, Google Sheets, and LibreOffice. For large data sets, we recommend using Excel, as that is the only application we've tested that can handle them reasonably well. Applications which cannot show pivot tables will still be able to display the raw data.
In this guide, we'll show you how you can navigate the various options available and use pivot tables in Excel to customize the views.
Pivot table fields
When a pivot table is selected, Excel shows the PivotTable Fields pane on the right edge of the window (highlighted above). This pane allows you to choose which data values you want to show across rows, columns, and as values within the cells.
In this example, the Resources pivot table shows days in columns, grouped by month and year. Similarly, the resources are shown in rows with billable/non-billable breakdowns. The values shown in the table are the sum of hours booked for that particular day and resource.
When we provide more than one value per row or column, we can collapse the value down to see the summary across that entire group of values by clicking the [-] button next to the row or column group.
When the row is collapsed, it will show a summary for that group, adding up all billable and non-billable hours for that particular resource.
This example shows how to collapse and expand a column group, and sum up values that belong to the same month. You could also do the same using the year column group, which would sum up all the dates belonging to the same year.
Removing column and row groups
If, for example, we did not care about the difference between billable/non-billable in the Booked Hours report, we can remove the Billable field from the Rows box in the PivotTable Fields pane.
This will remove the billable/non-billable breakdown in the pivot table, as shown above.
If we only care about the monthly summary, we can also remove the Date column.
We're now left with a table that shows the total number of booked hours for each resource, per month.
Grouping values in rows and columns
Let's say that we wanted to report the hours booked for each resource per week. We'll start with the default pivot table in the Resources worksheet as a base.
First, we'll drag and drop the Week Number field above the Date field in the Columns box.
Since we don't care about the day, month and year, we'll remove those fields by either selecting the Remove field option or by dragging the fields out of the Columns box.
We also want to remove the billable/non-billable breakdown in rows, so let's remove the Billable field in the Rows box.
This leaves us with a table that displays week numbers in columns and resources in rows, summing up each resource's weekly booked hours.
If we wanted to change our table to show the booked time expressed in days rather than hours (taking into account the individual resource's configured hours per day), we can replace the Hours field with Days in the Values box in the PivotTable Fields pane.
The table is now recalculated to display a weekly summary with days booked instead of hours.
Filtering with standard data and custom fields
If we wanted to limit the data set to a specific set of values, we can add those fields to the Filters box. The Resources worksheet contains a pivot table with the bookings filtered by their Confirmed field value, showing only those bookings which aren't in the waiting list.
To display bookings that are on the waiting list, you can select the Waiting value in the filter dropdown.
You can also add other fields, including custom fields, to the Filters box by dragging them from the fields list in the PivotTable Fields pane should you need them.
Here, we’re using a custom field called “Location” to filter to resources in South Africa by dragging the field name into the Filters pane.
Please note that this type of filtering only really works if you are filtering by a field where each resource is assigned only one of the values from that field. For example, if each resource is assigned with only one location. If you have a “Skills” field where a resource could have multiple skills assigned to them, eg Photoshop, Illustration and Animation, then you will not be able to effectively filter in this way.