插入数据透视表¶
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.
注解
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, …])
函数参数说明如下：
pivot_id: the ID assigned when the pivot table is inserted. The first pivot table inserted in a spreadsheet is assigned pivot ID1, the second, pivot ID2, etc.domain_field_name：用作维度的字段技术名称，例如`user_id`；若维度是时间段，则为日期字段技术名称后跟时间段，例如`date_order:month`。measure_name：被测量指标的技术名称，后跟聚合类型，例如`product__qty:sum`。domain_value：记录的ID；若维度是时间段，则为目标日期或时间段。
小技巧
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 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.
注解
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 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.
小技巧
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), where1is the pivot table ID. However, with this method, the table styling needs to be re-applied manually.注解
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 ID1, the second, pivot ID2, etc.row_countandcolumn_count: set a value to limit the number of rows and columns respectively.include_total: set a value of0to hide the totals; this can be useful when creating a graph.include_column_titlesandinclude_measure_titles: set a value of0to remove the column and measure titles respectively.
小技巧
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 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.
小技巧
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:
在数据库中打开相关透视图后，点击 在电子表格中插入。
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.
Click Blank spreadsheet to create a new spreadsheet, or select in which existing spreadsheet the pivot table should be inserted.
注解
When inserting a pivot table into a new spreadsheet, the spreadsheet is saved in the Odoo Documents app in the My Drive personal folder.
点击 确认。
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.
小技巧
To allow the pivot table to be manipulated, convert it to a dynamic pivot table.
要切断插入的数据透视表和数据库之间的联系，请选择整个数据透视表，点击右键并选择 :
guilabel:`复制，然后再次点击右键并选择 。
Insert a pivot table that retrieves Odoo data from a spreadsheet¶
To insert a pivot table using Odoo data directly from an Odoo spreadsheet:
Open the relevant Odoo spreadsheet from the Documents app or create a new blank spreadsheet.
Click from the menu bar.
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.