In a previous blog post (Importing related MySQL tables into an Excel Data Model using MySQL for Excel) we covered in detail how an Excel Data Model can be created containing tables and their relationships so the data can be analyzed in Excel via a PivotTable. In this blog post we are going to talk about one of the features included since MySQL for Excel 1.3.0 that allows you to create PivotTables for data imported from MySQL tables, views or stored procedures, or more importantly for the whole Excel Data Model if it is created.
You may have already worked with Excel PivotTable reports (or PivotTables for short), which are dynamic tables used to summarize and visualize data in different ways. This article assumes you are familiar with the basics of PivotTables, if not you may want to read this article: PivotTable reports 101. We will start with the simplest case, where you import a MySQL table or view and from its data create a blank PivotTable.
Let's say we want to create a PivotTable for the film table, we select it from the database objects selection panel and click on Import MySQL Data. On the Import Data dialog we must check the Create a PivotTable with the imported data option.
When the Create a PivotTable with the imported data option is checked, an empty PivotTable (or a PivotTable placeholder) is inserted just at the right of the imported data. The name given to the PivotTable follows the same naming rules used for Excel tables created from imported data; but PivotTables can be created regardless of the setting of 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).
When we click Import, the film table's data is dumped to the active Excel worksheet, and a PivotTable is created for that data as you can see in the screenshot below.
Clicking the PivotTable will cause the PivotTable Fields panel to appear next to the MySQL for Excel one, from it you can select fields you want to summarize in the PivotTable report. It is as simple as dragging&dropping fields from the list to any of the FILTERS, COLUMNS, ROWS or VALUES areas, depending on the visualization you want to give to the report. The report will be completely dynamic, meaning that you can change the views by moving fields around the areas until you get exactly the visualization you need for your PivotTable report.
You can see below a sample PivotTable report using the film table we imported in our previous example. Note the report has a filter by release_year and summarizes the rental_rate values, while grouping the data by values in the rating column.
Expanding one of the groups will reveal the values from the title and description columns.
We can do exactly the same with data coming from a MySQL view or even a stored procedure. The only difference is that for stored procedures we can create a PivotTable for each of the imported result sets returned by the procedure's call. Take the following screen as an example, we have the film_in_stock store procedure selected, we filled its input parameter values and called the procedure. You can see the procedure returned one result set (Result1) and the OutAndReturnValues table (always present if the procedure has output parameters or a return value).
Note that we selected to import All Result Sets - Arranged Horizontally, and since the Create a PivotTable with the imported data option is checked as well, a PivotTable will be created for each returned result set. You can see it in the example below.
Maybe the most important use for PivotTables is when we create it for multiple related tables, since normally a single table does not contain all the data we need in a report. You can create a single PivotTable tied to the data in the current Excel Data Model (again, refer to the Importing related MySQL tables into an Excel Data Model using MySQL for Excel article for more information) that contains fields from several related tables. That way you can crunch data in a single report for a whole MySQL schema if needed. Bear in mind you can only do this in Excel 2013 (and later) where the Excel Data Model is available.
If a lower (than 2103) Excel version is used, only a PivotTable for each imported table or view can be created, the reason for this is that a single PivotTable for the whole Excel Data Model requires that the tables are related among each other. If no Excel relationships can be created, then this type of PivotTable cannot be created either. So the Import Data dialog would look like the following sample screenshot.
If the Why is this option disabled? link is clicked, an information dialog with an explanation of the disabled controls will be displayed.
Let's do an example of that, where we select all tables in our schema (using <CTRL>+<A> in the database objects list), when clicking Import Multiple Tables and Views, the Import Data dialog appears as you can see below. We need to check the Create a PivotTable option; note that by default its drop-down is set to for all the tables in the data model., which is what we need for our example.
When clicking Import, the data in all the selected tables will be imported to Excel, its Data Model will be created, Excel relationships will be created and in a new worksheet a PivotTable will be created containing all the tables that were imported. You can see that in the sample screenshot below, note all the tables are listed in the PivotTable Fields panel.
You an also choose from the Create a PivotTable drop-down list the for each imported table or view. value, which in turn will create a PivotTable for each of the imported table or view as opposed to creating a single PivotTable for all of them.
As you can see, creating PivotTable reports to analyze data from a MySQL database has never been so easy. MySQL for Excel cuts several cumbersome steps that you would need to do if you extract the data from the MySQL database using another tool. We hope you give this and the other new features in the 1.3.x version family a try!
Remember that your feedback is very important for us, so drop us a message and follow us: