How do I pivot a payroll report in Report Builder?

Pivot turns a long payroll report, with one row per pay item, into a wide report where each pay item has its own column. For example, if Employee is one of the columns, each employee appears on one row with their pay items across the top. Total gross, Total net, and Total employer contributions appear at the end of each row.

Pivot is available to companies that use Payroll or PEO (Professional Employer Organization), because only these companies have the Pay items data source. Companies that only use Employer of Record (EOR) can't pivot reports.

1. Go to Reports & Analytics > All reports.
2. Open a report that uses the Pay items data source in edit mode, or create a new report and select the Pay items data source.
3. In the panel on the right, click Pivot. If you see the AI chat, switch to Manual first.
4. Switch on Turn on pivot. An option is picked for you. Change it to Pay item name or Pay item type if needed.
5. Review the preview, which updates by itself. To get the full file, click Download report.

The report needs the column you pivot by and the Amount column. If either is missing, the panel adds it. Keep at least one other column in the report, such as Employee. Otherwise, the whole report shows as one row.

You can also ask the AI tab in the builder to pivot a report. If it can't, it explains why.

To turn pivot off, switch off Turn on pivot. The report goes back to one row per pay item, and any columns added for the pivot stay in the report.

Each row is one combination of the other columns in the report. For example, with Employee as the only other column, you get one row per employee. If you add a payroll period column, you get one row per employee per period.

The columns depend on what you pivot by:

• Pay item name: One column per pay item in your data, within the report's filters. New pay items appear as new columns automatically.
• Pay item type: Always the same 17 columns, even when some are empty: Base salary, Cash benefits, Non cash benefits, Expenses, Incentives, Other, Employer contribution, Deduction statutory, Deduction non statutory, Net pay, Holiday accruals, Severance accruals, Other accruals, Net pay adjustment, Total payroll cost, Reporting amount, and Tax subsidy.

Pivoted columns can't be renamed, reordered, or given a formula.

When a row has no amount for a pay item, the cell shows a dash in the preview and is empty in the downloaded file. Zero amounts are left out of the report, so a cell only shows 0.00 when amounts cancel each other out, such as a payment and its reversal. Keep this in mind if you download the report and add up the columns in a spreadsheet.

Totals are calculated from the underlying payroll rows, so they may not equal the sum of the visible columns. For example, if two legal entities use the same pay item name for different kinds of amounts, that pay item is left out of the totals but still appears as a column.

• Total net isn't the same as the Net pay column.
• Net pay, accruals, Net pay adjustment, Total payroll cost, Reporting amount, and Tax subsidy aren't included in any total.
• There's no total deductions column.

• Cells show sums only.
• A pivot by Pay item name can create up to 200 columns. If it would create more, you'll be asked to narrow the report with filters.
• The preview shows the first 25 rows of the pivoted report and up to 60 pivoted columns. The downloaded report includes everything.
• CSV, XLSX, and XLS downloads all keep the pivot, and so do duplicated reports.

See also: Report Builder