Home : Locate Alberta eReport
Q13997 - INFO: Locate Alberta eReport

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

 

 

Related Articles
Q13998 - HOWTO: Produce Alberta eReport

Article Attachments
Lab-DWQ_Data_File_Format062003.pdf
FTP_Codes_EXPs.pdf

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 11/6/2012 11:32 AM.
Last Modified on 12/1/2014 12:34 PM.
Last Modified by Scott Dorner.
Skill Level: Intermediate.
Article has been viewed 3648 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article