April 2008

Web Exclusive

Report generation

Project reports may seem mundane, but watch out for holes in software

Fast forward

  • Shock may hit when software does not do what you thought it would do.
  • Easy tests to see if software fills the bill.
  • You can fix communication problems easily, but sometimes that increases complexity
By Michael Carey

When it comes to a project, there is always the excitement of planning or desinging and then executing, but here is a bit of advice: Don't delay reports until the end of the project.

In the grand scheme of things, reports often end up put off until last, once the system is operable. Report requirements, such as a general report "type" (shift report, batch report, etc.), may have been specified, but the specific data for those reports may not be needed until much later.

The true shock may hit when the software you thought would capture the specified project data does not do what you thought it would do.

In a perfect world, the packaged automation system would provide reports with minimal configuration, but there are reasons why the software can not meet the requirements, including: it cannot support the format, it cannot integrate its data with other system data, and the data needs to undergo processing before the report software can use it. The solution to the problem can be to reduce report requirements, purchase a report package, or develop custom software. Hopefully, for most organizations the automation system's report is adequate; but if the report package is not adequate, it would be best for all concerned on the project to identify the deficiencies early and then make the proper plans.

Reports can be either standard or ad hoc. Standard reports are pre-defined reports generated on periodic events (shift report, daily report, weekly report), process events (batch completed, critical process event), or request (downtime analysis, inventory levels). Ad hoc reports are temporary reports created when information is not available in the standard reports. The person seeking the information normally generates the reports, and they are usually just printouts of information available on the system. An example ad hoc report is printing the alarm and event log at the time of a process failure. Specialized ad hoc reports are generally not the issue; it is the standard reports you have to keep an eye on.

Standard report details

The easiest detail to verify is whether the report software supports the required media. Based upon the industry and how you use the reports, read-only media may be a requirement, for example paper or PDF. Non-regulated industries have the advantage of creating reports using editable formats such as ASCII, RTF, or Excel, which are easier to create but can undergo editing.

Data presentation requirements are also easy to verify. You can quickly determine whether or not the report software supports graphics, what type of graphics it supports, and the integration of the graphics with text. How you can present the data and the media used may greatly depend upon the report generation method. This is an easily overlooked detail software vendors try to obfuscate. It is a nasty little surprise when you find out after installing the system that you can only generate trend reports on request and not automatically.

Report generation methods can be either automatic or on request. Automatically generated reports were prevalent when storage space was at a premium so the data was instantaneously outputted to a printer. Automatically generated reports still exist, but they are going out of fashion as storage space is not a problem now and networking makes requesting reports simple. Requested reports are normally easier to create because the automatic method for generating the report, the report trigger, does not have to be created.

The report trigger can be periodic or event based. Periodic report triggers are the easiest because you can schedule them through the system. Event-based report triggers are much more complex because they must hook into the process data and report based upon a certain state or event (for example, a batch completes, a temperature exceeds a certain limit, etc.). Event-based report triggers are normally more costly because they require custom configuration or programming outside a standard implementation.

Requested reports do not suffer media limitations like some automatically generated reports. When a report needs to be in PDF format, the user can just print the report to a PDF printer; but to have the PDF report created automatically, the system may require special software. As for report formats, a requested report may use an ActiveX component for displaying a process trend, but that same ActiveX component may not function on an automatically generated report.

Graphs preferred

The data and how the system reports it can affect the complexity of the report. Two common manufacturing data models are continuous historian data and transactional data. The historian systems can have their data reported in tables or in graphs. The tabular data is easier to produce, but graphs provide more useful information to the users and are often the preferred method. Graphs are difficult to reproduce outside the historian's software package and normally require some level of custom development or integration.

Transactional data (the data found in relational database management systems, or RDBMS) comes if two forms-single instance and recursive. Single instance data are data about an entity that does not relate to another entity of the same class. An example is an alarm and event log where each entry is a unique entry that does not relate to another entry. Recursive data are data stored about an entity that relates to another entity of the same class. An example is a material tracking system where a child material entry links to its parent material entries. Commercial report software can easily acquire single instance data, but you will need custom software to pre-format the recursive data for the commercial report software. Thus recursive data will increase the complexity, but single instance data may not.

The report's complexity increases as the number of data sources increase. Reportable data may reside in multiple data sources that have to link together for the report. For example, a batch report may require data from the batch manager/historian, the continuous process historian, and a laboratory information management system (LIMS). The distributed or fragmented nature of the data can rarely be overcome, and why should it be? The user selected each software package for its ability to meet different requirements-storing process history data in a RDBMS is inefficient; you should use a continuous historian. It is the report's role to pull this data together. When pulling the data together, there are two critical questions that need answering: Can the report generator communicate with the data sources, and is there a relationship among the data across the different data sources?

Determining if the report generator can communicate to the data sources is the easiest part. Communication methods may be ODBC, OPC HDA, etc. If there is not a communication method, the report developers may need to add middleware such as web services to make data collection from the report generator possible. You can usually fix communication problems easily, but sometimes that increases complexity. The more difficult item is determining if there are data relationships between the different data sources.

All about relationships

Data relationships to a continuous historian are normally just the time stamp of the data. Getting the time span normally requires some programming, which makes the report generator more customized. Data relationships between transactional data sources like a batch historian and LIMS normally require procedural components and custom programming. For example, if the QA lab takes samples at various stages of a production run, the run ID and stage information need to be in the LIMS in a format that can match the samples to the exact run and stage. This may seem trivial, but when working with different systems, field lengths can be problematic (i.e., the run ID is too long for LIMS) or there may not be enough fields to create the relationship. When adding reports to existing installations, the reports may require operational procedural changes to make them work. This can cause previous data to be unreportable. There are always challenges when reporting data from distributed systems, and the word challenge often means an increase in complexity.

After evaluating the report requirements, you may determine the automation system's report package cannot meet the report requirements, and thus, the project has the options of reducing report requirements, purchase a commercial solution, or create a custom report generator. The preferable method is always to reduce the report requirements to operate within the system's functionality. This reduces system complexity, reduces development costs, reduces training, and reduces maintenance costs. When reducing requirements, you will find people normally require more than they need, and in many situations less is better as non-essential data creates noise that can hide the critical data. If you really look at it, it could be an information overload. In a batch report, the user may ask for a temperature trend during a phase, but for regulatory requirements, all you really need is a verification the temperature stayed within a required range. The range data can easily report through phase alarms supported by the packaged batch report, but graphical trends would require a custom solution that really is not a business requirement. Reducing requirements is very effective at the beginning of the project, but it can be detrimental to user perception at the end of the project.

If reducing the requirements cannot solve the problem and you need either a commercial package or custom development, a course of action may be to get IT involved and hope they have a magic solution. They may; after all, IT is all about databases and reporting.

But IT may not be the knight in shining armor because they are not familiar with some manufacturing data models-continuous historians and recursive transactional data. This type of data is usually not on the business-based tools they are familiar with. For example, trends required by continuous historians are different from business trends. Business trends come from aggregated data based upon specific categories; the business trending tools cannot handle the amount of data available from the historian in an uncompressed format nor have the algorithms to interpolate compressed data.

The common perception is report creation is a simple part of the automation system, and that is why it is left to last on projects. In reality, reporting can be very complex, based upon the system and reporting requirements. You cannot eliminate reporting headaches, and rarely can some outside group come to the rescue in the 11th hour. However, if you can analyze the report requirements in a timely fashion, the project team can plan accordingly to meet those challenges before they become problems.


Michael Carey is director of MES and Information Systems at Panacea Technologies Inc., an automation systems consulting firm. His e-mail is careym@panaceatech.com.