Report Parameter Lists in Tree View

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Reports > How To >

Report Parameter Lists in Tree View

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.

 

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

 

Note: Please add either /* group_members_tree */ at the beginning of the above DATA_SOURCE or --group_members_tree at the end of the DATA_SOURCE if the version of Professional/Enterprise is 6.3 or later. This note also applies to Examples 2 and 3 below.

 

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

 

Notes:

There is a bug in EQuIS Professional v5.5.1 (and earlier) that will prevent reports from working correctly if member_code is different from member_desc. This bug has been fixed in v5.6.

There is a  bug in EQuIS Professional v6.2.2 for loading the parameter tree. The workaround is to add 8 null columns at the end of the SELECT, e.g. SELECT al.action_level_code as member_code, ..., null, null, null, null, null, null, null, null from rt_action_level_type ... in the Example 1 above.

Loading the parameter tree may fail if member_code=member_desc=group_code, so try to avoid this extreme case.

 

Office Hour Video

 

The Office Hour video Action Levels: Group Tree Feature can be viewed on YouTube.