Item Pivot Tables

If you need to build a pivot table for a specific record, "Item Pivot Tables" report will help you.

The complexity of this report is that it requires an understanding of how tables (entities) are related in the database and you need to have experience building MySql queries, since all calculations are based on user queries.

This report is created in 3 stages:

  • Step 1: creating a report
  • Step 2: creating calculations
  • Step 3: create columns for the pivot table

Creating report 

To create a report, go to the "Extension - Reports - Pivot Tables - Item Pivot Tables" page and click the "Create" button.

The report form has three required fields:

  1. Entity - select the entity for which the pivot report will be displayed.
  2. Select data from - select the entity from which the data will be selected.
  3. Fields  - are the fields by which data will be grouped.

Creating calculations

After creating the report, click on its name and you will be taken to the report settings page where you need to create calculations and columns. In the "Calculations" section, click the "Create" button.

In the "Select field" field, you must enter a MySQL formula to select a value. In most cases, it will be SUM or COUNT. Then, in the "Condition" section, specify the conditions for which the data will be selected. The following constructions can be used in the condition:

  • [current_item_id] - id of the current record on the page where the report is displayed.
  • [field_261_value] - the value of the field from the pivot table by which the data is grouped.
  • [261] - field value in the database table.

Note: that if you select data from a nested entity, the program allows you to add conditions for all parent entities.

Creating columns

After creating the calculations, we need to create columns, the values of which will be displayed in the pivot table. In the column form, in the Formula field, you must specify the ID of the created calculation. For example: {14}.

Also in the formula we can use mathematical operations with calculations, for example: {14}-{15}

Please note: the formula is allowed to use PHP syntax to output values, for example:

({13}>5 ? '<font color="red">' . ({13}+1) . '</font>' : {13})

See our video review for an example of creating and detailed settings for this report: