Column Configuration | Column Configuration File: The Column Configuration input parameters, hidden by default, enable entering partial SQL commands or scripts to customize report output as described below. The Column Configuration works similar to the Extra Selects. The order in which the Column Configuration is run allows more flexibility than the Extra Select, including being able to query Action Levels.
Notes: •The SQL “from”/insert/update/delete commands are not supported. Input data sources are limited to those included with the standard ALEII output. •Due to the additional data processing, column configuration inputs may significantly affect report performance. •If both Column Configuration input parameters are populated, the Column Configuration File (below) will be applied. |
Column Configuration: (hidden by default); Enter partial SQL commands or scripts of less than 4,000 characters into this parameter to customize report output, as described below.
1)Add additional output columns after the standard ALEII output by entering partial SQL scripts. Using Example 1, the ALEII output will include my_flag and my_criteria columns after the standard columns.
Example 1:
my_flag = case when action_level > 0.8*report_result_value then '>0.8r' else flag end, 0.8*report_result_value my_criteria
2)Specify which standard columns to include in the report output by entering ! followed by partial SQL scripts.
Example 2:
! sys_loc_code, sys_sample_code, sample_date, cas_rn, flag, action_level, report_result_text, report_result_unit
3)Combine options 1) and 2) above: i.e. add additional custom logic columns in conjunction with specifying standard columns in the report output.
Example 3:
sys_loc_code, sys_sample_code, sample_date, cas_rn, flag, action_level, report_result_text, report_result_unit, my_flag = case when action_level > 0.8*report_result_value then '>0.8r' else flag end, 0.8*report_result_value my_criteria
Warning: Aliases are needed when fields with the same column names are added. |
Column Configuration File (hidden by default): This parameter functions the same as the 'Column Configuration' parameter, except it supports the partial SQL scripts exceeding 4,000 characters.
Prerequisites for the file input display/selection:
•Upload .txt file(s) with the SQL script via Docs & Photos.
•Set DT_FILE.PLACE_TYPE to “report” for the .txt file record(s).
ACTION_LEVEL_CODE CAS_RN CHEMICAL_NAME FLAG ACTION_LEVEL ACTION_LEVEL_MIN AL_RESULT_TEXT AL_RESULT_VALUE ACTION_LEVEL_UNIT WARNING_FLAG WARNING_LEVEL WARNING_LEVEL_MIN FACILITY_ID FACILITY_CODE SYS_LOC_CODE SAMPLE_NAME SYS_SAMPLE_CODE AL_SUBFACILITY_CODE ACTION_LEVEL_TYPE AL_MATRIX AL_FRACTION AL_ANALYTICAL_METHOD AL_REMARK OBJECT_NAME ACTION_LEVEL_NOTE ACTION_LEVEL_MIN_NOTE LOC_NAME LOC_GROUP LOC_GROUP_CODE LOC_REPORT_ORDER SAMPLE_DATE SAMPLE_TYPE_CODE START_DEPTH END_DEPTH DEPTH_UNIT |
MATRIX_CODE GEOLOGIC_UNIT_CODE TASK_CODE TASK_DESC FIELD_SDG PARENT_SAMPLE_CODE ANALYSIS_LOCATION LAB_SAMPLE_ID LAB_MATRIX_CODE LAB_NAME_CODE ANALYTIC_METHOD ANALYSIS_DATE COLUMN_NUMBER FRACTION TEST_TYPE PREP_METHOD LEACHATE_DATE LAB_SDG PERCENT_MOISTURE DILUTION_FACTOR METHOD_ANALYTE_GROUP REPORT ORDER TEST_ID ORGANIC_YN REPORT_RESULT_TEXT REPORT_RESULT_VALUE REPORT_RESULT_UNIT REPORT_RESULT_LIMIT REPORT_METHOD_DETECTION_LIMIT REPORT_REPORTING_LIMIT REPORT_QUANTITATION LIMIT REPORT REPORTABLE_RESULT DETECT_FLAG INTERPRETED_QUALIFIERS |
VALIDATOR_QUALIFIERS LAB_QUALIFIERS QUANTITATION_LIMIT METHOD_DETECTION_LIMIT REPORTING_DETECTION_LIMIT DETECTION_LIMIT_UNIT APPROVAL_CODE RESULT_TEXT RESULT_NUMERIC RESULT_UNIT RESULT_TYPE_CODE X_COORD Y_COORD Z_COORD_AVG ZFROM ZTO LONGITUDE LATITUDE AL_LAND_USE ALP_REMARK PERMIT_NUMBER ACTION_LEVEL_DESC LOOKUP_SOURCE LOOKUP_CODE LOOKUP_METHOD SAMPLEDATE SAMPLETIME TASK_CODE_2 ACTION_LEVEL_VALUE ACTION_LEVEL_MIN_VALUE WARNING_LEVEL_VALUE WARNING_LEVEL_MIN_VALUE
Extra fields: Any fields chosen |
Tables: DT_ACTION_LEVEL, DT_ACTION_LEVEL_PARAMETER, DT_SAMPLE, DT_TEST, DT_RESULT, DT_SUBFACILITY, DT_LOCATION