Database Design & 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 & Reporting | Project Portfolio:
Project: Batch Reporting Upgrade: S-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.