How To Customize Additional Fields Parameter

<< Click to Display Table of Contents >>

How To Customize Additional Fields Parameter

This article explains how to add tables and fields to the Additional Fields Parameter. Advanced users who are familiar with how to code SQL queries can add a table and its fields to the drop-down list of the Addition Fields Parameter in certain standard EQuIS reports. This enables selection of the custom additional fields for inclusion in the output of the report.

 

Here is an example of how to add RT_SAMPLE_METHOD table and its fields into the Additional Fields Parameter.

 

Publish the report (if not already published), then navigate to related records in ST_REPORT_PARAMETER.

Copy and paste the value from the ST_REPORT_PARAMETER.DATA_SOURCE field for .PARAM_NAME = @extra_fields into a text document.

 

How To Add Parameter Selection or SQL

 

Replace "order by table_name, column_name", which is at the end of the DATA_SOURCE value for the @extra_fields report parameter, with the query below and then copy and paste the modified query back to the DATASOURCE cell.

 

union select '{"TableName":"RT_SAMPLE_METHOD","JoinCondition":" LEFT OUTER JOIN RT_SAMPLE_METHOD ON DT_SAMPLE.SAMPLE_METHOD = RT_SAMPLE_METHOD.METHOD_CODE","FieldExpression":"METHOD_DESC","FieldAlias":"METHOD_DESC"}','RT_SAMPLE_METHOD', 'METHOD_DESC' order by table_name, column_name

 

Reconnect to the facility, open the report, and select any fields from RT_SAMPLE_METHOD in the Additional Fields Parameter to include them in the output of the report.

 

Here is an example of how to add an EQuIS database function to return a "remapped" value. For this we will use the EQUIS.REMAP_INT_TO_EXT() database function to remap the internal CHEMICAL_NAME to an external CHEMICAL_NAME (which will be given the alias of CHEMICAL_NAME_REMAP). This example also uses JSON to construct additional parts needed for the query generated by the report.

 

Set-up by adding records to the RT_REMAP and RT_REMAP_DETAIL. We will give these a REMAP_CODE = 'DEMO'

RT_REMAP:

REMAP_CODE = DEMO

STATUS_FLAG = A

RT_REMAP_DETAIL:

REMAP_CODE = DEMO

STATUS_FLAG = A

EXTERNAL_FIELD = chemical_name

INTERNAL_VALUE = <value in RT_ANALYTE>

EXTERNAL_VALUE = <new value to map to>

{there can be multiple records mapping various chemical names}
 

Publish the report (if not already published), then navigate to the related records in ST_REPORT_PARAMETER.

Copy/paste the value from the ST_REPORT_PARAMETER.DATA_SOURCE field for .PARAM_NAME = @extra_fields into a text document.

Insert the statement listed below into the statement in the text document, just prior to the "order by.." near the end.

Remove any line breaks, then copy the entire statement back into ST_REPORT_PARAMETER.DATA_SOURCE where the statement was originally copied from (make sure to close and reopen the report, so that cached values are refreshed).

Open the report (e.g. Analytical Results II) and on the Extra Fields parameter, select the item created by the update made here - it should begin with {"TableName:"... and is very long. Then execute the report and the CHEMICAL_NAME_REMAP column should be at the end of the data with remapped chemical names as defined in RT_REMAP_DETAIL.

 

Statement to insert into the text of the DATA_SOURCE field:

 

UNION SELECT
'{"TableName":"RT_ANALYTE"
  ,"JoinCondition":" LEFT JOIN RT_ANALYTE ON DT_TEST.CAS_RN = RT_ANALYTE.CAS_RN"
  ,"FieldExpression":"EQUIS.REMAP_INT_TO_EXT( CHAR(68)+CHAR(69)+CHAR(77)+CHAR(79), CHAR(99)+CHAR(104)+CHAR(101)+CHAR(109)+CHAR(105)+CHAR(99)+CHAR(97)+CHAR(108)+CHAR(95)+CHAR(110)+CHAR(97)+CHAR(109)+CHAR(101), RT_ANALYTE.CHEMICAL_NAME)"
  ,"FieldAlias":"CHEMICAL_NAME_REMAP"
 }'
