Customize Additional Fields Parameter

<< Click to Display Table of Contents >>

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.

 

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

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

 

For SQL, replace ") order by table_name, column_name", which is at the end of the datasource of @extra_field", with the query below, and then copy/paste the modified query back to the DATASOURCE cell.

 

,'RT_SAMPLE_METHOD') 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

 

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

 

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

2.RT_REMAP: REMAP_

a.CODE = DEMO

b.STATUS_FLAG = A

3.RT_REMAP_DETAIL:

a.REMAP_CODE = DEMO

b.STATUS_FLAG = A

c.EXTERNAL_FIELD = chemical_name

d.INTERNAL_VALUE = <value in RT_ANALYTE>

e.EXTERNAL_VALUE = <new value to map to>

 

Note that there can be multiple records mapping various chemical names.
 

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

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

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

7.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 re-open the report so that cached values are refreshed).

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

 

The statement to insert into the text of the DATA_SOURCE field is shown below.

 

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 squiggly bracket starts the JSON statement, and the ending squiggly 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%.

 

,'DT_COORDINATE') 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 three parameters listed below.

 

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. change this to match the REMAP_CODE used in the 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 - the name of the external field, for 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 - 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 strings 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 as shown below.

 

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. Right after this there is a closing squiggly brace indicating the end of the JSON.

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