Used to design the electronic report for submission (upload) to CIWQS. The CIWQS EZ method populates an imported PET Tool Excel file and produces the .CDF file to upload.      Note: This is an alternative (typically easier) method to building the CIWQS report to the eDMR/eReport California CIWQS method.

This form is used to "map" your state provided PET Tool Excel File to produce a file that can be uploaded to the state. Basic Steps:

1. Get your PET Tool Excel file from the state for mapping and import file into Spread Design using File>Create New Report From Excel.  Hint: Remove the conversion sheet and reduce the number of rows on each sheet to decrease the load speed of the report.
2. Update your variables and locations to make Auto Detection of variables easier. See California CIWQS Auto Detection rules.
3. Decide how data qualifiers, MDL's and RL/ML are handled. See Rules for displaying results below and planning CIWQS reporting.
4. Verify the header info (Discharger Name, Facility Name, etc...) on the General Sheet.
5. Choose eDMR/eReport>California CIWQS EZ
6. Click Prep Data Entry.  This will add rows to the Data Entry if you have Weekly reporting with only 4 rows (as sometimes you will require 5) and/or add 1 row to Single Value rows to handle the case where you have take 2 samples when only 1 was required.
7. Choose the variable, Analysis Date, MDL, and ML options and click Locate and Continue.  This will locate your data into the report and move the next parameter.
8. Repeat Step 7 until the report is filled out. 
9. Use Report Pac, Spread Reports and choose the eDMR / Electronic Report File option to create your electronic file for upload (CDF).

Prep Data Entry

This step is used to insert rows into the report to handle the following scenarios and insert formulas, values to help QC the report:

• Data Types of Average Weekly (AWEL) that have only 4 rows for results.  Since some months have 5 weeks, we insert a row for the 5th week.  NOTE: Rows without results (i.e. column J is blank) are NOT output when creating the CDF, therefore the 4 week months will also work.
• Data Types of Single that have only 1 row for results. In some cases you may want two rows in case you have 2 results for a parameter even though it is only required once.  Example, we are required to report TDS once/month so the PET tool comes with one row, however in the past we have run it a second time in the month when the initial result was high.  We need to report both results so we add a row.  Again note that rows without results are not exported, therefore in a month with 1 result that result will be reported and the blank row will not.

The Prep also adds the following formulas that are used by the QC Report button to verify you have the correct number of results in the report. 

• Column AB is the number required.  For a Daily uses NDM(0), number of days in month.  For weekly uses NWM(1,"W7"), number of weeks in the month.  For a single will typically be 1.
• Column AC is the number of slots (or rows) for the results.
• Column AD is set when data is located and counts the number of results in the slots, i.e. COUNT(AA2:AA32). 

These cells are then used by the QC Report button when creating the CDF to warn of any issues with number of results. See QC Report button below.

 

FIELDS

MDL (LABDL):    Sets the MDL (Method Detection Limit) value.  NOTE:  ONLY applies to results entered with a "DNQ" qualifer or a result of "ND" (i.e. ND with no number after it).  If the result is NDx, NMx, or < the MDL will be set to the x.  For example, if the result is ND2 the MDL will be set to 2 (<0.5 would be 0.5, and NM4 would be 4).  However, this field will be used if a DNQx is entered as the x represents the numeric result, not the MDL.  You have the following options to populate the MDL:

	Sets the MDL to the constant specified.
	Will retrieve the MDL from the Data Additional Info field specfied using the AINFO formula.  If disabled, no Data Additional Info fields have been setup.
	The MDL will be pulled from the specified variable using the VT function. Use the  button to pick the variable.

If the 'Populate MDL regardless of qualifier' box is checked, the MDL field will be populated based on the above settings for qualifiers other than "DNQ" and "ND".

ML (REPDL):  The Minimum Level (or Reporting limit) for the method.  This will only be reported with DNQ results.  You have the following options to populate the ML:

	Sets the ML to the constant specified.
	Will retrieve the MDL from the Data Additional Info field specfied using the AINFO formula.  If disabled, no Data Additional Info fields have been setup.
	The MDL will be pulled from the specified variable using the VT function. Use the  button to pick the variable.

