Use Microsoft Excel as a report writer

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

By Dave Harrold February 1, 2002

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

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 consists of:

Designing the report layout using Excel’s cell and chart formatting tools, or if a ‘favorite’ report exists, preparing the report to use the XLReporter add-in;

Assigning worksheet cells to one of four data source types using XLReporter’s Connection Configurator (See Connection configurator data types sidebar); and

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.

Report scheduling, publishing

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 go to www.contrleng.com/freeinfo or to down load a trial verison visit www.systech.com .

Author Information

Dave Harrold, senior editor dharrold@cahners.com

Connection Configurator Data Types

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

Function data come from XLReporter’s internal calculation database for long term and persistent time- or event-based statistic, difference, or profile results.