ST_REPORT_PARAMETER
When a report is published, it creates an entry in the ST_REPORT table, with child entries using the same ST_REPORT.REPORT_ID in the ST_REPORT_PARAMETER table. These tables are viewable and configurable by EQuIS Administrators.
Published report parameters can be customized in the ST_REPORT_PARAMETER table, using the following fields:
•PARAM_TYPE - the type of characters accepted for input, lining up with SQL Server data types, such as:
odatetime - date and time
odecimal, float, real - numeric input data types of varying size and precision
oint - whole number (non-decimal) numeric input, typically used for EQuIS ID fields such as FACILITY_ID or EBATCH
ovarchar - string input of characters, e.g. an Extra Select SQL statement
•PARAM_ORDER - the order in which the parameters appear, although the tree view defined in the caption takes precedence
•DEFAULT_VALUE - the selection that is already made for the parameter upon opening the published report
•CAPTION - the report name as well as a dynamically generated nesting structure of where parameters will appear
•DISPLAY_TYPE - the selection options, such as:
oBoolean - displays a check box for a yes/no parameter
oDate - displays a date section with a pop-up calendar from which a date may be selected
oMultiSelect - displays a list of selections, of which multiples may be chosen (EQuIS will separate these with the pipe character, |, e.g. "MW1|MW2")
oSingleSelect - displays a list of selections, of which one may be chosen
oText - allows the freeform entry of text characters
•REMARK - the description of the parameter that will appear in the right pane of the report interface once the parameter is selected. Note that custom, user-added parameters have specific requirements as to the content of this field.
•VISIBILITY - whether a parameter is shown when opening the report
•DATA_SOURCE - the query used to display options for the parameter. The parameters listed on the SQL Query Reports page are already defined and do not require a data source to be populated.
•REQUIRED_YN - whether a parameter selection must be specified for the report to be run.
For specifics on customizing the Additional Fields report parameter to add more fields, see the How To Customize Additional Fields Parameter page.
Note: When a SQL Query report is published, the display_type and data_source for each parameter that is not pre-defined may not display or run as expected without further configuration. |
ST_USER_REPORT_PARAMETER
When a user report is saved for a published report, an entry is created in ST_USER_REPORT using the same ST_REPORT.REPORT_ID value, with child entries based on USER_REPORT_ID created in ST_USER_REPORT_PARAMETER:
While only the PARAM_VALUE is configurable here, it can be treated the same as the ST_REPORT_PARAMETER.DEFAULT_VALUE field for customization purposes. Editing fields in ST_REPORT_PARAMETER affects the report for all users of the database; consequently, configuring ST_USER_REPORT_PARAMETER, which requires loading that particular user report, may be a safer option if only the initial selections for the report require editing.
Examples
ST_REPORT_PARAMETER.DATA_SOURCE
Some parameter selections display a table with multiple columns, such as individual analyte parameters showing both the CAS_RN and CHEMICAL_NAME or individual locations showing the SYS_LOC_CODE and LOC_NAME. A SELECT statement can be expanded to include additional fields of interest, such as LOC_TYPE. This then allows the user to sort and select based on values in this field. For example:
1.Open ST_REPORT_PARAMETER and filter to your REPORT_ID and PARAM_NAME = ‘@sys_loc_codes’.
2.In the DATA_SOURCE field, paste:
select sys_loc_code, loc_name, loc_type, data_provider from dt_location where facility_id in (select facility_id from equis.facility_group_members(@facility_id))
3.Save.
This statement allows the SYS_LOC_CODE, LOC_NAME, LOC_TYPE, and DATA_PROVIDER fields from DT_LOCATION to be provided for the active facility or facility group for the @sys_loc_code parameter.
ST_REPORT_PARAMETER.DEFAULT_VALUE
By adding a SELECT statement to ST_REPORT_PARAMETER.DEFAULT_VALUE for the @sys_loc_codes report parameter, the report interface would preselect the individual locations that meet the goals for that report. The report user could still alter the location selections before running the report, but they would start with a list of recommended values.
1.Open ST_REPORT_PARAMETER and filter to your REPORT_ID and PARAM_NAME = ‘@sys_loc_codes’.
2.In the DEFAULT_VALUE field, paste either of the following statements, filling in the DATA_PROVIDER and LOC_TYPE fields as desired, depending on the server version.
a.SQL Server 2017 or later, or Azure (e.g. an EQuIS Online hosted site):
$select STRING_AGG(sys_loc_code, '|') from dt_location where data_provider = 'MyDataProvider' AND loc_type = 'MyLocType' and facility_id in (select facility_id from equis.facility_group_members(@facility_id))
b.Older versions of SQL Server supported by EQuIS, or unknown server version:
$SELECT STUFF(( SELECT '|' + LTRIM(RTRIM(l.sys_loc_code)) FROM dbo.dt_location l WHERE l.facility_id in (SELECT facility_id FROM equis.facility_group_members(try_cast(@facility_id as varchar(20)))) AND l.loc_type = 'MyLocType' AND l.data_provider = 'MyDataProvider' ORDER BY l.sys_loc_code FOR XML PATH(''), TYPE ).value('.','VARCHAR(MAX)') , 1, 1, '') AS sys_loc_codes
3.Save.
This statement preselects all locations that meet the criteria of having ‘MyLocType’ for location type and ‘MyDataProvider’ for data provider in the report chooser upon opening the report. The DATA_PROVIDER and LOC_TYPE fields can be replaced by others, as desired.
ST_USER_PARAMETER.PARAM_VALUE
Similar to modifying the ST_REPORT_PARAMETER.PARAM_VALUE field:
1.Open ST_REPORT_PARAMETER and filter to your REPORT_ID and PARAM_NAME = ‘@sys_loc_codes’.
2.In the PARAM_VALUE field, paste:
select sys_loc_code, loc_name, loc_type, data_provider from dt_location where facility_id in (select facility_id from equis.facility_group_members(@facility_id))
3.Save.