oExample: Locations by Parent Location
oExample 1: Action Levels by Action Level Type
oExample 2: Locations by Subfacility
oExample 3: Locations by Location Type
Report parameters for a list of selections (such as ST_REPORT_PARAMETER.DISPLAY_TYPE = 'SingleSelect' or 'MultiSelect') can display a tree, instead of a list. To accomplish this, set the SELECT statement in ST_REPORT_PARAMETER.DATA_SOURCE, as described below. For more details on configuring this field, see Populate Parameter Selection List (DATA_SOURCE).
Note: Previous releases of EQuIS had a SQL query vulnerability that could be exploited by malicious users with access to the system. Further details about the vulnerability are not provided here for security purposes but are available upon request. |
Using Items in Groups
If items are already in a group (using RT_GROUP and RT_GROUP_MEMBER), use the EQUIS.GROUP_MEMBERS_TREE function in the SELECT statement in the ST_REPORT_PARAMETER.DATA_SOURCE, as follows:
SELECT MEMBER_CODE, MEMBER_DESC, GROUP_CODE, GROUP_DESC, GROUP_TYPE, DEPTH, DISPLAY_ORDER FROM EQUIS.GROUP_MEMBERS_TREE('GroupCode1|GroupCode2|GroupCode3', @FACILITY_ID) --group_members_tree
Notes: •Within the DATA_SOURCE, add either /* group_members_tree */ at the beginning or •Loading the parameter tree may fail if MEMBER_CODE = MEMBER_DESC = GROUP_CODE. Avoid using this setup in a parameter tree or (if feasible) in any groupings. |
Example: Locations by Parent Location
To browse locations in a tree of all parent location codes within certain groups (here, GroupCode1, GroupCode2, and GroupCode3), find the @sys_loc_codes parameter and set the ST_REPORT_PARAMETER.DATA_SOURCE as follows:
SELECT l.sys_loc_code as member_code, l.sys_loc_code as member_desc, coalesce(l.parent_loc_code,'(All)') as group_code, coalesce(l.parent_loc_code,'(All)') as group_desc, 'parent_loc_code' as group_type, 0 as depth, 1 as display_order from dt_location L where facility_id in (select l.facility_id from equis.group_members_tree('GroupCode1|GroupCode2|GroupCode3', @facility_id)) order by group_desc, member_desc --group_members_tree
In the pictured example below, SB-01 and SB-02 are members of GroupCode1, GroupCode2, and/or GroupCode3.
Using Ungrouped Data
User can also present non-group data as a tree. To accomplish this, set ST_REPORT_PARAMETER.DATA_SOURCE to return the same output columns as the EQUIS.GROUP_MEMBERS_TREE function:
•MEMBER_CODE: The code of the item (the value passed into the report).
•MEMBER_DESC: The display text or description of the item ( what the user will see).
•GROUP_CODE: The code of the group (e.g. RT_GROUP.GROUP_CODE).
•GROUP_DESC: The display text or description of the group ( what the user will see).
•GROUP_TYPE: The type of group (e.g. RT_GROUP.GROUP_TYPE).
•DEPTH: The depth of the node in the resulting tree (used to organize the tree).
•DISPLAY_ORDER: the order of the item within its parent.
Notes: •Within the DATA_SOURCE, add either /* group_members_tree */ at the beginning or •Loading the parameter tree may fail if MEMBER_CODE = MEMBER_DESC = GROUP_CODE. Avoid using this setup in a parameter tree or (if feasible) in any groupings. |
Example 1: Action Levels by Action Level Type
To display Action Levels in a tree organized by ACTION_LEVEL_TYPE, place the following SELECT statement in the DATA_SOURCE of the @action_level_codes report parameter:
/* group_members_tree */ SELECT AL.ACTION_LEVEL_CODE as MEMBER_CODE, AL.ACTION_LEVEL_CODE as MEMBER_DESC, T.ACTION_LEVEL_TYPE as GROUP_CODE, coalesce (T.ACTION_LEVEL_TYPE_DESC, T.ACTION_LEVEL_TYPE) as GROUP_DESC, 'ACTION_LEVEL_TYPE' as GROUP_TYPE, 0 as depth, 1 as DISPLAY_ORDER from RT_ACTION_LEVEL_TYPE t inner join DT_ACTION_LEVEL al on T.ACTION_LEVEL_TYPE = AL.ACTION_LEVEL_TYPE where al.facility_id in (select facility_id from equis.facility_group_members(@facility_id)) or (AL.FACILITY_ID is null) order by GROUP_DESC, MEMBER_DESC
Example 2: Locations by Subfacility
To browse locations in a tree organized by subfacility instead of as a list, find the @sys_loc_codes parameter and set the
ST_REPORT_PARAMETER.DATA_SOURCE as follows:
SELECT L.SYS_LOC_CODE as MEMBER_CODE, L.SYS_LOC_CODE as MEMBER_DESC, S.SUBFACILITY_CODE as GROUP_CODE, coalesce (S.SUBFACILITY_NAME,S.SUBFACILITY_CODE) as GROUP_DESC, 'subfacility' as GROUP_TYPE, 0 as depth, 1 as DISPLAY_ORDER from DT_SUBFACILITY s inner join DT_LOCATION L on S.FACILITY_ID = L.FACILITY_ID and S.SUBFACILITY_CODE = L.SUBFACILITY_CODE where s.facility_id in (select facility_id from equis.facility_group_members(@facility_id)) order by GROUP_DESC, member_desc
Example 3: Locations by Location Type
To browse locations in a tree organized by location type instead of as a list of locations, find the @sys_loc_codes parameter and set the ST_REPORT_PARAMETER.DATA_SOURCE as follows:
SELECT L.SYS_LOC_CODE as MEMBER_CODE, L.SYS_LOC_CODE as MEMBER_DESC, S.LOCATION_TYPE_CODE as GROUP_CODE, coalesce(S.LOCATION_TYPE_DESC,S.LOCATION_TYPE_CODE) as GROUP_DESC, 'LOC_TYPE' as GROUP_TYPE, 0 as depth, 1 as DISPLAY_ORDER from RT_LOCATION_TYPE s inner join DT_LOCATION L on S.LOCATION_TYPE_CODE = L.LOC_TYPE where L.FACILITY_ID in (select facility_id from equis.facility_group_members(@facility_id)) order by GROUP_DESC, MEMBER_DESC