Introduction

The Consumer Confidence Rule requires public water suppliers that serve the same people year round (community water systems) to provide consumer confidence reports (CCR) to their customers. These reports are also known as annual water quality reports or drinking water quality reports.

The CCR summarizes information regarding sources used (i.e., rivers, lakes, reservoirs, or aquifers) any detected contaminants, compliance and educational information. The reports are due to customers by July 1st of each year.

This article will take you through the basic steps to track the data for your CCR using WIMS.  With the data in WIMS you can produce reports that can be sent to your customers or used as a basis for your report when using a graphic artist.  Report is due on July 1^st of the following year (ie 2006 report is due July 1^st 2007)

Overview

The basic steps to setup your CCR:

1. Download required attachments.
2. Create the required CCR variables using the OPS SQL Import Utility with the WaterWiz.xls.
3. Create your data entry forms and enter the data
4. Create your CCR report

Step 1. Download required attachments

At the bottom of this page there are several attachments that you will need.  Simply download the files and place them in the following folders:

File	Folder	Description
CCR_LookupTables.zip (Contains CCR_HE.ss3, TOC_REM.SS3)	Extract to: C:\HachWIMS\Client\LookupTables	CCR_HE.ss3 - Lookup table that defines sources, health effects, MCL, MCLG, MDL for contaminants based on Storet Code TOC_REM.SS3 - Lookup table that defines TOC % Removal Requirement
guide_ccr_forwatersuppliers.pdf	C:\HachWIMS\Client	Not required, EPA guideance manual on CCR
CCR_Templates.zip (contains CCR_Table_Only.ss3, CCR_With_Text.ss3)	Extract to: C:\HachWIMS\Client\templates	Spread Report Templates
Water Wizard Spreadsheet.xls	C:\HachWIMS\Client\OPSSQLIMPORT	Excel file used by the OPSSQLImport.exe program to create the variables
Lead_and_Copper_Rule.pdf	C:\HachWIMS\Client	Not required, EPA guideance manual on Lead and Copper rule
OPSSQLIMPORT.EXE	C:\HachWIMS\Client\OPSSQLIMPORT	Utility used to create variables - Used by Project Managers only - Can be found on  I:\Services\OPSSQLIMPORT_LatestVersion.

Step 2.  Create CCR Variables

1. Rename the Water Wizard Spreadsheet.xls you downloaded it step 1.  NOTE: In the examples below the file was renamed to SandyCCR.xls after download. 
2. Open the spreadsheet.  You will probably need to use the Dist Monitoring, Lead&Copper and the CCR sheets.  The Distribution Monitoring sheet will be used to create the variables for your Total Coliform samples. 

Notice D1 says “MULTISITE”.  This tells the OPS Import utility to create a set of the listed variables for each location listed in V9:

Now list each of your Distribution Sites (including an upstream and downstream location for resampling requirements when a positive is detected).  Location description can be the site name, address, or any other description that identifies the location.   You may have over a hundred sites, if so make sure to space the variables (ie by setting the Base Var#) so you do not overwrite other variables.

Next edit the list of variables to reflect what you need to track.  When removing variables, do NOT delete the entire row as that will also delete a location that you entered in Column V.

Next we setup your Lead and Copper sampling in much the same way.   Based on Lead and Copper Rule you should have between 20 and 100 sites. (See Lead and Copper Rule Quick Reference Guide.pdf for more info).

Now use the CCR Sheet to create the Inorganic, VOC,SOC, and radioactive contaminant variables.  In this case I got rid of all the Microbiological Contaminants as they were already created elsewhere:

Total Coliform - You must report the Monthly % Positive of your Distribution Samples.

TOC – You must report the Filtered Water TOC and the % removal.

Turbidity – You must report the Filtered Water Turbidity, usually the Min, Max, and Avg of your 4 hour turbidity readings. 

Lead and Copper – Setup on the Lead&Copper sheet.

Other variables may already exist depending (Nitrates, Chloramines, HAA5, TTHMs are likely to already exist) so make sure to review your existing db and the list.

Save the file and now we are ready to run the OPSSQLIMPORT.exe (on your OPSSQL CD or download) to create the variables.

Browse for your Wizard XLS file using the … button.  Set the Custom Fields “T” to UD3 and “U” to UD4. 

WARNING: Do NOT check either of the Delete existing Locations or Delete Existing Variable List checkboxes.   

Click Create Variables and your variables, locations, and areas will be created.

You now need to import 2 lookup tables that are required.  Download CCR_HE.ss3 and TOC_REM.SS3 and place in your c:\opssql\lookupTables folder.

                Go to System Setup, System Tables, Lookup table Setup.  Click Load and select CCR_HE.ss3

The CCR_HE table is used to lookup containment name (1), Source (2), Health Effect (3), MCL (4),  MCLG (5) and EPA Method Detection Limits(6).

The TOC_REM is used to calculate the Required % Removal for TOC based on Source TOC and Source Alkalinity.

From the variables setup by the wizard you need to create summaries.  In order to calc the % Positive for a month on total coliform you must create the following 3 variables. 

MVGT(1.1,GETVAL(V9005),GETVAL(V9015),GETVAL(V9025), …)  - Counts the number of values >1.1.  The variables are Text Parameters with a list like:

The GetVal() returns the numeric value from a Text Parameter.  The Value will be 1 when absent and 2 when present.

To calc the percent removal of TOC:

The TOC % Removal Required uses TABLE("TOC_REM",V1051,V1081,3,1) to lookup the Requirement.

Step 3: Create your data entry forms and enter the data

The easiest way to create data entry forms is to create a Monthly Data Entry Form for each grouping:

                CCR SOC
                CCR VOC
                Dist Total Coliform
                CCR Inorganics
                CCR Radiologicals

Step 4: Create Report

We provide several templates that can be used to help produce your CCR or just the Containment Table.  

Download CCR_Table_Only.ss3 and put in your c:\opssql\templates folder.

Get into spread design.   Go to File, New and select the CCR_Table_Only.ss3 template.  If you want the introductory text which contains example educational statements, Sources of Water..., pick the CCR_With_Text.ss3 template. 

In column L enter in the variable number for each Contaminant.  The Report will then find the last value entered in column M and then report the Low, Avg and High value.   There are some special cases (for TOC, Turbidity...), refer to instructions in column O,P in the report template.  Hide columns L thru W when you are done designing reports.

Save the report as CCR. 

Next, save the report as CCR_2007.  Now  go through the report and delete the rows where the contaminant was not detected.