Extra Selects

The Extra Selects or @extra_select report parameter is available for advanced users. Extra Selects supplement report output based on data from fields available in the report, as well as any fields added by the Additional Fields parameter (@extra_fields).

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.

Show the Extra Selects Report Parameter

The Extra Select parameter is hidden by default. To unhide the parameter, an EQuIS administrator can do the following:

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

2.Open ST_REPORT_PARAMETER and filter on the REPORT_ID.

3.Change VISIBILITY to Y for the record where PARAM_NAME = @extra_select.

4.Save.

Input Extra Selects

Extra Selects use partial SQL queries (SELECT statements minus the term "SELECT"). The data queried must be from an existing column of the report.

When the report is opened from the Reports button on the Home ribbon, code can be entered into the Extra Selects parameter.

30390_extra_select

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

1.Output a Combination Result or Formatted Result

2.Generate 'Permanent' Output Fields

Include multiple Extra Select statements in a report by separating them with a comma ',', for example:

NULL as LOC_REPORT_ORDER, NULL as MAG_REPORT_ORDER, IsNull(Cast(start_depth as varchar(20)),'') + ' - ' + IsNull(Cast(end_depth as varchar(20)),'') + ' ' + Isnull(depth_unit, '') as [Depth_Range]

Each Extra Select statement will create a new column.

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. Leaving the ST_REPORT_PARAMETER.VISIBILITY as N while the DEFAULT_VALUE so that the Extra Selects parameter is populated but will not be editable by end users.

Warning: When using the Extra Select input statement to create a name for the new column, avoid including the special characters < or > ("less than" or "greater than") in the column name. This will cause a loss of the column data in Enterprise.

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 example, 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

Adding the following to the DEFAULT_VALUE for @extra_selects will create a field called STime with contents like “18:58:00” in output by default:

CONVERT(VARCHAR(8),sample_date,108) AS STime

Generate 'Permanent' Output Fields

The @extra_select parameter can also 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. This can cause notices of mismatches when configuring a published crosstab that was originally designed with groups included, then run without any groups.

After including the following in 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:

NULL as LOC_REPORT_ORDER, NULL as MAG_REPORT_ORDER

Note: Extra Selects can decrease report performance.

Using Functions with Extra Selects

Users can use EQuIS scalar-valued functions (i.e. functions that will return a single value) in Extra Select statements. Stored EQuIS database functions and/or built in SQL functions can perform various calculations like unit conversions in an Extra Select statement.

Some examples of EQuIS functions are the EQuIS Significant Figures functions, equis.significant_figures and equis.significant_figures_get, and the EQuIS unit conversion function equis.unit_conversion.

When using the REPORT_RESULT_TEXT field 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. An Extra Select statement can perform some of these functions for that individual report or user report. The equis.unit_conversion database function uses the following inputs:

equis.unit_conversion(value to convert, unit converting from, unit converting to, value if conversion fails)

Examples

Water Levels II – Convert* water level depths into feet. Specifically, 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

Water Levels II – Round the calculated water depth for a water level measurement involving a light non-aqueous phase liquid (LNAPL) based on the precision of the water level measurement:

equis.significant_figures(WATER_DEPTH,equis.significant_figures_get(WATER_LEVEL_DEPTH),0) as WL_sigfigs

Analytical Results II and dependencies – Convert* REPORT_RESULT_VALUE from the existing REPORT_RESULT_UNIT into mg/L, displaying 9999999 if the unit conversion fails:

equis.unit_conversion(REPORT_RESULT_VALUE, REPORT_RESULT_UNIT, 'mg/l', 9999999) as New_Field

Analytical Results II and dependencies – Round result values from RESULT_NUMERIC based on the number of significant figures from the REPORT_RESULT_VALUE column (generating a formatted value that might be used as a precursor for other analyses). Note that transformed (such as MAG or TEQ sums) or non-detect values would be excluded from the new column.

equis.significant_figures(RESULT_NUMERIC,equis.significant_figures_get(REPORT_RESULT_VALUE),0) as AR_sigfigs

* – Conversion assumes that RT_UNIT and RT_UNIT_CONVERSION_FACTOR tables are populated for the source and target units.

Additional Examples

To provide output based on a value in a specific field:

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

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

To combine different fields with additional formatting:

Chemical_name + ' [' + cast(cas_rn as varchar(15)) + ']' as Long_Analyte

Chemical_name + '-' + fraction as [Analyte by Fraction]

Analytic_method + ' ' + fraction as [Fraction Method]

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

To convert dates:

CONVERT(char(10),sample_date,112) as sample_date_yyyymmdd

CONVERT(char(10),sample_date,101) as sample_date_mmddyyyy

To aggregate data based on each unique fraction, matrix, and reporting unit combination for each analyte:

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

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

To output the calculated difference between two sample locations using LAG and LEAD functions along with PARTITION BY:

LAG(report_result_value) OVER (PARTITION BY sample_date, cas_rn, fraction, report_result_unit ORDER BY sys_loc_code) - report_result_value AS difference

Note that this statement to set up to determine the results between only two locations. It may help to add your locations to a Location Group with just the two locations you intend to use.