Database Design and Reporting

EZSoft, Inc. has extensive professional experience with designing database schemas and reports to fit the needs of its customers’ applications. In fact, ControlBuilder® and ProductionSuite® are built upon relational databases as the repository for all project and area model data. 


EZSoft, Inc. has designed Recipe, Reporting, and Inventory Management databases from the ground-up for inclusion in the ProductionSuite® components. Stored procedures provide the interface into the components so the platform is completely open to future integration or enhancements by the customer. 

The ProductionSuite® Report Manager utilizes SQL Server Reporting Services for report authoring and publishing. In addition to a rich set of reports that sit on top of the ProductionSuite® databases, EZSoft, Inc. has also developed standard report templates for the leading Third Party Batch Execution systems on the market today.

Database Design and Reporting | Project Portfolio:


Project: Batch Reporting UpgradeS-88 Batch System upgrade to allow for detailed tracking of batch lots. Custom reports to show the transfer of product between tanks.

Industry: Consumer Healthcare

Application: Lot Tracking and Reporting

Overview: This project included updating a ControlLogix PLC, an iFix SCADA node and an Industrial SQL server to improve the tracking of transfers of liquid batches between tanks. The system involves 2 charge tanks where a premix is made, 5 batching tanks where liquid batches of mouthwash are mixed and 8 storage tanks where the completed batches are stored before packaging. The area has 4 Control Logix PLCs and 4 iFix SCADA nodes. 

There is also an iBatch server and a SQL database server. Batches can be transferred from one of the 5 batching tanks to one of 2 dedicated storage tanks. This plant needed the ability to generate detailed reports on the transfer of batch lots as they were moved between tanks.

When a transfer occurs, identifying data on each lot is kept in a PLC that controls the batch tanks and transferred to a separate PLC controlling the storage tanks. New logic added as part of this project buffered that data and triggered a custom VBA script on the iFix node. This script retrieved the data out of the buffer and passed it to a stored procedure on the SQL server. 

The stored procedure wrote a record to the database where a custom report could display filtered reports on all transfers of batches.

Technologies Used:

Allen Bradley ControlLogix PLC

Intellution iFix SCADA Version 3.5

Intellution iBatch and iWorkInstruction Server

SQL Sever with Crystal Reports

Follow Us on Linkedin