Shawn_Moore

CA Tuesday Tip: Hierarchical Grid Portlets - part 1

Discussion created by Shawn_Moore Employee on Mar 30, 2011
Latest reply on Jul 21, 2014 by navzjoshi00
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 3/29/2011

Today we will be starting a several week exploration into Hierarchical Grid porlets. This suggestion came in from Steve VanArsdale. Thanks Steve!

We will start our "journey" with a very simple example which will enable us to show a list of projects with their tasks under them. With each task and project there will be a start and finish date. You will be able to "expand" each investment to view the list of tasks underneath. Here is how it is done.


Master Object to Sub-Object Relationship Hierarchical Grid Query Example:
---------------------------------------------------------------------------------------------

1. Define an NSQL with the following characteristics:

a. The query will need to be a union of 2 queries that contain the same set of data types for their display properties.
b. In this case we are just using the investment start and finish dates, as well as the task start and finish dates
c. A property will need to be defined with the name "hg_has_children".
(This naming convention is required)

i.e. @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.hg_has_children:hg_has_children@

d. You will then need to define the actual query, which will select rows from the master object and subobjects tables.
e. The master object will need to specify some value for hg_has_children.

i.e. id hg_has_children

f. The sub-object will need to specify null for the hg_has_children field

i.e. null hg_has_children

g. Each of the "union-ed" queries will specify a where clause.
h. The master query's where clause will determine master records when hg_row_id is null.

i.e. where @where:param:user_def:string:hg_row_id@ is null

i. The sub-object's query where clause will need to map the sub-object to the parent using the parameter from hg_row_id@.

i.e. where prprojectid = @where:param:user_def:string:hg_row_id@


2. A fully formed solution to the requirements is as follows.

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:OBJ:hgquery.NAME:Name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.start:Start@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.finish:Finish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.hg_has_children:hg_has_children@
from (
select name, schedule_start start, schedule_finish finish,id hg_has_children
from inv_investments
where @where:param:user_def:string:hg_row_id@ is null

union

select prname, prstart start, prfinish finish, null hg_has_children
from prtask
where prprojectid = @where:param:user_def:string:hg_row_id@

)hgquery
where 1=1 and @filter@


Next week, we will cover the steps to get this query into a portlet. (it is actually really easy to do and with most of the defaults you can get the query to display data. ;) )


-Shawn Moore

Outcomes