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.

02/01/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.



No comments
The Engineers' Choice Awards highlight some of the best new control, instrumentation and automation products as chosen by...
The System Integrator Giants program lists the top 100 system integrators among companies listed in CFE Media's Global System Integrator Database.
The Engineering Leaders Under 40 program identifies and gives recognition to young engineers who...
This eGuide illustrates solutions, applications and benefits of machine vision systems.
Learn how to increase device reliability in harsh environments and decrease unplanned system downtime.
This eGuide contains a series of articles and videos that considers theoretical and practical; immediate needs and a look into the future.
Sensor-to-cloud interoperability; PID and digital control efficiency; Alarm management system design; Automotive industry advances
Make Big Data and Industrial Internet of Things work for you, 2017 Engineers' Choice Finalists, Avoid control design pitfalls, Managing IIoT processes
Engineering Leaders Under 40; System integration improving packaging operation; Process sensing; PID velocity; Cybersecurity and functional safety
This article collection contains several articles on the Industrial Internet of Things (IIoT) and how it is transforming manufacturing.

Find and connect with the most suitable service provider for your unique application. Start searching the Global System Integrator Database Now!

SCADA at the junction, Managing risk through maintenance, Moving at the speed of data
Flexible offshore fire protection; Big Data's impact on operations; Bridging the skills gap; Identifying security risks
The digital oilfield: Utilizing Big Data can yield big savings; Virtualization a real solution; Tracking SIS performance
click me