Reports Editor: example of how to create a pivot table
This article describes the step-by-step creation of a pivot table using the Reports Editor.
This example is based on the Payments file in the BiblioMaker software. This file contains a list of payments made by library users. A Payment record contains the payment date, the amount paid and a payment method. The pivot table will show the sum of daily payments broken down by payment method, as illustrated below:
| 1.10.2024 | 2.10.2024 | 3.10.2024 | Total | |
| Cash | Fr. 25.00 | Fr. 20.00 | Fr. 30.00 | Fr. 75.00 |
| Card | Fr. 10.00 | Fr. 12.00 | Fr. 12.00 | Fr. 34.00 |
| Invoice | Fr. 70.00 | Fr. 30.00 | Fr. 45.00 | Fr. 145.00 |
| Total | Fr. 105.00 | Fr. 62.00 | Fr. 87.00 | Fr. 254.00 |
Here's how to create such a report:
In the Payments file, select a number of records created on different dates and with different payment methods. Then call up the File>Print command, click on the Create button, choose the Report format and validate.
In the Report Editor, click on the Pivot table report type in the top right-hand corner. The report area is filled with a table of 3 columns of 3 rows.
| A1 | B1 <Columns> | C1 |
| A2 <Rows> | B2 <Cells> | C2 |
| A3 | B3 | C3 |
For ease of reading, we'll identify the cells as in a spreadsheet, i.e. the columns are named A, B, C and the rows are numbered from 1 to 3. The middle cell is called B2.
Column A will contain the list of payment methods. Double-click on cell A2 and, in the formula editor, double-click on the Payment method field. Validate the formula editor. In cell A3, enter Total.
Line 1 will contain the dates. Double-click on cell B1 and, in the formula editor, double-click on the Date field. In cell C1, enter Total.
Double-click on cell B2 and, in the formula editor, double-click on the Cashed amount field. Select cell B2 and, with a secondary click, choose the Sum function from the contextual menu. The symbol ∑ is displayed in cell B2. Insert this symbol in cells C2, B3 and C3 in the same way. The report area now looks like this:
| [Payments]Date | Total | |
| [Payments]Payment method |
[Payments]Cashed amount ∑ Sum |
∑ Sum |
| Total | ∑ Sum | ∑ Sum |
In the Sorting order area, check the Columns sorting and Rows sorting boxes and radio buttons to define the sorting direction.
If you are satisfied with the preview, validate the report and give it a name. This will be displayed in the print/export dialog, and you will be able to reuse this report with a different content each time, depending on the records selected before the report was generated.
Français
Deutsch
Italiano