The pivot table

The other reports have a fixed shape and you filter your way to an answer. The pivot table is the opposite: it loads your time entries with all their context and lets you decide what goes down the side, what goes across the top, and what gets counted in the middle. It is the report to reach for when the question you have does not match any of the others.

Open it under Reports, then Pivot table. There is no filter form to fill in first. Your history is loaded whole and you narrow it down inside the table.

The three choices

Every pivot is three decisions.

• Rows are the field down the left. Drag Project, Client, User, Task or a date field into the row area.
• Columns are the field across the top. Date fields usually belong here: Year, Quarter, Month, Week, Date.
• The measure is the number in every cell. Pick the calculation first (Sum, Average, Count, Minimum, Maximum and a set of fraction-of-total variants), then the field it applies to.

Fields are locked to the role that makes sense for them. Hours, Price, Cost, Profit and Hour rate can only be measures. Everything else can only be a row, a column or a filter.

Straight out of the box the table shows Sum of Hours, with Project as rows and Year as columns. Rearrange from there.

What you can pivot on

Every entry arrives with its own context, plus ready-made date groupings so you never have to build them:

Any flexfields you have defined on time entries or projects appear as fields too. Which of these you see depends on your role: prices, costs and profit are not available to everyone. See Finding the right report.

Filtering, sorting and charts

Each field name carries a small arrow. Click it to open a list of that field's values and untick the ones you do not want. This is how you restrict the pivot to one client, to billable time only, or to a single year, without leaving the page.

The two arrow glyphs next to the calculation dropdown cycle the sort order, one for rows and one for columns: name order, then by total from low to high, then from high to low. Hover one for a reminder of which state it is in.

The renderer dropdown at the top left switches how the same pivot is drawn. Alongside Table there are heatmap variants that shade the cells by value, and six charts: Bar Chart, Stacked Bar Chart, Horizontal Bar Chart, Line Chart, Area Chart and Pie Chart. Switching renderer keeps your rows, columns and measure, so you can read the numbers and then look at the shape.

Two worked examples

Billable and non-billable hours per person, month by month

1. Drag User into rows, then drag Billable in below it.
2. Drag Month into columns.
3. Leave the measure on Sum of Hours.
4. Switch the renderer to Stacked Bar Chart.

Each person becomes a bar per month split into billable and non-billable. Somebody whose non-billable share creeps up over the year is visible at a glance rather than after a spreadsheet exercise.

What you actually earn per hour on each client

1. Drag Client into rows and leave columns empty.
2. Set the calculation to Sum over Sum.
3. Choose Price as the first field and Hours as the second.

This divides total revenue by total hours, which gives the rate you realised rather than the rate you quoted. Averaging the Hour rate field would not do this, because it weighs a ten-minute entry the same as a full day. Sort descending by total to put your most profitable clients at the top. See Rates and who gets charged what.

Exporting, resetting and large histories

Export to Excel writes a workbook of what you are looking at. With a table renderer active that is the pivoted table itself, headers and totals included. With a chart on screen there is no table to copy, so the underlying rows are exported instead.

Your layout is remembered between visits, in the browser you used. It does not follow you to another machine, and a colleague opening the pivot gets the defaults rather than your arrangement. Reset layout puts everything back.

The pivot loads up to 60,000 time entries, most recent first. If your history is longer than that, a warning at the top of the page tells you so, and the totals only cover the entries that were loaded. Narrowing the question in Timesheet reports is the better tool at that point.