Shawn_Moore

CA Clarity Tuesday Tip: Hierarchical Grid Portlets - Part II

Discussion created by Shawn_Moore Employee on Apr 26, 2011
Latest reply on Apr 27, 2011 by Chris_Hackett
Hierarchical Grid Portlets - Part II Aggregation and Cleanup

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 4/26/2011

In part I, we learned how to set up a basic Hierarchical Grid Portlet. In this tip, we will create a similar portlet that will contain some aggregation.

For simplicity, we are going to calculate the completion of a project by the average of the percent complete of all its tasks.

i.e.

Project A: 63.7%
Task 1: 100.0%
Task 2: 70.0%
Task 3: 21.0%

After looking at the PMO Accelerator's "Actuals by Transaction Type" as an example, I decided to make some changes to the NSQL query we developed in Part I.


1st, we add a field that notes the hierarchy level in each sub query. This is as simple as specifying a literal value

i.e.

select 1 nsql_level -- have the value 1 represent level 1 for our nsql_level field.


2nd, we will pull out the "has children logic" out of the query and put the logic in our dimension's hg_has_children property. In the field specifier of the dimension property, we can put a case statement, instead of just the field value. Here we have added a level indicator per sub-query to identify the level. We can use this to conditionally toggle between null or an investment(project) id.

i.e.

CASE WHEN hgquery.nsql_level < 2 THEN hgquery.UID ELSE NULL END


3rd, to aggregate we join the task to the investment and take the average of the prPctComplete field of the task. We group by PRTask.prProjectId in the query.

Here's the full query:

SELECT
@SELECT:DIM:USER_DEF:IMPLIED: OBJ:hgquery.UID:UID@,
@SELECT:DIM_PROP: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.prPctComplete:Percent_Complete@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED: OBJ:CASE WHEN hgquery.nsql_level < 2 THEN hgquery.UID ELSE NULL END:hg_has_children@
from (
/* Level 1 - Investments */
select 1 nsql_level, name, schedule_start start, schedule_finish finish,
prprojectid UID, avg(prPctComplete) prPctComplete
from inv_investments, prtask
where prtask.prprojectid = inv_investments.id
and @where:param:user_def:string:hg_row_id@ is null
group by name, schedule_start, schedule_finish, prprojectid
union
/* Level 2 - Tasks */
select 2 nsql_level, prname, prstart start, prfinish finish, prid UID, prPctComplete
from prtask
where prprojectid = @where:param:user_def:string:hg_row_id@
)hgquery
where 1=1 and @filter@


To make a portlet with the above query, follow these steps.

1) Create a new Query and use the above NSQL
2) Create a new grid portlet with the query from step 1 as your data provider.
3) Add the fields: uid (optional), name, start, finish and percent_complete.
4) You can use the aggregation option if you want a total for the portlet as well.
5) Save your portlet and place it on a new tab.

Enjoy!

-shawn

PS: I added a space above in the NSQL after the colon in the dimensions and properties.

Outcomes