Skip to main content

Building pivot tables and saving layouts with Design report

How to open the full screen pivot table designer for the budget report and the actuals report, build a table by dragging fields to the filter, column, row and value zones or with Add, choose the summary and the percentage display, set subtotals and…

This article explains how to build your own pivot tables from the rows of the budget report or the actuals report. Design report opens full screen: pages and tables on the left, the fields and the zones of the selected table on the right. The tables are calculated from the report's own rows; their grand totals match the report's figures to the cent.

Before you start

  • The plan's report has been created at least once (Creating and recalculating the budget report). For the actuals report, the closed payrolls have been brought in on the Actuals step (Comparing the budget with closed payrolls (Actuals)).

  • Designing reports needs the manager or assistant manager responsibility of the module. With the view permission the button is disabled.

  • The tables only hold the employees you are allowed to see: your salary permission and branch scope apply as in the report.

Step by step

Opening the designer

  1. Open the plan and press Design report on the Report step. For the actuals report, press Design report on the card of the employee table on the Actuals step. The badge next to the title tells you which report you are in: Budget report or Actuals report.

  2. The first table is ready: Period in the rows, Total cost in the values (budgeted, actual and deviation in the actuals report) and the grand total on.

    Design report

Building a table

  1. Type into Search fields on the right or pick a category: All, Period, Employee details, Cost centre, Totals, Pay items, Bases and premiums, Expenses. The period fields include Quarter and Year. A category shows only when the report has fields in it.

  2. Drag a field to the Filters, Columns, Rows or Values zone. On a touch screen or with the keyboard, press Add next to the field and choose Add to rows, Add to columns, Add to values or Add to filters. Text fields such as branch, department and period go to every zone; amounts go to Values only.

  3. Drag an item of a zone to change its order or zone; on a touch screen use its ⋯ menu: Move up, Move down, move to another zone or Remove.

  4. Press an item in Values and choose Summarise by: Sum, Count, Average, Minimum, Maximum, Distinct count (for example, the distinct count of Employee is the number of people). Under Show as choose Value, % of row total, % of column total or % of grand total.

  5. Press a field in Filters and tick the values the table should include. With none ticked, every value is included.

  6. Under Layout options set Subtotals (At the bottom, At the top, Do not show), Empty cells (Leave blank, Show zero, Show a dash), Grand total row and Grand total column.

  7. For a new field that adds or subtracts two amounts on every row, press Add calculated column: choose the First field, the Operation (Add or Subtract) and the Second field, and type a Column name if you like. The new field sits in the Calculated category and is saved with the layout.

Pages and tables

  1. Add table adds a table to the page, New page opens a new page. Pressing a table selects it and the right panel shows its zones. Expand spreads the table over the full width of the page. The table's ⋮ menu has Rename, Copy, Download as Excel and Delete table; the page's menu has Rename and Delete page.

Source data

  1. Double-click a cell (tap it on a touch screen, or move to it and press Enter). The Source data panel lists the rows behind that cell with the employee and the period; their amounts add up to the number in the cell. In the budget report, the calculator icon of a row opens the How was this number calculated? drawer (See how a number in the report was calculated).

Source data

Saving, opening, downloading

  1. Press Save, type the Layout name and choose Only me or Company-wide under Who sees it. In a saved layout, Save writes the changes to the same layout; for a copy use Saved layouts > Save as.

  2. From Saved layouts choose New layout, Start with a blank table or a layout of the My layouts and Company-wide lists. Layouts belong to the company: you can open the same layout in every budget plan.

  3. Download > Selected table or All tables downloads an Excel file. Every table is a sheet of its own; numbers are number cells, subtotal and grand total rows are bold, the filters are written at the top of the sheet.

  4. To delete the open layout, press Delete and confirm. Close the designer with Close or the X; with unsaved changes you are asked to choose Save and continue or Continue without saving.

Rules and limits

  • The tables are calculated from the report's rows (employee and month); the grand totals match the report's figures, and in the actuals report the budgeted and actual cards. Totals are calculated from unrounded values and rounded at the end.

  • A percentage display is the cell's share of its row, its column or the table total; subtotal rows follow the same rule.

  • Up to 24,000 rows the tables are calculated in your browser; larger reports are calculated on the server with the same rules, and a note above the designer says so. A very long table draws its first 200 rows; continue with Show more rows.

  • Employees you may not see appear in no field, filter, cell or Source data list; the designer says Only the N employees you are allowed to see are included.

  • Personal layouts are seen and changed only by their owner. Company-wide layouts are seen by everyone using the module; only the module admin saves, changes and deletes them. A layout stores fields and options, never amounts.

  • When you open a layout in a plan that lacks some of its fields, those fields are removed from the layout and a warning says so.

  • In a headcount plan every row is a group's month and its amounts are already multiplied by the group's head count. The Headcount field is in the Totals category: summed in the values it gives the person months (the report's Total payslips figure); the distinct count of Employee counts groups.

  • In a rolling forecast the Month type field (Actual, Forecast) separates the closed months from the forecast months.

Common mistakes and FAQ

  • Design report is disabled: You have the view permission; ask your admin for the assistant manager responsibility. On the Actuals step the button is also disabled when there are no rows to compare.

  • I cannot drop an amount into the rows: Rows, columns and filters take text fields only; drop amounts into Values.

  • Company-wide cannot be chosen: Only the module admin saves company-wide layouts; save your own with Only me.

  • A total differs from the figure card: The table has a filter; check the Filters zone.

Related articles

Did this answer your question?