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.
Motor specification guidelines; Understanding multivariable control; Improving a safety instrumented system; 2017 Engineers' Choice Award Winners
Selecting the best controller from several viewpoints; System integrator advice for the IIoT; TSN and real-time Ethernet; Questions to ask when selecting a VFD; Action items for an aging PLC/DCS
Robot advances in connectivity, collaboration, and programming; Advanced process control; Industrial wireless developments; Multiplatform system integration
Motion control advances and solutions can help with machine control, automated control on assembly lines, integration of robotics and automation, and machine 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!

Future of oil and gas projects; Reservoir models; The importance of SCADA to oil and gas
Big Data and bigger solutions; Tablet technologies; SCADA developments
SCADA at the junction, Managing risk through maintenance, Moving at the speed of data
Automation Engineer; Wood Group
System Integrator; Cross Integrated Systems Group
Jose S. Vasquez, Jr.
Fire & Life Safety Engineer; Technip USA Inc.
click me