<< Click to Display Table of Contents >> Navigation:  Professional > Reports > Configuring Report Parameters > Default or Saved Values

•Example - Set a Dynamic Default Value

•Example - Change the Saved Value of a User Report Parameter

 

Default or saved values for a report parameter appear when the report or user report is loaded based on the ST_REPORT_PARAMETER.DEFAULT_VALUE or ST_USER_REPORT_PARAMETER.PARAM_VALUE fields, respectively. Since a user report’s default values are set upon saving, it is easier to update a user report via the EQuIS Professional report interface or EQuIS Enterprise EZView widget. However, dynamic default values, which use SQL statements, must be added manually.

 

Follow these steps to update a report parameter’s default value or user report parameter's saved value:

1.Find the record for REPORT_ID or USER_REPORT_ID in ST_REPORT or ST_USER_REPORT, respectively.

2.Paste the REPORT_ID into a Filter for ST_REPORT_PARAMETER or ST_USER_REPORT_PARAMETER.

3.For an admin user, check the CAPTION fields to find the PARAM_NAME of the field you wish to edit. If a non-admin user cannot determine the correct PARAM_NAME to edit for their user report, consult an administrator for assistance.

4.For the record with the PARAM_NAME being edited, change ST_REPORT_PARAMETER.DEFAULT_VALUE (for reports) or ST_USER_REPORT_PARAMETER.PARAM_VALUE (for user reports) to the desired value. Dynamic default values must start with the dollar sign character ($). For example, here is a statement that always returns a date of the next day:
$select getdate() + 1

5.Save.

 

Example - Set a Dynamic Default Value

 

The following steps add a SQL select statement to a report’s @sys_loc_codes report parameter (typically appearing in the interface with Locations > Individual[s]) .The report, when loaded, then preselects the individual locations meeting that criteria. The report user could still alter the location selections before running the report, but they would start with a list of recommended values. 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.

1.Copy the REPORT_ID from ST_REPORT, per Find Records.

2.Open ST_REPORT_PARAMETER and filter to your REPORT_ID and PARAM_NAME = ‘@sys_loc_codes’.

3.In the DEFAULT_VALUE field, paste either of the following statements (depending on the server version), updating the DATA_PROVIDER and LOC_TYPE fields as needed:

a.SQL Server 2017 or later, or Azure (such as 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
 

4.Save.

 

Here, an administrator can add other fields from DT_LOCATION to the conditions or replace the existing LOCATION_TYPE and DATA_PROVIDER fields with those.

 

Example - Change the Saved Value of a User Report Parameter

 

This change modifies a @sys_loc_codes report parameter (typically appearing in the interface with Locations > Individual[s]) with a statement to preselect all locations whose names begin with “MW”:

1.Copy the USER_REPORT_ID from ST_USER_REPORT, per Find Records.

2.Open ST_USER_REPORT_PARAMETER and filter to your REPORT_ID and PARAM_NAME = ‘@sys_loc_codes’.

3.In the PARAM_VALUE field, paste:

a.SQL Server 2017 or later, or Azure (such as an EQuIS Online hosted site):
$select STRING_AGG(sys_loc_code, '|') from dt_location where SYS_LOC_CODE like 'MW%' 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 SYS_LOC_CODE like 'MW%' ORDER BY l.sys_loc_code FOR XML PATH(''), TYPE ).value('.','VARCHAR(MAX)') , 1, 1, '') AS sys_loc_codes
 

4.Save.