Copyright © 2020 EarthSoft, Inc • Modified: 24 Feb 2020
The Extra Selects or @extra_select report parameter is available for advanced users to configure in ST_REPORT_PARAMETER, and allows for supplementing report output using the fields available in the Additional Fields parameter. There are two methods for using the @extra_select report parameter, both of which involve populating the DEFAULT_VALUE field for the @extra_select report parameter.
Note: Extra Selects are included when you save a report configuration Parameter File (.xml). If you are running a report using a saved Parameter File (.xml), you will need to create a new Parameter File (.xml) for any subsequent changes you make to the Extra Selects for it to be reflected in your report output.
1. Output a combination result or formatted result
The first option allows users to add a column to the report which includes a combination of fields or a field with special formatting.
For example, for PARAM_NAME = @extra_select, if ST_REPORT_PARAMETER.DEFAULT_VALUE of a SQL Database is populated with
IsNull(Cast(start_depth as varchar(20)),'') + ' - ' + IsNull(Cast(end_depth as varchar(20)),'') + ' ' + Isnull(depth_unit, '') as [Depth_Range]
a column of Depth_Range with contents like “15-20 m” is added to the report output grid.
If “CONVERT(VARCHAR(8),sample_date,108) AS STime” is added to the DEFAULT_VALUE, a field called STime with contents like “18:58:00” will be included in the output.
2. Generate 'permanent' output fields.
The @extra_select parameter may be also used to generate 'permanent' output fields. For example, if Location Group(s) or Result>Analyte>Group(s) (also known as Method Analyte Groups, or "MAGs") parameters are not selected in the Analytical Results II Report, information associated with MAGs or Location Groups such as MAG_REPORT_ORDER or LOC_REPORT_ORDER will not be included in the report output.
After “NULL as LOC_REPORT_ORDER, NULL as MAG_REPORT_ORDER” is added to the DEFAULT_VALUE of the @extra_select, the fields of LOC_REPORT_ORDER and MAG_REPORT_ORDER will always be present in the output, regardless of whether there are any MAGs or Location Groups selected.
Note: Extra Selects can decrease report performance.
•Case when SYS_SAMPLE_CODE LIKE '%ABC%' THEN 'xx' ELSE NULL end as FINAL_SYS_SAMPLE_CODE
•Case when detect_flag like 'Y%' then [report_result_value] Else null End as [NEW_report_result_value]
•Case when detect_flag like 'N%' then [report_result_value] Else null End as [NEW_report_limit_value]
•Case when detect_flag like 'N%' then [report_result_value]*0.5 Else [report_result_value] End as NEW_report_graph_value
•Chemical_name + ' [' + cast(cas_rn as varchar(15)) + ']' as Long_Analyte
•Chemical_name + '-' + fraction as [Analyte by Fraction]
•Analytic_method + ' ' + fraction as [Fraction Method]
•CONVERT(char(10),sample_date,112) as sample_date_yyyymmdd
•CONVERT(char(10),sample_date,101) as sample_date_mmddyyyy
•CASE WHEN start_depth IS NULL AND end_depth IS NULL THEN NULL WHEN end_depth IS NULL THEN CAST(start_depth AS VARCHAR(20)) + ' ' + COALESCE(depth_unit,'') WHEN start_depth IS NULL THEN ' - ' + CAST(end_depth AS VARCHAR(20)) + ' ' + COALESCE(depth_unit,'') ELSE CAST(start_depth AS VARCHAR(20)) + ' - ' + CAST(end_depth AS VARCHAR(20)) + ' ' + COALESCE(depth_unit, '') END AS depth_range
Note: Multiple extra select statements can be included in a report if a comma is used ',' to separate them.
IsNull(Cast(start_depth as varchar(20)),'') + ' - ' + IsNull(Cast(end_depth as varchar(20)),'') + ' ' + Isnull(depth_unit, '') as [Depth_Range] , NULL as LOC_REPORT_ORDER, NULL as MAG_REPORT_ORDER
When using REPORT_RESULT_TEXT in an Extra Select statement, some formatting and transformations (such as unit conversions, number formatting, and multipliers) will not be applied to the resulting string. However, it is still possible to create an Extra Select statement that performs some of these functions specifically tailored to your individual report/report. The stored EQuIS database functions and/or built in SQL functions can perform various calculations like unit conversions in an Extra Select Statement. The equis.unit_conversion database function can be used with the following inputs:
equis.unit_conversion(value to convert, unit converting from, unit converting to, value if conversion fails)
The following is a simple example of an Extra Select statement that could be used to convert results into mg/l (assuming that RT_UNIT and RT_UNIT_CONVERSION_FACTOR tables are populated for the source and target units). This examples converts REPORT_RESULT_TEXT to a number, then converts it from the units in DT_RESULT.RESULT_UNIT to mg/l, displaying 9999999 if the unit conversion fails:
equis.unit_conversion(Try_Cast(REPORT_RESULT_TEXT as float) , RESULT_UNIT, 'mg/l', 9999999) as New_Field