The Locate Alberta eReport option allows the user to design a report to output a data file to submit electronic sample/measurement data to Alberta Environment (AENV).  The eReport option supports the Lab-OPR file ("laboratories may analyze samples provided to them by Operators of Drinking Water facilities, and then submit the data to AENV on behalf of the Operator. These Lab-Operator files are identified as “Lab-Opr” rules.").  See Alberta Lab-OPR file structure for more information.

Starting File Number: Allows users to specify the file number to be used on the next generated Alberta eDMR report.   NOTE: When the report is output using Report Pac, Spread Reports, eDMR / Electronic Report File option, this cell will automatically be incremented.       

Typically a user would only open the spread report to adjust the next report file number.

Alberta requires eReport filenames to be:

• Sequentially numbered
• Six digits long
• Padded with 0's to the left of any file number not six digits in length
• Example: 1st file would be 000001.M027, 2nd 000002.M027, 3rd 000003.M027 etc...

File name extension: Specifies the eReports file extension.  The file name extension must be an uppercase “M” followed by the 3-digit lab code assigned to the lab.  See attached FTP_Codes_EXPs.pdf for available Lab Codes (as of Jan 2013). 

SQL Query Where Statement: Allows advanced users to specify the SQL Where Statement, thereby changing which Lab Cal Samples are reported.  Example Query with setting of TASKUD1='Y': 

• The field is entered just after the Where keyword is used in the built-in SQL statement.
• The default where statement is: TASKUD1 = 'Y'
• The tables opened via the from statement are:

• LC_SAMPLE 

• LC_SAMPLETEST

• LC_SAMPLEDEF

• VARDESC

SELECT LC_SAMPLE.AUDITTIMESTAMP, LC_SAMPLE.SAMPLEDTTIME, LC_SAMPLE.SAMPLENUM, LC_SAMPLE.ID, LC_SAMPLE.NOTES, LC_SAMPLETYPE.NAME, LC_SAMPLEDEF.TASKUD2, LC_SAMPLEDEF.TASKUD3, LC_SAMPLEDEF.TASKUD4, LC_SAMPLEDEF.TASKUD5, LC_SAMPLEDEF.NOTES AS APPROVAL,LC_SAMPLE.NOTES FROM LC_SAMPLE , LC_SAMPLEDEF , LC_SAMPLETYPE Where TASKUD1 = 'Y' AND LC_SAMPLEDEF.ID = LC_SAMPLE.SAMPLEDEFID AND LC_SAMPLE.SAMPLETYPEID = LC_SAMPLETYPE.ID AND LC_SAMPLE.SAMPLEDTTIME >= '01/12/2012' AND LC_SAMPLE.SAMPLEDTTIME < '01/01/2013'

NOTE:  Locate Alberta eReport will also change the Report Type to "Alberta eDMR" in Report Options.

MAPPINGS

The following tables show how each field of the OPR records are populated by fields in WIMS/Lab Cal.  The LAB-OPR file is comprised of 3 record types (Sample Header Record, Measurement Detail Record, and Sample Header Comment Record). 

Sample Header Record

Example Record:

S000001          20110207095000                            20110209093200              118BA43503             AB05BM1422          10          1                        17151                                    ANNUL  

 

Field Name	Description	Mapping
Record Type	Sample Header	Auto set to "S"
Record Number	Sequential Number of each record in the file	Auto set by WIMS
Sample Number	Sample Number issued by AENV (not required in Lab-OPR file)	Auto Set to "" ie blank
Sample Date	Date Sample was taken in YYYYMMDDHHMISS format	Lab Cal Sample Date / Time (LC_SAMPLE.SAMPLEDTTIME)
Sample End Date	Not required in Lab-OPR file	Auto Set to "" ie blank
Send Date	Not required in Lab-OPR file	Auto Set to "" ie blank
Received Date	Date Sample was received by Lab.	Lab Cal Sample Date / Time (LC_SAMPLE.SAMPLEDTTIME)
Returned Date	Not required in Lab-OPR file	Auto Set to "" ie blank
Lab Code	ID Code of lab where measurements are done	Lab Cal Sample Definition User Defined field 2.  LC_SAMPLEDEF.TASKUD2.   See Sample Setup for setting the User Defined Fields.
Lab Sample Number	Sample number used internally by the lab	Lab Cal Sample Number (LC_SAMPLE.SAMPLENUM)
Station No.	Station number, issued by AENV, where sample was obtained	Lab Cal Sample Definition User Defined field 3.  LC_SAMPLEDEF.TASKUD3.   See Sample Setup for setting the User Defined Fields.
Project No.	Not required in Lab-OPR file
Agency Code	Not required in Lab-OPR file
Sample Matrix Code	AENV Code to id the sample matrix. Valid values are 10 - Treated; 6 - Raw; 24 - Partially Treated	Lab Cal Sample Definition User Defined field 4.  LC_SAMPLEDEF.TASKUD4.  Uses only the first 2 characters of the field, i.e. if you set UD4 to 10 - Treated, 10 will be used.
Number Caught	Not required in Lab-OPR file
Number Kept	Not required in Lab-OPR file
Sample Type Code	AENV Code that identifies the type of sample. Valid Values: 1 - Discrete / Grab, 19 - Calculated, 96 - Continous, 32 - Bacti Resample, 33 - Bacti Resample NOT for protocol, 35 - Sample not for compliance	Lab Cal Sample, Sample Type Setting.  Uses the number in Parenthesis of the LC_SAMPLE.SAMPLETYPE Field.   Use Sample Type Setup to enter your sample types with the AENV codes in parenthesis.  Example for grab:
Collection Code	Not required in Lab-OPR file
Group Sample No	Not required in Lab-OPR file
Sample Cross Ref	For Labs, if the data is associated with a particular approval (e.g. for bacteriological data), this field will contain the approval id associated with the measurement.	Lab Cal Sample Definition Notes.  Enter the Approval as Approval=AB05BM1422
Sample Depth	Not required in Lab-OPR file
Sampler ID 1	Not required in Lab-OPR file
Sampler ID 2	Not required in Lab-OPR file
Sampler ID 3	Not required in Lab-OPR file
Sample Frequency Code	Code to indicate the frequency of the sampling. Annual = ANNUL (annual or semi annual chemistry samples) Daily = DAILY Monthly = MONTH (calendar month) Weekly = WEEK (usually a 7 day week) Weekly (except Statutory holidays) = WKGW (this typically for small groundwater operations that can have a 5 day week)	Lab Cal Sample Definition User Defined field 5.  LC_SAMPLEDEF.TASKUD5:
Reading Type	Not required in Lab-OPR file

