Contact Us
SyTech ProductsWeb SupportDownloadsSyTech PartnersSyTech DistributorsAbout SyTech
 

Regulatory Report

Web Published by XLReporter- click here

Overview 
With minimal effort, a standard report issued by a regulatory agency can be enhanced so that it can be automatically created and updated, without human intervention.

The Chemical Treatment Report provided with XLReporter is an example of a regulatory report. It consists of a workbook containing a worksheet for each month.  Each monthly sheet contains information collected on a daily basis, monthly totals and charts.  At the end of each day, the appropriate monthly sheet is updated.  At the end of each month, the report is printed and also published to a web page so that it is accessible on the intranet and internet.


Chemical Treatment Report

Technology
The Chemical Treatment Report uses the following technology:
Converting an existing regulatory form for automation..
Creating workbooks and worksheets automatically.
Updating worksheets automatically.
Printing and web publishing.

Report Design
The design is provided in the file ChemicalSample.xls. It is designed around the standard Massachusetts State form which was downloaded from the Internet. The report contains daily readings in monthly worksheets and is submitted to the State at the end of each month.

The report contains a Direct date expression to cell I8 and Column connections to the real-time database on cells C16 to I16.

It is not necessary to build all the workbooks and worksheets in advance.  Only one master workbook containing one master worksheet is required.  Thereafter, all the workbook and worksheet management is performed automatically by the Scheduler.

x

x

x

Simulation
The report is provided with a toolbar which can be used to simulate the update of the monthly sheets interactively from within Excel; perfect for testing and presentations.  In practice, this toolbar would not be provided in automated solutions.

The toolbar has two pushbuttons:
Reset: Press to clear the current sheet.
Update: Press to simulate daily data collection.

To simulate the report, press Reset  Then press Update to simulate a daily update.

Scheduler
To use this example in a 'real' environment, use  the workbook to ChemicalMaster.xls provided in the XLReporter installation.

A schedule has been provided in the file Chemical.sch which can be viewed in the Schedule Designer.  In practice, this would be used to automate the report generation, automatically and in the background. 

The schedule can be tested by using the Execute feature of the Scheduler designer. Load the configuration into the Schedule Designer, select the line to execute, right click and select Execute.

The configuration uses Time Based schedules.


Time Based Schedule

At the beginning of the year:
The master book ChemicalMaster is copied to Chem{YYYY}.  Note that {YYYY} is the four digit representation of the current year.

At the beginning of each month:
The worksheet Chem{YYYY}.Report is copied to {MMM}.  Note that {MMM} is the 3 character representation of the current month e.g., Oct for October

At the end of each day:
The scheduler performs two operations at the end of each day.
 
Set the column offset counter
The report uses the column offset counter CR019 which is set to a value based on the current day of the month.  This ensures that the report data is placed in the correct cells of the report.
 
Update the worksheet
The worksheet Chem{YYYY}.{MMM} is updated with the daily data from the process.
 
At the end of each month:
The scheduler performs two operations at the end of each month

Print the worksheet.
The completed report is printed, ready for signing and distribution to the state.

Save the worksheet to a web page.
The worksheet Chem{YYYY}.{MMM} is saved to Chem{YYYY}{MMM}.htm and is now accessible from the intranet and internet. 

© 2006 SyTech, Inc. All Rights Reserved.