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.
[TABLE]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 Da
To continue reading for free, register below or login
To read more you must become a member of Search400.com
ta and selecting New
Web Query.
[TABLE]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.
[TABLE]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.
[TABLE]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.
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.