Measurement Detail Record

Example Record:

M000004BA43503             000000002            20110210105408106221000000.00050L

Field	Decription	Mapping
Record Type	M (if standard measurement record) B (if biological measurement record)	Always "M"
Record Number	Sequential Number of each record in the file	Autoset by WIMS
Lab Sample Number	Sample Number used internally by the lab	Lab Cal Sample Number
Measurement No.	Measurement sequence number within a sample	Autoset by WIMS
Project No.	N/A	Autoset to ""
Tissue Item No.	N/A	Autoset to ""
Measurement Date	Date Measurement was made	LC_SAMPLETEST.DATECOMPLETE
VMV Parameter Code for Measurement	VMV parameter code for measurement	Variable's Storetcode field.  VARDESC.STORETCODE
Value	Numeric Result	Variable's Result for Sample/Analyte.
Flag	Flag to qualify measurement value	If WIMS Result is "<", report as "L".  If WIMS Result is ">", report as "G".  Otherwise report as blank.
Preatreatment Code	N/A	Autoset to ""
Sample Detect Limit	Detection limit for measurement	Autoset to ""
Value Type Code	N/A	Autoset to ""
Qualifier 1	Qualifier for measured value	Autoset to "".  If reported the following list shows the valid values: <RDL LESS THAN RELIABLE DETECTION LIMIT >MRV VALUE IS GREATER THAN MRV BUT LESS THAN MDL B ANALYTE FOUND IN BLANK AND SAMPLE. BLANK VALUE SUBRACTED BNS ANALYTE FOUND IN BLANK AND SAMPLE(BLANK NOT SUBRACTED) CG CONFLUENT GROWTH CR CORRECTED FOR RECOVERY DR DILUTION REQUIRED TO ANALYZE THE SAMPLE AND MDL ADJUSTED ACCORDINGLY E ESTIMATE - LESS THAN MDL FSE FALLS WITHIN STANDARD ERROR HT HOLDING TIME EXCEEDED HTA ANALYSIS TIME EXCEEDED (21 DAYS) HTE EXTRACTION TIME EXCEEDED (7 DAYS) I ALL CONFORMATION IONS NOT PRESENT ISB INAPPROPRIATE SAMPLE AMOUNT OR BROKEN ISH INSUFFICIENT HEAD SPACE IST INAPPROPRIATE SAMPLE TEMPERATURE M CALCULATED BY AN ALTERNATE RAW DATA SET QIR QUALIFYING IONS PRESENT BUT FAILED THE ION RATIO LIMITS R REPLICATE CRITERIA NOT MET RER RERUN/RECHECK OF MEASUREMENT SPK VARIABLE HAS BEEN SPIKED SPNF STANDARD PROCEDURE NOT FOLLOWED SUS VALUE IS SUSPECT TAM TEMPORARY ALTERNATE METHOD VER VALUE HAS BEEN VERIFIED
Qualifier 2
Qualifier 3
Qualifier 4
Qualifier 5
Qualifier 6
Qualifier 7
Missing Meas Code		Set to "" by WIMS.  If reported the following list shows the valid values: Analyzer Broken = ANB Laboratory Error = LBE Transposing Error = TPE Either / Or Missing Measurement (re; Chlorine Residual) = EIO

BUTTONS:

OK - Accepts the entries and places them into the Spread Report

Cancel - Closes the form without loading any changes

Restore Defaults - Resets the starting number to 000001, File Extension to M118, and the Where Statement to TASKUD1 = 'Y'

Help - Pulls up a knowledgebase article to further assist users

 

 

For assistance with setting up and producing the Alberta eDMR report, please see Q13998 - HOWTO: Produce Alberta eDMR Report