Insert a pivot table

There are two main ways to integrate live Odoo data into a pivot table in Odoo Spreadsheet:

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

• Insert a pivot table that retrieves Odoo data directly from an Odoo spreadsheet. This option allows you to pull data from any model, including models for which no pivot view is available, e.g., the Sales Order Line model.

In both cases, the pivot table is connected to the underlying database data thanks to its unique data source, which keeps the data up-to-date and allows you to access the underlying data directly from the spreadsheet. However, it is important to understand the difference between static and dynamic pivot tables in Odoo.

Catatan

It is also possible to create a pivot table from a range of data already present in a spreadsheet. This could be a range of static, manually entered data or a range of Odoo data inserted into the spreadsheet, e.g., from an Odoo list view.

Static vs dynamic pivot tables

When a pivot table from an Odoo pivot view is inserted into an Odoo spreadsheet, it is, by default, a static pivot table, where each cell retrieves data from your database via an Odoo-specific function. The pivot table's data is kept up to date, but it does not expand to accommodate new data, e.g., sales data for a new quarter or for a newly hired salesperson, and its dimensions and measures cannot be manipulated.

A static pivot table can be converted to a dynamic pivot table to allow the pivot table to evolve with your business and be manipulated to gain different and deeper insights.

Inserting a pivot table that retrieves Odoo data directly from an Odoo spreadsheet results in a dynamic pivot table, where a single Odoo-specific array function retrieves data from your database. The pivot table's data is kept up to date, it expands for new data, and its dimensions and measures can be manipulated. A pivot table inserted from a range of data is also a dynamic pivot table.

If needed, there are various ways to see the individual functions of a dynamic pivot table.

Static pivot table functions

A static pivot table uses the following Odoo-specific functions to retrieve the header and field values, respectively, via its data source:

=PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …])
=PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …])

The arguments of the functions are as follows:

• pivot_id: the ID assigned when the pivot table is inserted. The first pivot table inserted in a spreadsheet is assigned pivot ID 1, the second, pivot ID 2, etc.

• domain_field_name: the technical name of the field used as a dimension, e.g., user_id, or, if the dimension is a time period, the technical name of the date field, followed by the time period, e.g., date_order:month.

• measure_name: the technical name of what is being measured, followed by the type of aggregation, e.g., product_uom_qty:sum.

• domain_value: the ID of the record, or, if the dimension is a time period, the date or time period targeted.

Tip

Clicking on an individual cell displays the related formula, if relevant, in the formula bar. To display all the formulas of a spreadsheet at the same time, click View ‣ Show ‣ Formulas on the menu bar. The example below shows the function used to retrieve the total amount of sales for the salesperson Anita Rodman for Q2 2024.

Convert a static pivot table to a dynamic pivot table

To manipulate the dimensions and measures of a pivot table that has been inserted from an Odoo pivot view, it first has to be converted to a dynamic pivot table.

There are two main ways to do so:

• Duplicate the static pivot table from the pivot table properties: Open the pivot table's properties panel, click the (gear) icon at the top right of the pane, then click Duplicate.

  A new data source is created and a dynamic version of the pivot table is inserted into a new sheet. The dynamic pivot table has the same styling as the original pivot table.

  Catatan

  When you use this method, your new dynamic pivot table gets the next available pivot ID. This means you can create multiple pivot views associated with the same model, but with distinct settings, groupings, or calculations.

• Re-insert the dynamic pivot table from the Data menu: On the sheet that contains your static pivot table, position your cursor in an empty cell. Click Data ‣ Re-insert dynamic pivot from the menu bar, then select the relevant pivot table.

  A new, dynamic pivot table appears, with the same styling as the original pivot table.

  Tip

  It is also possible to re-insert the dynamic pivot table by entering the function of the dynamic pivot table in an empty cell, e.g., =PIVOT(1), where 1 is the pivot table ID. However, with this method, the table styling needs to be re-applied manually.

  Catatan

  When you re-insert the dynamic pivot table from the data menu or via the dynamic pivot table function, your static and dynamic pivot share the same data source, and, consequently, the same pivot ID. To avoid confusion, delete the original static pivot table.

