Extra Selects

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



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.


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.


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.


Note: Extra Selects can decrease report performance.


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


Note: Multiple extra select statements can be included in a report if a comma is used ',' to separate them.
For example:

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