Jumbabox

This tutorial will run through how you use an Excel Pivot Table with a MySQL database, before we begin please check out my Configuring your MySQL ODBC driver as this takes you through a step by step guide on how to set up an ODBC connection (for MySQL), if you have already done this and your connection has tested successfully - lets move on!

Related Posts:

• Excel Pivot Table with a MySQL database
• Excel Pivot Table with an Oracle database
• Excel Pivot Table with an Access database

Step 1 - Setting up your Pivot Table

Open up a blank Excel workbook and click on the Data > PivotTable and PivotChart report… option.

This will open up the PivotTable and PivotChart wizard, the first option is to select where is the data you want to analyze?, click on the External data source option and click the Next button.

The next step will ask you where your external data is stored?, click on the Get Data… button. You can see that No data fields have been retrieved.

Step 2 - Picking you MySQL data source

Once you have clicked on the Get Data… button, a Choose data source dialog box will pop up, this will ask you what data source you would like your Pivot Table to be based on, locate your MySQL data source and click OK. As you can see from my previous post I have already created one called JUMBABOX_MYSQL.

If you get an error in relation to your data source containing no “visible tables”, scroll down to the bottom of this post for a solution to this problem

After selecting your MySQL data source and have clicked OK you should see a new dialog box appear which will show you all available tables you can build your Pivot Table on, in this example I have only setup one table called sample_table. Clicking on the + sign next to the table will show you what columns are available.

You can move or remove fields from the Colums in your query area by using the > >> < buttons. I have decided to select everything in my sample_table. Once you are happy with the columns you want available in your Pivot Table, click on the Next > button.

The next two steps in the wizard will ask you if you want to Filter your query and then Sort your query. I hardly fill out these steps as I know I can perform all my filtering and sorting using the native Pivot Table functions, however, if you are working with a large dataset it is wise to do some pre-filtering.

Filter Data

Sort Data

After clicking the Next > button through these two steps, you will get the dialog box below, make sure you check the Return data to Microsoft Excel radio button and click the Finish button.

The next dialog box will let you know that Data fileds have been retrieved. Click on the Next > button to continue.

Step 3 - Finialising your Pivot Table

The following steps are standard steps which you have seen before when setting up a Pivot Table.

Choose whether you would like to put your Pivot Table in an existing or new worksheet and click the Finish button.

Finished! You have sucessfully setup a Pivot Table with a MySQL database!

An encountered Problem and the Solution

If you recieved an error (as seen below) regarding the data source containing no visible tables, you most likely recieved the error below because you have:

• Not specified a database in your MySQL ODBC Driver configuration
• No tables in the specified MySQL database

I knew I had already setup a sample table in my MySQL database so the reason why I was getting the above error is due to having no database specified in my MySQL ODBC Driver configuration (as seen below).

After selecting an appropriate database from the database dop down box, I tested if the connection was successful and then clicked the OK button.

This should of fixed the error message, however, you will need to start from step 1 again when setting up your Pivot Table. If you still receive the error make sure you check that within the specified database you have setup tables.

If you have any suggestions or problems please comment.

Enjoy!