The top-left cell of the new dynamic pivot table contains an Odoo-specific array function that retrieves data from your your database.

Dynamic pivot table function

Instead of each cell containing a unique function that retrieves data via the data source, as in a static pivot table, a dynamic pivot table has a single Odoo-specific array function:

=PIVOT(pivot_id, [row_count], [include_total], [include_column_titles], [column_count],
 [include_measure_titles] )

This function allows the pivot table to expand automatically to accommodate the results of the function. The arguments of the function are as follows:

• pivot_id: the ID assigned when the pivot table is inserted. The first pivot table inserted in a spreadsheet is assigned pivot ID 1, the second, pivot ID 2, etc.

• row_count and column_count: set a value to limit the number of rows and columns respectively.

• include_total: set a value of 0 to hide the totals; this can be useful when creating a graph.

• include_column_titles and include_measure_titles: set a value of 0 to remove the column and measure titles respectively.

Tip

To set a value for an argument of the function, with the function open in the formula bar or the top-left cell of the pivot table, position your cursor after the pivot ID then type , to advance to the argument you want to modify. In the example below, adding the value 0 for [include_total] removes both the row total and column total from the pivot table.

Convert a dynamic pivot table to a static pivot table

There are two main ways to convert a dynamic pivot table to a static pivot table, where each cell has an individual function that retrieves data from the data source:

• Re-insert the static pivot table from the Data menu: On the sheet that contains your dynamic pivot table, position your cursor in an empty cell. Click Data ‣ Re-insert static pivot from the menu bar, then select the relevant pivot table.

  A new static pivot table appears, with the same styling as the original pivot table. Click on a cell to see the function in the formula bar.

• Use the 'Convert to individual formulas' option: Right-click on any cell in the pivot table, then click Convert to individual formulas. Every populated cells is replaced by an individual function; click on a cell to see the function in the formula bar.

Tip

To see the individual functions behind specific cells of a dynamic pivot table, copy the relevant cells and paste them elsewhere in the spreadsheet; click on a pasted cell to see the function in the formula bar.

Insert a pivot table from a pivot view

To insert a pivot table from an Odoo pivot view into an Odoo spreadsheet:

1. With the relevant pivot view open in your database, click Insert in Spreadsheet.

2. In the window that opens, edit the Name of the pivot if needed. This name is used as the sheet name and in the pivot table properties, and can be edited later, if needed.

3. Click Blank spreadsheet to create a new spreadsheet, or select in which existing spreadsheet the pivot table should be inserted.

   Catatan

   When inserting a pivot table into a new spreadsheet, the spreadsheet is saved in the Odoo Documents app in the My Drive personal folder.

4. Click Confirm.

The pivot table is inserted into a new sheet in the spreadsheet; the sheet name is the pivot table name followed by the pivot table ID, e.g., Sales Analysis by Sales Team (Pivot #1). By default, the pivot table is a static pivot table, where each cell contains an Odoo-specific function that retrieves data from your database.

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

Tip

• To allow the pivot table to be manipulated, convert it to a dynamic pivot table.

• To sever the link between an inserted pivot table and your database, select the entire pivot table, right-click and select Copy, then right-click again and select Paste special ‣ Paste as value.

Lihat juga

Manipulate a pivot table

Insert a pivot table that retrieves Odoo data from a spreadsheet

To insert a pivot table using Odoo data directly from an Odoo spreadsheet:

1. Open the relevant Odoo spreadsheet from the Documents app or create a new blank spreadsheet.

2. Click Insert ‣ Pivot table From Odoo data from the menu bar.

3. In the New Odoo Pivot panel at the right of the sheet, select the Model from which data should be retrieved, then click Save.

A new sheet opens with an empty pivot table in the top-left corner of the sheet; the sheet name is the model name followed by the pivot table ID, e.g., Sales Order (Pivot #1). 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 your database once columns, rows, and measures have been added.

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

Lihat juga

Build and manipulate a pivot table