If the 'Populate ML regardless of qualifier' box is checked, the ML field will be populated based on the above settings for qualifiers other than "DNQ" and "ND".

Analysis Date (ANADATE):  The date the analysis is started (per Mike Fischer, Central Valley Regional Water Quality Control Board call CIWQS Help center, 1-866-79-CIWQS for more information).   For calculated summaries (i.e. Data Types of Average Monthly (AMEL), 7 day median, etc...) the end date of the summary will be displayed  for the Analysis Date.  For example,  an Average Monthly (AMEL) type will calculate the monthly average for the variable and locate the last day of the month for the Analysis Date.  For Data Type's where a raw result is reported (i.e. Single) the following options are available:

	Will use the Test's Analysis Start Date (LC_SAMPLETEST.DATESTART).
	Will use the Test's Analysis Date Complete (LC_SAMPLETEST.DATECOMPLETE).  Note: It is recommended that the Analysis Start Date is used, however your regional board may require the date complete.  If you are not sure contact your regional board or the CIWQS help center.
	Will add the number of days specified to the Collection/Sample Date.  Enter 0 if you analyze on the same day you take the sample.  Uses the DATEADD function.
	Will retrieve the Analysis date from the Data Additional Info field specfied using the AINFO formula.  If disabled, no Data Additional Info fields have been setup.
	The Analysis Date will be pulled from the specified variable using the VT function. Use the  button to pick the variable.

PET Tool screen shot shows what fields need to be entered (indicated by the ?) when certain data qualifiers are entered.

Rules: These rules are combined from several sources including the esmr2Bus_rules.pdf (attached), CIWQS Help Desk, and customer input:

• Normal Result (i.e. above the RL or ML) - Set Qualifier to "=" and result to numeric result
• < Result - Set Qualifer to "<" and set result to the MDL, user should enter <2 into WIMS where 2 is the MDL.
• ND Result - Set Qualifer to ND, set result to "0" (zero), and set MDL. User should enter ND2 into WIMS where 2 is the MDL.
• DNQ result - Set Qualifer to "DNQ", and set Result, MDL, and either the ML or RL. WIMS supports the ML (REPDL) field and will leave the RL field blank as you only need to fill in the ML or the RL. User should enter DNQ3 into WIMS where 3 represents the Result. The MDL and ML can be gotten from a number of places including Variable UD fields, located as constants, Additional info, etc...

TECH NOTES

• The report type must be set to CIWQS.  This tells WIMS to that it is a CIWQS report and can export it as a CDF. 
• Columns should not be inserted or moved.  WIMS exports the data based on the column position, i.e. the method is in Column E. 
• Rows should not be inserted above Row 1 (i.e. there can only be 1 header row), the tool expects data to start at row w.
• You can use any columns after AA and it will not effect the output of the CDF.
• The Lookup Codes sheet MUST be in the report.  WIMS uses this sheet to lookup the reportable code for methods, parameters etc...  Example, when creating the CDF we lookup the method "Standard Method (19th) 2540 D: Tot. Sus. Solids Dried 103-105C [A2540D]" in Column E to find the MethodCode to report in column D "A2540D".
• Can I rerun the Prep?  Not normally as the prep tool may insert rows that will effect the QC check.  Example, TDS is required once per month and therefore the PET Tool has 1 row for it. The Prep Tool would set Number Required (column AB) to 1. Depending on options selected, the Prep tool would insert a second row for TDS in case you take a second sample in the reporting period. If you reran the prep it would see 2 rows for parameter (section) and would set the number required to 2 which is incorrect.  
• How can I remove the QC checks for a parameter?  If cells AB thru AD are cleared for a parameter the QC check will be skipped. 
• The QC check warns if the following occurs:
• Number of Results (AD) is less than the Number Required (AB)
• Number of Slots(AC) is less than the Number of Results (AD)