Microsoft Excel as a report writer using XLReporter

TECHNOLOGY UPDATE

Use Microsoft Excel as a report writer

D

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, or go to www.controleng.com or to down load a trial version visit http://www.sytech.com/.

—Dave Harrold, senior editor, Control Engineering