Pivot tables

Pivot tables allow you to organize, summarize, and analyze large amounts of data without the need for complex formulas. By grouping and segmenting data into dimensions (i.e., the fields added as columns and rows) and defining what is being measured (e.g., the total amount or quantity ordered), the corresponding values of each intersecting data point are calculated automatically.

Various manipulations can be performed to see the same dataset from different perspectives, such as rearranging or sorting dimensions, or changing how measures are aggregated or calculated.

Example

In the example, the pivot table shows the Total amount of sales orders per salesperson per quarter. The Salesperson field is represented in rows, the Order Date (grouped by quarter and year) in columns, while the values represent the total amount for the given salesperson and quarter.

With Odoo Spreadsheet, it is possible to:

• insert a pivot table from an Odoo pivot view into an Odoo spreadsheet, Odoo dashboard, or quote calculator spreadsheet.

• insert a pivot table referencing Odoo data directly from an Odoo spreadsheet. This option allows you to retrieve and work with data from any model, including models for which no Odoo pivot view is available e.g., the Sales Order Line model.

• insert a pivot table from a range of data. The data can be static data or dynamic Odoo data that had been inserted into the spreadsheet, e.g., from a list view.

When a pivot table is inserted in an Odoo spreadsheet, a data source is created, which connects the pivot table to the underlying database data or spreadsheet range.

Se även

Static vs dynamic pivot tables

Insert a pivot table from a range of data

Tips

Before inserting a pivot table from a range of data:

• Organize your data in columns, not rows, i.e., each column should contain one category of information, while each row should contain one record.

• Ensure all columns have a unique header; avoid double rows of headers or merged cells.

• Format your data as a table by selecting any cell in the range, clicking Insert table from the toolbar, then selecting a style. This ensures any updates to the table are reflected in the pivot table.

To create a pivot table from a range of data:

1. Open the relevant Odoo spreadsheet.

2. Select any cell within the range of data to be analyzed, then click Insert ‣ Pivot table From range from the menu bar.

A new sheet opens with an empty pivot table in the top-left corner of the sheet; the sheet name is the pivot table ID, e.g., Pivot #2. By default, the pivot table is a dynamic pivot table, where the top-left cell contains an Odoo-specific array function that retrieves data from the range of data once columns, rows, and measures have been added.

A panel on the right side of the spreadsheet shows the properties of the pivot table.

Se även

Build and manipulate a pivot table

Pivot table properties

When a pivot table is inserted into an Odoo spreadsheet, a properties panel opens to the right of the spreadsheet.

Tips

A pivot table’s properties panel can be opened at any time via the Data menu by clicking the relevant pivot table, as prefaced by the (pivot) icon, or by right-clicking anywhere on the relevant pivot table and clicking See pivot properties.

Depending on how the pivot table has been inserted, the following properties are shown, some of which can be edited:

• Pivot #: the pivot table ID.

  Observera

  A pivot table retains its ID for the lifetime of the spreadsheet. As well as being referenced at the top of the properties panel, this ID also identifies the pivot table in the Data menu. Pivot table IDs are assigned sequentially as additional pivot tables are inserted into the spreadsheet.

• Name: the name of the pivot table. Edit the name if needed. Note that editing the name in the pivot table properties does not modify the sheet name, and vice versa.

• Range: for a pivot table created from a range of data, the range used. Edit the range if needed.

• Model: for a pivot table referencing Odoo data that was inserted from an Odoo pivot view or inserted directly from the spreadsheet, the model from which the data is retrieved.

• Domain: for a pivot table referencing Odoo data that was inserted from an Odoo pivot view or inserted directly from the spreadsheet, the rules used to determine which Odoo records are shown. Click Edit domain to add or edit rules.

  Observera

  When referencing dynamic Odoo data in a pivot table and using global filters, this domain is combined with the selected values of the global filter before the data is loaded into the spreadsheet.

The Columns and Rows are the dimensions used to group or segment data, while the Measures define what is being measured, or analyzed, based on the selected dimensions.

To prevent real-time updates while building or manipulating a pivot table, enable Defer updates.

To duplicate or delete the data source of a pivot table, click the (gear) icon at the top of the properties panel, then Duplicate or Delete as relevant.

Se även

• Duplicate a pivot table

• Delete a pivot table

Build or manipulate a pivot table

After a pivot table has been inserted into an Odoo spreadsheet, it is possible to:

• add or manipulate dimensions

• add or manipulate measures

• edit the domain of a pivot table linked to an Odoo model

Viktigt

A pivot table inserted from an Odoo pivot view must first be converted to a dynamic pivot table to be able to manipulate the dimensions and measures.

Tips

