Inserează un tabel pivot

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.

Notă

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, …])

Argumentele funcțiilor sunt următoarele:

• pivot_id: ID-ul atribuit atunci când tabelul pivot este inserat. Primul tabel pivot inserat într-o foaie de calcul primește ID-ul pivot 1, al doilea, ID-ul pivot 2 etc.

• domain_field_name: denumirea tehnică a câmpului folosit ca dimensiune, de exemplu, user_id, sau, dacă dimensiunea este o perioadă de timp, denumirea tehnică a câmpului de dată urmată de perioada de timp, de exemplu, date_order:month.

• measure_name: denumirea tehnică a ceea ce se măsoară, urmată de tipul de agregare, de exemplu, product_uom_qty:sum.

• domain_value: ID-ul înregistrării sau, dacă dimensiunea este o perioadă de timp, data sau perioada de timp vizată.

Sfat

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.

  Se creează o nouă sursă de date și o versiune dinamică a tabelului pivot este inserată într-o nouă foaie. Tabelul pivot dinamic are același stil ca tabelul pivot original.

  Notă

  Când utilizați această metodă, noul tabel pivot dinamic primește următorul ID pivot disponibil. Aceasta înseamnă că puteți crea mai multe vizualizări pivot asociate aceluiași model, dar cu setări, grupări sau calcule distincte.

• Reinserați tabelul pivot dinamic din meniul Date: Pe foaia care conține tabelul pivot static, poziționați cursorul într-o celulă goală. Faceți clic pe Date ‣ Reinserați tabel pivot dinamic din bara de meniu, apoi selectați tabelul pivot relevant.

  Va apărea un nou tabel pivot dinamic, cu același stil ca tabelul pivot original.

  Sfat

  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.

  Notă

  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: ID-ul atribuit atunci când tabelul pivot este inserat. Primul tabel pivot inserat într-o foaie de calcul primește ID-ul pivot 1, al doilea, ID-ul pivot 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.

Sfat

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.

Sfat

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. Cu vizualizarea pivot relevantă deschisă în baza de date, faceți clic pe Inserează în tabel.

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.

   Notă

   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. Faceți clic pe 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.

Sfat

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

• Pentru a întrerupe legătura dintre un tabel pivot inserat și baza de date, selectați întregul tabel pivot, faceți clic dreapta și selectați Copiază, apoi faceți din nou clic dreapta și selectați Lipire specială ‣ Lipește ca valoare.

Vedeți și

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.

Vedeți și

Build and manipulate a pivot table