<< Click to Display Table of Contents >> Navigation:  Professional > Reports > How To > Set Report Parameter Lists in Tree View

•Using Items in Groups

•Using Ungrouped Data

 

 

It is possible to make any report parameter that displays a list of selections (e.g., ST_REPORT_PARAMETER.DISPLAY_TYPE = 'SingleSelect' or

'MultiSelect') display a tree, instead of a list. To accomplish this, set the SELECT statement in ST_REPORT_PARAMETER.DATA_SOURCE as

described below.

 

Using Items in Groups

 

If you already have your items in a group (using RT_GROUP and RT_GROUP_MEMBER), then you may use the existing function in your SELECT

statement in the ST_REPORT_PARAMETER.DATA_SOURCE as follows:

 

SELECT * FROM DBO.FN_GROUP_MEMBER_TREE('GroupCode1|GroupCode2|GroupCode3', @FACILITY_ID)

 

Using Ungrouped Data

 

You can also present non-group data as a tree. To accomplish this, set ST_REPORT_PARAMETER.DATA_SOURCE such that it returns the exact same

seven output columns as the DBO.FN_GROUP_MEMBER_TREE function:

•MEMBER_CODE: The code of the item (this is the value passed into the report).

•MEMBER_DESC: The display text or description of the item (this is 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 (this is 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.

 

Example 1

 

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:

 

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 = @facility_id) or (AL.FACILITY_ID is null)) order by GROUP_DESC, MEMBER_DESC

 

 

Example 2

 

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 = @facility_id order by GROUP_DESC, member_desc

 

Example 3

 

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 = @facility_id order by GROUP_DESC, MEMBER_DESC