Report management: A review of XLReporter

In the past, I have talked about OPC servers, devices and HMI with Visual Basic, which all present data to operators for real-time and historical information. Tabular data is the most popular method of producing reports, but this may not be the best approach for everyone in the organization. Operators often need to provide an up-to-date production report that is accessible from anywhere, for both shop floor and top floor management.

A web-based or PDF-style report would be great, but the data that management is looking for is so different from the operator data. How do you overcome this challenge?

XLReporter is a tool that you may want to investigate. While not inexpensive, it should be able to do much of what you need. And there are four different entry levels – Standard, Suite, Professional and Team – each with increasing functionality.

I downloaded and installed the Professional version with no issues. You need Microsoft Excel on the report development machine, and I used Excel 2003. I did find it odd that it wanted to install off the root directory and not in the Program Files directory, as most software does.

The Team version allows for network-wide report development, as well as viewing. My focus is to have the reports viewable from anywhere.

The product uses templates, real-time OPC data or historical database data, and can create the final reports automatically using a scheduler. Historical reports use data that exists in a pre-defined format that was produced by a third-party product. Since I used an OPC server, I could create an OPC history database using OPC-HDA.

Sequence of events includes design, reporting and publishing. Certain reports can be gleaned from a normal data source, such as SQL and ODBC. The documentation provided is adequate for reference and understanding, but as with most software products, you have to play with it. And play I did!

When I switched projects, the software asked me to close Excel. Once I did, I was asked to restart the Project Explorer. Weird, I thought, since you can do that automatically. That tells me that you should expect the unexpected, which is not commensurate with the cost of the software.

While testing the product, I wanted to create a report based on the real-time data from my lab PLC. The power of XLReporter comes from its templates. Access to OPC data and databases is old news. It’s the gathering and processing of that data that we always seem to have issues with.

Clicking on Templates opened Excel. XLReporter adds a toolbar and a context menu to Excel. There are predefined templates, as well as the option of creating your own. The full complement of Excel tools is available to you in creating the template. The template function creates the workbook, and installs VBA password-protected macros, which the menu selections link to.

Here I had to use the help file. It seems that the template will automatically update and fill in the workbook, and create the pages necessary based on your criteria. The expression builder dialogue is needed to create this connection with Excel.

The software supports multiple OPC servers and connection servers, so making reports with disparate devices is a snap. The cells may need to be formatted manually based on the data that you are reporting on. Once the template is designed, I need to schedule the report so that the template knows how often to update the data, and when to create new sheets. The schedule designer is part of the XLReporter project explorer. Once opened, you have the choice of time- or event-based initiation.

There are many commands available when creating the schedule. This is a very powerful section of the software. I want to update the real-time values in the template I have just created, so I select UpdateSheet and fill in the blanks. When you start the task, the sheet will update as you have asked it to. Be sure to set the scheduler up to run as a service.

Publishing is another key component of any reporting tool. A PDF printer driver is installed so you can print at interval to a PDF file, which anyone can have access to over the network. Add a publishing event to the schedule, such as PrintSheet, so that the report can be printed each day, or at your discretion. Use SaveSheetHTML to allow a web browser to view the report. Add-ons are available for automatic e-mailing.

You can view the action using the Status Viewer in the project explorer. You always have access to the raw Excel sheet as well. New sheets are created in the standard template sheet, and new data files are created as you go.

XLReporter is a very intelligent tool as a report management portal. The ability to use OPC is a real bonus, and would provide you with plenty of options for gathering and publishing data to all levels of your company. While not inexpensive, doing it on your own might cost a lot more.

PRODUCT SPECS:
Name: XLReporter
Version: Version 9.0
Application: Report management
Vendor: SyTech, Incorporated (www.sytech.com)
Price: Starting at $900 US

Jeremy Pollard has been in the industrial automation industry for more than 25 years. He has worked as a systems integrator, consultant and educator. You can reach him at jpollard@tsuonline.com.