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