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
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.
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.
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.