By default, most manipulations of a pivot table are reflected in the pivot table data in real time. To prevent updates while you make changes, for example, if the dataset is very large, enable Defer updates at the bottom of the properties panel. When you have finished making changes, click Update (Apply all changes) or (Discard all changes).

Disabling Defer updates applies all the changes made since the option was enabled and restores real-time updating.

Dimensioner

The dimensions of a pivot table, i.e., fields added as columns and rows, represent the categories used to group or segment the data. Columns are typically used for fields that can provide a comparative view, e.g, Order Date grouped by quarter, while rows are typically used for fields that will return many values, e.g., Customer or Product.

It is possible to:

• add or remove dimensions

• rearrange dimensions

• sort dimensions

Add or remove dimensions

Adding or removing dimensions allows you to tailor the pivot table to your needs and control the level of granularity of the data.

To add a dimension to a pivot table:

1. Open the pivot table’s properties panel.

2. In the Columns or Rows section, as relevant, click Add.

3. Select the appropriate field.

4. Change the sorting, if desired.

Tips

• For date- or time-based dimensions, select the desired Granularity from the options in the dropdown menu.

• In a pivot table referencing Odoo data that was inserted from an Odoo pivot view or inserted directly from the spreadsheet, click the (right arrow) next to the field name to access the list of related fields when adding columns or rows.

To remove a dimension from a pivot table, click the (delete) icon on the dimension’s card.

Nested dimensions

Adding multiple row or column dimensions creates a nested hierarchy. To change the hierarchy of the dimensions, drag the dimension’s card to the desired position within its section.

Example

In the example, the field Product was added as a row dimension in addition to Saleperson to show products per salesperson. Switching the order of the dimensions then shows salesperson per product.

To collapse or expand a nested hierarchy in the pivot table, click the (expand) or (collapse) icon as relevant.

Rearrange dimensions

Rearranging the dimensions of a pivot table, i.e., moving a field from a column to a row, or vice versa, allows you to view the same dataset from different perspectives and potentially gain new insights.

To change the axis on which an individual dimension is shown:

1. Open the pivot table’s properties panel.

2. Drag the dimension’s card from the Columns section to the Rows section or vice versa.

To move all the dimensions represented in columns to rows and vice versa, at the same time:

1. Open the pivot table’s properties panel.

2. Click the (gear) icon.

3. Click Flip axes.

Observera

Depending on the volume of data, flipping the axes of a pivot table can result in a #SPILL error. This happens when a formula tries to output a range of values, but something is preventing cells from being populated, such as other data, merged cells, or the boundaries of the current sheet.

Hovering over the cell containing #SPILL provides details of the error.

Sort dimensions

Sorting dimensions allows you to organize pivot table data and more easily uncover the insights you need. Any dimension can be sorted by dimension value, while row dimensions can also be sorted by measure.

By dimension value

Dimension values are typically sorted in ascending alphabetical, chronological, or numerical order by default.

To change how a dimension is sorted by dimension value:

1. Open the pivot table properties.

2. On the card of the relevant dimension, in the Order by field, select Ascending, Descending, or Unsorted.

By measure

Row dimensions can be sorted by measure, e.g., to see the total amount of sales orders per salesperson per quarter in ascending order based on the total amount.

To sort row dimensions by measure:

1. Open the pivot table’s properties panel.

2. Right-click any value in the relevant column, then click Sort pivot and select Ascending or Descending.

To return to the default order, follow the same steps, then select No sorting from the dropdown.

Group dimension values

Grouping dimension values allows you to declutter your pivot table by combining multiple dimension values into a single, collapsible group. This can be useful, for example, for creating an Others category to group less significant values and allow focus to be placed on more significant data.

To group dimension values and create an Others category:

1. Open the pivot table’s properties panel.

2. Hold Ctrl then select the cells of the row or column dimension values that should be grouped.

3. Right-click one of the selected cells, then click Group pivot dimensions. A new dimension is added to the Columns or Rows section, as relevant. Change the sorting to Ascending or Descending.

4. On the card of the new dimension:

   • Change the Order by field from Unsorted to Ascending or Descending, as desired.

   • Click Groups to expand the sub-section.

   • Optionally, rename Group by clicking it and entering a new name.

   • Click + ”Others” group to have the unselected dimension values added to a single group that is then placed after the selected values.

To collapse or expand a dimension group in the pivot table, click the (expand) or (collapse) icon as relevant.

Example

In the example, data per salesperson for the secondary markets of Spain, France, Ireland and Italy has been grouped into an Others category, which can then be collapsed and its data seen beside that of the main markets of Belgium and the United States.

Mått

The measures of a pivot table represent what is being measured, or analyzed, and are typically fields such as Total, Quantity ordered, etc.

Tips

If the desired measure did not exist in the original data source, create a calculated measure, e.g., to show the average revenue per order.

It is possible to:

• add or remove measures

• rearrange measures

• change how measures are calculated and shown

Add or remove measures

To add a measure to a pivot table:

1. Open the pivot table’s properties panel.

2. In the Measures section, click Add.

3. Select the appropriate measure or click Add calculated measure to create and add a custom calculated measure.

4. Optionally, edit the name of the measure by clicking and then editing the measure’s name.

5. Optionally, change how the measure is aggregated by selecting a value from the dropdown.

To remove a measure from a pivot table, click the (delete) icon on the dimension’s card. To temporarily hide a measure instead of removing it, click the (Hide) icon; to make a previously hidden measure visible, click the (Show) icon.

Tips

• To simply display a count of the dimensions, rather than a quantifiable measure, select Count as the measure.

• In the pivot table, a measure’s label is shown in a second header row by default; hide this row if desired.

• The same measure can be added multiple times with different aggregations.

• If multiple measures are added, they are shown in columns in the order in which they are added; change the order if desired.

Create calculated measures

It is possible to add one or more calculated measures if the desired measure(s) did not exist in the original data source. For example, a calculated measure could be added to show the average revenue per order or the profit margin per product.

Observera

The fields needed for the calculation of the new measure must already have been added to the pivot table as dimensions or measures.

To add a calculated measure:

1. Open the pivot table’s properties panel.

2. In the Measures section, click Add.

3. Below the scrollable list, click Add calculated measure.

4. Rename the calculated measure by clicking on the name and typing.

5. Click on the line starting with =, then enter the relevant formula.

   Tips

   Functions can be used in the formula of a calculated measure.

6. Choose how the measure should be aggregated by selecting a value from the dropdown.

Example

In the example, the average revenue per order is calculated by dividing the sum of the sales by the number of orders.

Rearrange measures

In the pivot table, measures are shown in columns in the order in which they were added.

To change the order in which measures are shown:

1. Open the pivot table’s properties panel.

2. Drag the measure’s card to the desired position.

’Show measure as’

By default, measures are shown as the actual returned value, with no additional calculations performed. However, it is also possible to show measures in relation to other data, e.g., as a % of grand total or ranked from smallest to largest.

To change how a measure is shown:

1. Open the pivot table’s properties panel.

2. On the measure’s card, click (Show values as) icon.

3. Select the desired option from the dropdown menu.

4. Klicka på Spara.

Observera

When changing how the measures are shown, the pivot table data updates in real time even if Defer updates is enabled.

Remove measure titles

By default, measure titles are shown in a second header row in a pivot table.

To remove this header row:

1. Open the pivot table’s properties panel.

2. Double-click the top left cell of the pivot table to be able to edit the formula.

3. Position your cursor after the pivot ID then type , to advance to [include_measure_titles], then type 0.

The row showing the measure titles is removed.

Tips

In a static pivot table inserted from an Odoo pivot view, delete the second header row manually, if desired.

Duplicate or delete a pivot table

Observera

When duplicating or deleting a pivot table, it is important to remember that each inserted pivot table also has a data source.

Duplicate a pivot table

Duplicating a pivot table via the pivot table’s properties creates an additional data source. This allows for different manipulations to be performed on the same data within one spreadsheet. For example, you can see the same data aggregated by different dimensions or use global filters to offset the date and create pivot tables that compare the current period’s data with a previous period.

To duplicate a pivot table:

1. With the pivot table properties open, click the (gear) icon then Duplicate.

   The duplicated pivot table is automatically inserted into a new sheet in the spreadsheet, with the pivot table properties open in the right panel.

2. Edit the Name in the properties panel and the sheet tab if needed.

The new data source is assigned the next available pivot table ID. For example, if no other pivot tables have been inserted in the meantime, duplicating Pivot #1 results in the creation of Pivot #2.

Observera

• Duplicating a pivot table by copying and pasting it or by duplicating the sheet does not create a new data source. Any changes made to the pivot table’s properties would therefore impact any copies of the pivot table.

• When a pivot table is duplicated, the new pivot table is, by default, a dynamic pivot table whose dimensions and measures can be manipulated.

Delete a pivot table

To fully delete a pivot table and its data source from a spreadsheet, follow these steps in any order:

• Delete the pivot table using your preferred means, e.g., via keyboard commands, spreadsheet menus, or by deleting the sheet.

• From the properties panel of the relevant pivot table, click the (gear) icon then Delete. This deletes the data source of the pivot table.

Observera

Deleting the pivot table first results in a warning message beside the corresponding data source in the Data menu, while deleting the data source first results in a pivot table filled with #ERROR errors.