Creating Dynamic Pivot Table Reporting Systems

One of the more useful ways to utilize imported external data is in the creation of a dynamic reporting system. What does that mean?

Imagine for a moment that you have distributed, to 10 users, an Excel workbook that consists of one pivot table report. This report provides data on weekly sales. If your pivot table uses a data source within the Excel workbook, you have essentially given your users a static report. Although it is true that the pivot table is dynamic in and of itself, it will never show new data because the source data used to feed the pivot table is hard-coded locally. The bottom line is that every week, you will have to distribute a new weekly sales report to the same 10 users with the latest data.

Now imagine that the same pivot table report uses an imported external data source. With this new arrangement, you can dynamically link back to your source data and refresh the data that feeds your pivot table. Better yet, any of the users consuming data from your report can refresh the data source when needed at the touch of a button. Because they will always have access to the latest data, the need to redistribute subsequent reports is eliminated.

CASE STUDY: Create a Standalone Dynamic Pivot Table Reporting System


There is no sample file for this case study. In this example, the data source being used is a SQL server. The reason for this is that using a data source that can be updated and maintained on a server is more conducive to the kind of report building being demonstrated here. Unfortunately, it is difficult to provide a sample file for this case study because the essence of this demonstration is the interaction between Excel and a SQL server data source, and we cannot pass you a SQL Server environment.

In order to report the latest customer retention numbers, you have been distributing the same pivot table report repeatedly to the same managers every month. In the meantime, much of your data has been moved to a new SQL server.

You decide to take advantage of the new data situation and create a dynamic pivot table report that uses the SQL server as an external data source. This will give your managers the ability to refresh their customer retention report at the touch of a button, not to mention save you the time and effort it took to create and send new reports every month.

This task can be broken out into five steps:


Create your external data source.


Define your query.


Import your results.


Create and format your pivot table report.


Create a macro that refreshes the report with the latest data.

Create Your External Data Source

The first thing you will need to do is create the external data source. In a new workbook, go to the application menu and select Data, Import External Data, New Database Query.

This will initiate the MS Query Wizard. As shown in Figure 9.7, the first dialog box will ask you to identify the data source you would like to use. In this case, you want to use a SQL Server database. Because your desired data source is not shown here, you will have to select <New Data Source> and then click OK.

Figure 9.7. Because your SQL Server database is not shown here, select <New Data Source> then click OK.

In the next dialog box, enter the name you would like to give to your data source and identify which database driver should be used to connect to the data source.

A database driver is the translator between an application and the database. You cannot connect to any external data source without its appropriate driver. In Figure 9.8, you are using the SQL Server driver because your data source will be a SQL Server database. After you have named your data source and selected the appropriate driver, click the Connect button to continue.

Figure 9.8. Enter a name for your data source and then select the appropriate driver. Click the Connect button to continue.


Microsoft Office comes with default database drivers for the following data sources: SQL Server, Access, dBASE, FoxPro, Excel, Oracle, Paradox, various text files, and a select few third-party drivers. If you do not see a selection for your database driver, you will need to install it before any connection can be made.

Next, you will have to enter three pieces of information: server, login ID, and password.

To identify the server you are connecting to, you can use either an IP address or a DNS (Domain Name System), as shown in Figure 9.9. For the purposes of this case study, your server has been assigned a DNS of SOPS, so you do not have to use the actual IP address. It is always best to use a DNS if you have the option for a number of security and functional reasons.

Figure 9.9. In the Server input, enter the server's IP address or, preferably, the server's DNS (Domain Name System).

Next enter your login ID and password (normally provided by your Database Administrator). Click OK twice to complete the creation of your data source.

Define Your Query

At this point, you will see your new data source as a selection in your list of data sources. In this case, your new data source is called MyDataSource, as shown in Figure 9.10. Select your new data source, check the Use the Query Wizard to Create/Edit Queries check box, and then click OK to continue.

Figure 9.10. Select your new data source, place a check in the Use the Query Wizard to Create/Edit Queries check box, and then click OK to continue.

As shown in Figure 9.11, the dialog box that comes up next is a field selector. Use the tree view on the left to expand the tables in your database and view the fields. Then move the fields you will need to the list on the right using the buttons in the middle. When you are satisfied that you have all the fields you will need, click the Next button three times until you come to the dialog box titled Import Data.

Figure 9.11. Simply move the fields you will need from the list on the left to the list on the right using the buttons in the middle.

Import Your Results

As shown in Figure 9.12, select where you would like to place the resulting dataset. Keep in mind that if your resulting dataset has more than 65,536 rows, Excel will cut your data short.

Figure 9.12. Choose where you would like to place your resulting dataset and then click OK.

After your new dataset has been imported, right-click anywhere inside the dataset and then select External Data Range Properties. This will activate the External Data Range Properties dialog box, as shown in Figure 9.13. Place a check inside the Save Password check box. This setting will embed the password for the data source in the workbook, allowing your users to refresh data without requiring a password.

Figure 9.13. Use this dialog to save the password for the query.

Use Caution When Saving Passwords

When you enable the Save Password setting, as shown in Figure 9.13, you are essentially embedding the password into your Excel workbook. This means that anyone who is interested enough could open your Excel workbook with a text editor, such as WordPad, and extract the embedded password. Figure 9.14 shows an example of this.

Figure 9.14. Anyone determined enough can examine your Excel file with a text editor to find the password.

Here are a few actions you can take to help minimize the potential impact of this security flaw:

  • Employ the use of views where possible.

  • Use "read-only" tables that are isolated and dedicated to your dynamic reports.

  • Use a dedicated user ID and password used only for your dynamic reports.

  • Avoid using IP addresses in your data sources.

Create and Format Your Pivot Table Report

Create a pivot table using the external data you imported into your workbook. After your pivot table is built, you can link a chart to it and format your report to suit your needs, as shown in Figure 9.15.

Figure 9.15. Create your base pivot table and then add a chart and some formatting.

Create a Macro That Refreshes the Report with the Latest Data

After your users have this report in their hands, they can technically refresh their report to get the latest data any time they want by performing the following actions: right-click anywhere inside the source data, select Refresh Data, go to the pivot table, right-click anywhere inside the pivot table, and select Refresh Data.

Although this series of actions seems simple, some of your users will not have the Excel savvy or the inclination to begin to refresh their report. The good news is that you can create a macro that will perform these actions for the user anytime they fire it.

First, go up to the application menu and select Tools, Macro and then select Record New Macro. When the dialog box shown in Figure 9.16 activates, name your macro "Refresh Data." In the Shortcut Key Input field, type in the letter R. This will allow your users to fire the macro with the keyboard. In this case, the combination of keys that will fire this macro is the Control key, the Shift Key, and the R key. Click OK to start recording your actions.


Go to your source data.


Right-click and select Refresh Data.


Right-click on your pivot table and select Refresh Data.

Figure 9.16. Give the macro a name and a shortcut key.

When you are done, go up to the application menu and select Tools, Macro and then select Stop Recording. Now your users do not have to know every action it takes to refresh their data. They simply press the shortcut keys you defined, and the macro does all the work! To make the shortcut key easy to remember, add a note to your worksheet, as shown in Figure 9.17.

Figure 9.17. With a little additional formatting, you have created a dynamic reporting tool that you can now distribute to your users as a standalone Excel file.

    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140

    Similar book on Amazon © 2008-2017.
    If you may any questions please contact us: