Insertar una tabla dinámica¶
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.
Nota
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, …])
Los argumentos de las funciones son los siguientes:
pivot_id: El ID asignado al insertar la tabla dinámica. La primera tabla dinámica insertada en una hoja de cálculo recibe el ID1, la segunda el ID2y así sucesivamente.domain_field_name: El nombre técnico del campo utilizado como dimensión, por ejemplo,user_id, o el nombre técnico del campo de fecha, seguido del periodo de tiempo, si la dimensión es un periodo de tiempo, por ejemplo,date_order:month.measure_name: El nombre técnico de lo que está midiendo seguido del tipo de agregación, por ejemplo,product_uom_qty:sum.domain_value: El ID del registro o, si la dimensión es un período de tiempo, la fecha o el periodo de tiempo objetivo.
Truco
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.
Esto crea una nueva fuente de datos e inserta una versión no estática de la tabla en una nueva hoja. Esta tendrá el mismo estilo que la tabla original.
Nota
Al utilizar este método, la nueva tabla dinámica obtiene el siguiente ID de tabla dinámica disponible. Esto significa que puede crear varias vistas de tabla dinámica asociadas con el mismo modelo, pero con diferentes ajustes, grupos o cálculos.
Volver a insertar la tabla dinámica no estática desde el menú Datos: Coloque el cursor en una celda vacía en la hoja que contiene su tabla dinámica estática. Haga clic en en la barra de menú y luego seleccione la tabla dinámica correspondiente.
Aparecerá una nueva tabla dinámica no estática con el mismo estilo que la original.
Truco
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.Nota
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: El ID asignado al insertar la tabla dinámica. La primera tabla dinámica insertada en una hoja de cálculo recibe el ID1, la segunda el ID2y así sucesivamente.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.
Truco
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.
Truco
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:
Haz clic en Insertar en hoja de cálculo una vez que hayas abierto la vista de tabla dinámica correspondiente en tu base de datos.
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.
Haz clic en Hoja de cálculo en blanco para crear una nueva hoja de cálculo o selecciona en cuál hoja existente insertar la tabla dinámica.
Nota
Cuando insertas una tabla dinámica en una nueva hoja, esta queda almacenada en la carpeta personal Mi unidad dentro de la aplicación Documentos de Odoo .
Haga clic en Confirmar.
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.
Truco
To allow the pivot table to be manipulated, convert it to a dynamic pivot table.
Para deshacer el vínculo entre una tabla dinámica insertada y tu base de datos, selecciona toda la tabla, haz clic derecho y selecciona Copiar, luego vuelve a hacer clic derecho y selecciona .
Consulta
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.