Use Microsoft Excel as a report
the many integration investments, a significant number of
production reporting still occurs using spreadsheets printed
on paper. Why?
People will cite lots of reasons,
but they all boil down to trust. Spreadsheets have been around
so long that people have evolved their “favorite”
spreadsheet-based reports. They can see and validate formulas,
and they trust the results produced. After all, if you’re
sending a report with lots of “crunched numbers” to your boss
and/or your bosses’ boss, you need to trust the underlying
technology used to develop those reports.
Possibly the most-used spreadsheet
today is Microsoft Excel. Excel’s ability to manipulate,
analyze, and report numerical data is unquestioned.
Automatically getting data into Excel is achieved by
connectivity technologies, such as Dynamic Data Exchange,
Microsoft Query, Open Database Connectivity (ODBC), Object
Linking and Embedding (OLE), Component Object Modeling (COM),
and Distributed COM (DCOM), each with its own pros and
• Real data come from real-time data servers
• History data originate from human-machine
interface (HMI) software historical files.
• Expression data are user configured expressions
and may or may not include NameTypes, Excel formulas, or
fixed text. Time and/or date stamps are examples of
• Function data come from XLReporter’s internal
calculation database for long term and persistent time-
or event-based statistic, difference, or profile
SyTech (Walpole, Mass.) recognized
and set out to address the challenges of using Excel as an
integrated and automated report generation solution. The
result is SyTech’s XLReporter product.
Using COM technology, XLReporter
pushes data into Excel cells with XLReporter controlling Excel
as a “behind-the-scenes” engine to duplicate workbooks,
manipulate worksheets, update charts, recalculate formulas,
print reports, and post web pages. (See XLReporter
Architecture diagram.) (Note: A future release of XLReporter
will also support ODBC.)
ABC’s of report creation
Creating a report using XLReporter
- Designing the report layout
using Excel’s cell and chart formatting tools, or if a
“favorite” report exists, preparing the report to use the
- Assigning worksheet cells to one
of four data source types using XLReporter’s Connection
Configurator (See Connection configurator data types
- Testing the report using
XLReporter’s Worksheet Execute tool to verify data values
transferred to the worksheet and Excel formulas, charts,
etc., produce desired results.
When/if results aren’t what’s
expected, XLReporter’s status log aids troubleshooting with a
record of every event and when it occurred.
Most industrial automation reports
are generated based on time or events. Shift-end and daily
production reports are examples of time-based reports;
batch-end reports are examples of event driven reports.
Traditionally production reports
have been printed, but one of Microsoft Office 2000’s
enhancements allows converting documents to web pages, opening
up report publishing possibilities.
XLReporter’s Schedule Designer tool
defines when to collect and store data, and when, where, and
how to publish reports.
SyTech’s XLReporter is a product
designed to fill the unglamorous yet important space of
reporting. What makes SyTech’s use of technology unique is
that it creates those all-important reports using a product
that people already trust-Microsoft Excel.
For more information, or go to www.controleng.com or to down load
a trial version visit http://www.sytech.com/.
senior editor, Control Engineering
Dave Harrold, senior
editor, Control Engineering
Comments? E-mail: firstname.lastname@example.org