AnsweredAssumed Answered

Issue with a drill-down portlet

Question asked by ClareLeonard76040337 on May 13, 2016
Latest reply on May 17, 2016 by ClareLeonard76040337

Hi,
This explanation will be kind of long, but I'm hoping someone will be able to help.

 

I am trying to create a drill-down portlet. The parent portlet will be a pie chart showing projects divided by an attribute called last_reported_status. Clicking a slice of the pie will load a second portlet that will be a grid portlet of all the projects that have that particular last_reported_status. Last reported status is a lookup attribute with 3 possible values.

 

To create the portlets I followed the instructions in this presentation: Rego University: Advanced Portlets, CA PPM (CA Clarity PPM)

 

So far I have created the parent portlet and am trying to get it to work. The data provider is the following query:

SELECT  

@SELECT:DIM:USER_DEF:IMPLIED:PRJ:inv.id:uuid@

, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:inv.code:id@

, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:inv.name:name@

, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:inv.manager_id:manager@

, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:oci.obj_work_status:work_status@

, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:ocp.bb_last_status:last_status@

, @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(inv.id):count:AGG@

FROM     INV_INVESTMENTS inv

JOIN     odf_ca_inv oci on inv.id = oci.id

JOIN     odf_ca_project ocp on inv.id= ocp.id

WHERE    @filter@

GROUP BY

ocp.bb_last_status,

inv.id,

inv.code,

inv.name,

inv.manager_id,

oci.obj_work_status

HAVING   @HAVING_FILTER@

 

The portlet configuration does not allow me to define the group by attribute. I believe this is because it is a query-based portlet. I have a similar portlet that is object based that does allow the user to define the group by attribute on the portlet. The chart is dividing the results by uuid instead of last reported status, which is obviously not what I want it to do. I can't figure out how to make the portlet group by last reported status. The query fails if I don't include all columns in the group by expression.

Outcomes