MySQL for Excel Guide  /  Import MySQL Data into Excel  /  Creating PivotTables

5.4 Creating PivotTables

A PivotTable can be created from imported MySQL tables, views, stored procedures, or the entire Excel Data Model.

Note

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 5.9 Option: Create a PivotTable with the imported data

Content is described in the surrounding text.

When the Create a PivotTable with the imported data option is checked (see the previous figure), 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 as shown in the follow figure.

Figure 5.10 PivotTable Example: Empty PivotTable

Content is described in the surrounding text.

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.

The next figure shows 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.

Figure 5.11 PivotTable Example: Film Ratings

Content is described in the surrounding text.

Expanding one of the groups reveals its values from the title and description columns as shown in the figure that follows.

Figure 5.12 PivotTable Example: Expanded Group

Content is described in the surrounding text.

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 figure, 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 5.13 PivotTable Example: Stored Procedure

Content is described in the surrounding text.

In the next figure, 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 5.14 PivotTable Example: Arranged Horizontal

Content is described in the surrounding text.

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 5.15 Disabled Create Excel relationships option before Excel 2013

Content is described in the surrounding text.

Clicking Why is this option disabled? displays an information dialog (see the next figure) with an explanation of the disabled controls.

Figure 5.16 Disabled Create Excel relationships option description

Content is described in the surrounding text.

The next figure shows an example that uses all tables in the schema. You can choose each table manually 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 in the example. Confirm that the Create a PivotTable check box is selected and is set to for all the tables in the data model. Keep that value.

Figure 5.17 Importing All Tables and Views

Content is described in the surrounding text.

When clicking Import, the data in all of the selected tables are imported to Excel, the 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 combination is demonstrated in the figure that follows. Note that all tables are listed in the PivotTable Fields window.

Figure 5.18 Importing All Tables and Views: Listing

Content is described in the surrounding text.

You can also configure the Create a PivotTable drop-down list the for each imported table or view (see the next figure), which in turn creates a PivotTable for each of the imported tables or views, as opposed to creating a single PivotTable for all of them.

Figure 5.19 Importing Each Imported Table or View

Content is described in the surrounding text.


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