A PivotTable can be created from imported MySQL tables, views, stored procedures, or the entire Excel Data Model.
This feature was added in MySQL for Excel 1.3.0.
An Excel PivotTable report summarizes and provides a visual representation of data in many different ways. It is a native Excel feature. For instructions on how to use PivotTables, see the documentation provided by Microsoft.
The following example shows a simple use case where an empty PivotTable is created from an imported MySQL table. This example uses the film table of the sakila database. To create the PivotTable, select the film table from the task pane and then click Import MySQL Data. In the Import Data dialog, select the Create a PivotTable check box and click to execute the operation.
When the Create a PivotTable with the imported data option is checked, an empty PivotTable (or a PivotTable placeholder) is inserted just to the right of the imported data. The PivotTable name follows the same naming rules used for Excel tables created from the imported data, but PivotTables can be created with or without enabling the Create an Excel table for the imported MySQL data advanced option. That means a PivotTable can be created from an imported Excel range (if the aforementioned advanced option is off), or from an imported Excel table (if the option is on).
Clickto dump the film table data to an active Excel worksheet, and this also creates a PivotTable for that data.
Clicking the PivotTable opens a PivotTable Fields window next to the MySQL for Excel task pane, and from here you can select fields you want to summarize in the PivotTable report. Drag and drop fields from the list to any of the FILTERS, COLUMNS, ROWS, or VALUES areas, depending on the visualizations you want in the report. The report is completely dynamic, meaning that you can change the views by moving fields around the areas until you see the visualization you need for your PivotTable report.
Below is an example PivotTable report using the sakila.film table imported in the previous example. This report includes a filter by release_year and it summarizes the rental_rate values while also grouping the data by values in the rating column.
Expanding one of the groups reveals its values from the title and description columns.
The same operation can be performed with data coming from a MySQL view or stored procedures. One difference for stored procedures enables you to create a PivotTable for each of the imported result sets returned by the procedure call.
In the following example, the film_in_stock stored procedure is selected and its input parameter values are configured, When the procedure is called, it returns one result set (Result1) and the OutAndReturnValues table (always present if the procedure has output parameters or a return value).
In this example, the All Result Sets - Arranged Horizontally option was selected. Because the Create a PivotTable with the imported data option was also checked, a PivotTable was created for each returned result set.
An important use case for PivotTables is when we create it for multiple related tables as typically a single table does not contain all of the data needed by a PivotTables report. You can create a single PivotTable tied to the data in the current Excel Data Model that contains fields from several related tables. That way you can use the data in a single report for an entire MySQL schema if needed. However, you can only do this in Excel 2013 (and later) where the Excel Data Model is available.
In Excel versions before Excel 2013, only a PivotTable for each imported table or view can be created. This is because a single PivotTable for the entire Excel Data Model requires that the tables are related to each other. If Excel relationships cannot be created, then this type of PivotTable cannot be created. In these cases, the Import Data dialog looks similar to the figure that follows.
Clicking Why is this option disabled? displays an information dialog with an explanation of the disabled controls.
Our next example uses all tables in our schema. You can manually choose each table or use Control + A in the database objects list to select them all. When clicking , the Import Data dialog appears as shown below. We need to check the Create a PivotTable option, which by default its drop-down is set to for all the tables in the data model. Keep that value.
When clicking PivotTable Fields window., the data in all of the selected tables is imported to Excel, its Data Model and Excel relationships are created, and a new worksheet is created that contains a PivotTable with all of the tables that were imported. This is demonstrated in the screenshot below, and notice that all tables are listed in the
You can also configure the Create a PivotTable drop-down list the for each imported table or view, which in turn will create a PivotTable for each of the imported tables or views, as opposed to creating a single PivotTable for all of them.