Need a convenient way to view summaries of your business' vast amounts of iSeries data? Better yet, would you like to do it free using software you already have and are familiar with? Then we may have the solution you have been searching for: Microsoft's Excel.
Excel is not just for crunching numbers in lifeless spreadsheets. Using Excel's powerful ability to import external data, we are going to create what's commonly known as a dashboard -- a collection of graphs and other visual aids that brings you critical business data in an intuitive summary format. They allow the executives and managers (or anyone for that matter) to draw valuable insight easily from oceans of data while making smarter, faster decisions from the top down.
Figure 1.1: Example Excel report
Step 1: Get the Data
The first and most important step in creating a dashboard or report is, of course, obtaining the data. From importing an XML file to directly querying a database, Excel provides numerous options for retrieving your data. However, this article will focus primarily on Excel's little-known yet immensely powerful web query feature. Characteristic of most Microsoft software, this feature can be accessed through an easy-to-use wizard that allows the user to visually select a table from any Web page. This wizard can be found by pulling down the Data menu, expanding Import External Data and selecting New Web Query.
Figure 1.2: Creating a Web query in Microsoft Excel
This will open a browser-like window that displays the home page of the default Internet browser. Upon closer inspection, you will notice (if the Web page contains a table and you are using Windows XP or later operating system) that there are small yellow boxes with black arrows. Each of those represents an HTML table within the Web page.
Next, enter the Web page containing the desired data. In the examples depicted below, the iSeries data is obtained using Planet J's WOW (Web Object Wizard) software, a Web-based tool for creating custom Web applications against JDBC-compliant databases such as the iSeries. (WOW can be freely downloaded by sending an email to info@planetJavaInc.com.)
The table selected in Figure 1.3 was created with a simple SQL operation within WOW. As an alternative, you can use any Web application that you may have that renders an HTML table. Note that a blue outline appears around all selected tables. After all relevant tables are selected, click the Import button. That's it! Simple.
Figure 1.3: Selecting data (served up by Planet J's WOW) for the Web query
Step 2: Creating the dashboard/report
At this point the data from the Web query should be displayed nicely in Excel's standard tabular worksheet. The hardest part is over. It's all familiar territory from here on out. Now that the data has been imported, the second (and last) step is simply formatting the data.
To avoid cluttering the dashboard/report, separate the imported data from its stylized form (charts, graphs, etc.). This is best done using different sheets for the dashboard/report and the data. Once the data is on its own sheet, use a new blank sheet to begin building the dashboard.
Figure 1.4: Example Excel dashboard
In Figure 1.4 above, the example executive dashboard uses a simple combination of colors, graphs, charts and a few imported pictures to create a sophisticated and professional presentation using live iSeries data. The data is retrieved from the iSeries via the Web query and then it's all Excel development from there. Anything you can do in Excel will be available, including charts, pivot tables and graphs. For more information on creating graphs and charts, consult Excel's Help menu for some excellent guidance.
Optional: The extra mile
Not satisfied with simply presenting iSeries critical business data live to your already impressed boss? Good -- here are a few ideas to help you kick your dashboard up a notch or two.
- Have your cake and eat it, too. Say you need more than just the ability to view your data. You want to update your data on-the-fly right from the dashboard. From the Insert menu in Excel, insert a hyperlink. Since a WOW operation was used to obtain the Web query data, that same operation can be used to edit the data. Specify the address of the WOW operation within the hyperlink and you're done. In one click you've switched from a convenient summary graph to editing live data on your iSeries from any location with an Internet connection. Life is sweet.
- This data is from when?
So your boss was out of town for a few days and decided to leave his
computer on with his Excel dashboard open. Upon returning he sits back at
his desk and observes on his dashboard that J.W. Doe currently has an
outstanding balance of $250,000. He then calls Doe and angrily demands
payment to which Doe replies that he already paid two days ago. Stunned
and embarrassed, your boss apologizes profusely and hangs up. Then he
calls you. Not good. What went wrong? The data wasn't current.
To avoid this nasty situation when using time-critical data, simply use one of Excel's convenient date and time functions, such as "=CURRENT( )." This particular function will display the last date and time at which the data was refreshed. In addition, the Web query can be configured to refresh the data from your iSeries every minute or as required. Problem solved.
- Feeling ambitious? Throw in a macro using Excel's extremely easy macro recorder that records your mouse and keystrokes to recreate whatever series of actions you like. In the example dashboard in Figure 1.4, the Refresh Data button located in the middle right is a simple macro that refreshes all the data. It took less than a minute to make. No coding.
Honestly, the possibilities are endless. You are not limited to dashboards either. Try using Excel to generate visually appealing business reports such as the example in Figure 1.1. This can be done using essentially the exact same steps described for the dashboard, just with slightly different formatting. The key is to be creative. Good luck!
About the author:Paul Holm is an iSeries WebSphere and Java specialist at PlanetJ Corp. He worked for IBM-Rochester for over 10 years as a DB2/400 and Java/WebSphere developer. Matt Jensen is a Web software developer at PlanetJ Corp.