AutoSoft Systems
        AutoSoft Systems 2 Round Hill Court
        East Greenwich, RI 02818
        401.884.5653 Fax
                        401.996.3631 Cell
AMDG       Decision Ready Information! autosoft@aol.com
Commercial & custom multi-user computer software for a variety of applications including performance metrics, statistical analysis, data extraction and merger from multiple large databases, computer simulation and management information systems.  Founded in 1982.
We know Excel! Whenever possible, Excel is the user interface, VBA is the programming language, ODBC connects to the database of your choice.
Home About Us Literature Wire & Cable Pharmaceutical & Biotech Construction Management Commercial Products Custom Solutions
Contact Us Events Downloads
Corporate High Purity Water Performance Reporting
A major biotech company commissioned AutoSoft to prepare a program that would automatically generate a comprehensive monthly report that would gather data from the corporate Lab Information Management System (LIMS) and 5 different manufacturing facilities process data historian (in this case, PI Historian).  The report then would process and analyse the data, flag exceptions, and then prepare a Microsoft Word document that could be distributed to corporate leadership.  Quite a process!  We now identify how we did it.
Step 1:
Perform a query into the corporate LIMS, gathering only the high purity water data samples.  Count the total number of samples, and the samples that are above the alert and alarm limits.  In the case of data points above the limits, also extract the comments entered by the technicians performing the tests.  In the case of bacteria analysis, the technicians will often identify the exact bacteria(s) present.  The following screen shot illustrates the part of the Microsoft Word document that reports on this data (altered results).
Step 2:
Perform queries into 5 different PI data historians for those tags that measure bot Total Organic Carbon (TOC) and Conductivity.  Note, some tags record information every second of the month, resulting in more than 2 million data points for a single tag.
After the PI queries are completed, the user can access this screen, which provides a green-yellow-red dashboard of the performance of each PI tag for each month.  The user has the option to display any one of 6 different parameters.  Pictured below is "Intervention-Free Operation (Blank = 100%).  
The drop down allows an instantaneous change to whatever parameter is selected:                      
Right-clicking on a green, yellow or red cell reveals all of the details for the selection.  This happens in less than 2 seconds!  
Note that the Cpk has been calculated and the process capability has been assessed.                      
Note how both the actual distribution is displayed as a solid blue line, with a Normal distribution overlayed as a dotted red line.  The Alert Limit, Alarm Limit and Specification are also displayed.                      
This bottom section lists all of the excursions in the data, the start time, end time and duration.  This is the cat's meow!
Here are some more screen shots from the Executive Summary Microsoft Word report that is automatically generated with one mouse click:
The red circle is automatically generated when an exception occurs, and the exception is listed at the bottom of the chart.    
AutoSoft Systems | 2 Round Hill Court, East Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 | Mobile: 401.996.3631 
This web page was last updated at 02/21/2014 07:56 AM and is written in EXCEL!
AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach - AutoSoft Systems is not affiliated with Consona nor Zumbach