Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt

MySQL and Windows  /  Import MySQL Data into Excel  /  Creating PivotTables

9.6 Creating PivotTables

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 OK to execute the operation.

Figure 9.10 Option: Create a PivotTable with the imported data

Option: Create a PivotTable with the imported data

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).

Click Import to dump the film table data to an active Excel worksheet, and this also creates a PivotTable for that data.

Figure 9.11 PivotTable Example: Empty PivotTable

PivotTable Example: Empty PivotTable

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 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.

Figure 9.12 PivotTable Example: Film Ratings

PivotTable Example: Film Ratings

Expanding one of the groups reveals its values from the title and description columns.

Figure 9.13 PivotTable Example: Expanded Group

PivotTable Example: Expanded Group

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).

Figure 9.14 PivotTable Example: Stored Procedure

PivotTable Example: Stored Procedure

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.

Figure 9.15 PivotTable Example: Arranged Horizontal

PivotTable Example: Arranged Horizontal

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.

Figure 9.16 Disabled Create Excel relationships option before Excel 2013

Deselected Create Excel relationships option before Excel 2013

Clicking Why is this option disabled? displays an information dialog with an explanation of the disabled controls.

Figure 9.17 Disabled Create Excel relationships option description

Deselected Create Excel relationships option description

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 Import Multiple Tables and Views, 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.

Figure 9.18 Importing All Tables and Views

Importing All Tables and Views

When clicking Import, 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 PivotTable Fields window.

Figure 9.19 Importing All Tables and Views: Listing

Importing All Tables and Views: Listing

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.

Figure 9.20 Importing Each Imported Table or View

Importing For Each Imported Table or View

User Comments
Sign Up Login You must be logged in to post a comment.