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.