, 'RT_ANALYTE', 'CHEMICAL_NAME_REMAP'

 
Explanation of the Statement

 

First line: "UNION SELECT" adds this into the extra fields that can be selected for the report.

Second line: "..{"TableName":..." indicates the table that will be added into the SQL query. Note that the opening curly bracket starts the JSON statement, and the ending curly bracket later completes it.

Third line: "JoinCondition...." tells the query how to make the join in the table. Note if there are constant values involved in JoinCondition, double quotes or not single quote must be used, e.g. the below is to add DT_COORDINATE table to Additional Fields (SQL), where the double quotes are used around PRIMARY and  %LAT%LONG%.

 

union select '{"TableName":"DT_COORDINATE","JoinCondition":"INNER JOIN DT_COORDINATE ON dt_coordinate.facility_id = dt_location.facility_id AND dt_coordinate.sys_loc_code = dt_location.sys_loc_code AND DT_COORDINATE.facility_id = dt_location.facility_id and dt_coordinate.identifier = ''PRIMARY'' and dt_coordinate.coord_type_code LIKE ''%LAT%LONG%''","FieldExpression":"DT_COORDINATE.CUSTOM_FIELD_1","FieldAlias":"CF1_INFO"}','DT_COORDINATE','COORDINATE_INFO' order by table_name, column_name

 

Fourth line: "..FieldExpression..." defines the actual value that will be returned for the additional field. In this case, we are using the EQUIS.REMAP_INT_TO_EXT() database function. This function takes the following three parameters:

1.REMAP_CODE - the code in RT_REMAP and RT_REMAP_DETAIL for the remapping we will use. This will be hard-coded to match the REMAP_CODE stored in the database, i.e. you should change this to match the REMAP_CODE used in your database.
*Also note, in this example, the CHAR() function is used to build the string to prevent possible issues from the single-quotes surrounding the hard-coded value.

2.EXTERNAL_FIELD - this is the name of the "external field" in terms of the AR report, it is simply the the hard-coded value 'chemical_name'.  
*As with the previous parameter, this was replaced with the concatenation of the CHAR() function.

3.INTERNAL_VALUE - this is the value stored in the database that will be remapped. For this example, it is the the value in the RT_ANALYTE table, CHEMICAL_NAME field.

 

Note: Hard-coding string values in SQL are generally done by placing single quotes around the string, however, for this example the hard-coded stings passed to the EQUIS.REMAP_INT_TO_EXT function were replaced with the equivalent values using CHAR(...) function for each character and concatenated together, e.g. the first parameter 'DEMO' is replaced with:

 

CHAR(68)+CHAR(69)+CHAR(77)+CHAR(79)

 

(68, 69, 77, and 79 are the decimal values to pass to the function for the letters D, E, M, and O respectively).

 

Fifth line: defines the name of the new extra column or alias. Just after this, there is a closing curly bracket indicating the end of the JSON.

Final line: includes the table and column name for the extra field.

 

The steps above can be used to add any of the following tables to the Additional Fields Parameter:

 

DT_COLLECT_PROC

DT_EQUIPMENT

DT_PERMIT

DT_PHASE

DT_SPATIAL_EXTENT

RT_ANALYTE_DETAIL

RT_BASIN

RT_COMPANY

RT_COORD_TYPE

RT_DQM_STATUS

RT_EQUIPMENT_TYPE

RT_FRACTION

RT_GEOLOGIC_UNIT

RT_LAND_USE

RT_LOCATION_TYPE

RT_MATERIAL

RT_MATRIX_CLASS

RT_MEDIUM

RT_PREP_METHOD

RT_PRESERVATIVE

RT_RESULT_PARAM_TYPE

RT_RESULT_TYPE

RT_SAMPLE_METHOD

RT_SAMPLE_PARAM_TYPE

RT_STATE

RT_STREAM

RT_SUBFACILITY_TYPE

RT_TASK_TYPE

RT_TAXONOMY

RT_TEST_TYPE

RT_TIMEZONE

RT_UNIT

RT_VALID_CODE

RT_WELL_SEGMENT_TYPE

ST_GROUP_TYPE

DT_COORDINATE

RT_ANALYTE