•How to Add Parameter Selection or SQL
This article explains how an EQuIS administrator can 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 Additional Fields Parameter in certain standard EQuIS reports. Then, users can select custom additional fields to output in the report.
For more information on customizing report parameters, see Customize Report Parameters.
How to Add Parameter Selection or SQL
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 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.
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.
Due to how EQuIS Professional reads report parameters, the string being added must appear in the EXTRA_FIELD column of the Additional Fields report parameter selector, as shown below. Use the TABLE_NAME and COLUMN_NAME to search for your added parameter, if needed.
How to Add a Remapped Value
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.
1.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}
2.Publish the report (if not already published), then navigate to the related records in ST_REPORT_PARAMETER.
3.Copy/paste the value from the ST_REPORT_PARAMETER.DATA_SOURCE field for .PARAM_NAME = @extra_fields into a text document.
4.Insert the statement listed below into the statement in the text document, just prior to the "order by.." near the end.
5.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).
6.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 |
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_COORDINATE
•DT_EQUIPMENT
•DT_PERMIT
•DT_PHASE
•DT_SPATIAL_EXTENT
•RT_ANALYTE
•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