Jumbabox

This tutorial will look at building an interactive (drag and drop, filter etc.) Pivot Chart into your PowerPoint presentation using Microsoft Office Web Components.

Related : Building a Pivot Table into your PowerPoint presentation

Step 1 - How does it work?

With this method you can only build a Pivot Table or Pivot Chart in a PowerPoint presentation based on an Access database table (dataset) which is linked to an Excel sheet.

Pivot Chart <–Microsoft Office Web Components– PowerPoint <–OLE Db Connection– Access <–linked– Excel Sheet

You will notice that I have linked an Excel worksheet to the Access database - this is so the whole process is driven by the Excel worksheet (you may just want to work with your data in Access if so skip Step 2).

Pivot Charts and Pivot Tables are only interactive in full screen or presentation mode - a big flaw to this functionality.

Step 2 - Setting up your Access database

First create a new Access database or open up the one you wish you want to build your Pivot Chart on, as mine is new I will need to input some data. I will not be importing data as I would like to keep the whole process between Pivot Chart - PowerPoint - Access dynamic so I will be linking my dataset from Excel as a table in Access.

To link an Excel worksheet as a table in Access, right click in the Tables area, then click on the Link Tables… option.

This should display a Link dialog box, in the File Name: drop down box I have selected the Microsoft Excel (*.xls) option so that I can find my Excel worksheet to link.

Once you have found the Excel file which contains the data you want to build your Pivot Table on, click the Link button.

After this a Link Spreadsheet Wizard will pop up, the first step will ask you which sheet (in your Excel workbook) you would like to link. I have labelled the sheet which I want to link, data - the wizard will also show a preview of the data (see below screenshot).

Once you have selected the correct sheet click the Next button, you will now be asked if the First Row Contains Headings, in this example it does, so I have checked the check box, this then reflects in the preview window of the wizard as well. Click the Next > button to continue.

The last step is to label the name of the linked sheet, I will be calling mine data_tbl, once you have picked an appropiate name click the Finish.

A confirmation box will pop up letting you know if the linking was succesful, click OK to continue.

Now you should be able to see your linked sheet in the Tables area.

Step 3 - Setting up your Pivot Chart in PowerPoint

Open up your PowerPoint presentaton and make sure the Control Toolbox is visible, if it isn’t right click in the top area of PowerPoint and click the Control Toolbox option.

You should be able to see the Control Toobox now.

Click on the More.. button found on the Control Toolbox toolbar.

This will open a up a list of objects which you can insert into your PowerPoint presentation, for this example scroll down till you find the Microsoft Office Chart 10 or 11 (it doesn’t matter which version you pick).

After selecting your object you mouse pointer will turn into a cross.

Click and drag the cross to the size of the Pivot Chat. Within this area you should now see an embeded objects labelled Microsoft Office Web Components.

Right click inside the Microsoft Office Web Components object and click on the object, in this example it is the Microsoft Office Chart 11.0 Object > Edit.

Step 5 - Setting up the connection for a Pivot Chart

After clicking the Edit option the Command and Options dialog box will pop up this is where most of the data source / connection setting up will occur. To continue click on the Data from a database table or query radio button, then click on the Connection… button.

After clicking the Connection… button the Data Details tab will become active. Click on the Edit… button to continue.

This will pop up a Select Data Source dialog box, if you haven’t already setup a .odc connection to your Access database, click on the New Source… button.

After clicking the New Source… button, a Data Connection Wizard will appear, click on the Other/advanced option and click the Next > button.

A Data Link Properties dialog box will appear. In this example we are connecting to an Access database, and that uses the Microsoft Jet as the OLE DB Provider so select that option or select one that suits you then click the Next >> button.

Within the same Data Link Properties dialog box you will be asked to browse for the location of your Access database by using the … button. You will also be asked if you database has a Username or Password, in this example I left my User name and Password blank.

After setting up the options in your Data Link Properties dialog box, click the Test Connection button and you should receive this message box alerting you of a succesful or unsucessful test.

Click OK and then click the OK button again to get back to the Data Connection Wizard. You will be asked to choose a table within your database, in my database the table is named Data. After selecting a Table within your database click the Next > button.

You will then asked to enter in the File Name of your .odc file and also a Description, I would suggest checking the Save password in file check box as well just to save any password prompts down the line. Once you are done click the Finish button.

A dialog box will pop up asking for you to open your new .odc file, the file will already be inpuitted in the text box so just hit the Open button.

You will then be brought back to the Command and Options dialog box, you will be able to see that in the Connection textbox that a connection string has been setup and also that the table Data has been selected under the Data member, table, view or cube name area.

To change the chart type click on the Type tab, this will bring up the available charts available.

You have succesfully setup a connection for your Pivot Chart! Click the close button (x) to close the Command and Options dialog box.

Using your Pivot Chart

After clicking the close button (x) from the Command and options dialog box, you might be shocked to see that your PivotChart is blank (like the screenshot below).

Don’t worry! To work with Pivot Charts or Pivot Tables in PowerPoint it is best to get into Presentation or Fullscreen mode.

To do this press SHIFT+F5 or the Presentation Icon located in the bottom left hand corner:

Now you should be able to see your Pivot Chart in all it’s glory!

The tool bar at the top is the Microsoft Office Web Components tool bar, hovering over each icon will give you some information but what you can do :

After clicking on the Fields List icon on the tool bar (see above) and dragging and dropping some fields I now have a Pivot Chart built into my PowerPoint presentation based on an Access datbase linked to an Excel sheet!

If you have any problems or questions please comment.

Enjoy!