<< Click to Display Table of Contents >> Navigation:  EQuIS Library > Reports > Extra Selects

The Extra Selects or @extra_select report parameter is available for advanced users. With Extra Selects, report output can be supplemented using data from fields available in the report as well as any fields added by the Additional Fields parameter. The Extra Select parameter is hidden by default. To unhide the parameter, an EQuIS administrator can use the following steps:

1.Open ST_REPORT to find your desired report's REPORT_ID.

2.Open ST_REPORT_PARAMETER and filter on the REPORT_ID.

3.Change VISIBILITY to Y for the record with PARAM_NAME: @extra_select.

4.Save.

 

When the report is opened from the Reports button on the Home ribbon, code can be entered into the Extra Selects parameter. The DEFAULT_VALUE field within ST_REPORT_PARAMETER for the @extra_select parameter can be populated with specific code to auto-populate the parameter each time that the report is opened. It is also possible to populate the DEFAULT_VALUE but leave the ST_REPORT_PARAMETER.VISIBILITY as N so that the Extra Selects parameter is populated but will not be editable by end users.

 

 

There are two methods for using the @extra_select report parameter:

1.Output a Combination Result or Formatted Result

2.Generate 'Permanent' Output Fields

 

Information on using functions with extra selects and additional examples are provided below.

 

For similar Extra Selects functionality using action level columns in the Action Level Exceedance II Report, use the Column Configuration Report parameters.

 

 

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.  

 

Dates and times can also be converted to different formats using SQL Server conversion codes. for exampl:

•The following query converts a date into dd Mon yy format, e.g. 31 Dec 21:
CONVERT(char(10),sample_date, 6) as sample_date_ddMonyy

•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.

 

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.

 

 

Using Functions with Extra Selects

 

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 water level depths into feet (assuming that RT_UNIT and RT_UNIT_CONVERSION_FACTOR tables are populated for the source and target units). This example converts WATER_LEVEL_DEPTH to a number, then converts it from the units in DT_WATER_LEVEL.DEPTH_UNIT to ft, displaying 9999999 if the unit conversion fails:

equis.unit_conversion(WATER_LEVEL_DEPTH, DEPTH_UNIT, 'ft', 9999999) as New_Field

 

Additional Examples

•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

•CASE WHEN report_result_unit LIKE '%ug/l%' THEN 'ug/L' ELSE [report_result_unit] END AS [unit_formatted]

•avg(case when detect_flag = 'Y' then report_result_value else null end) over (partition by cas_rn, fraction, matrix_code, report_result_unit) as AVG_DETECT_VALUE

oProvides the mean of each unique fraction, matrix, and reporting unit combination for each analyte.

•max(case when detect_flag = 'Y' then report_result_value else null end) over (partition by cas_rn, fraction, matrix_code, report_result_unit) as MAX_DETECT_VALUE

oProvides the maximum value of each unique fraction, matrix, and reporting unit combination for each analyte.