Clarity

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.

If you are COUNTing by "inv.id" then you don't want to GROUP BY "inv.id"

In fact, in your "parent portlet" the only details that you want returned by the SQL is the "last reported status" and the count - i.e. no "inv" details at all - this is enough to draw the pie chart from.

(You would only return the "inv" details in your second, detail portlet)

Hi,
Thanks for your reply.

I would think that I'd need inv.id in order to have a primary dimension key that's unique. If I only include last reported status and count, I won't have a unique, non-null key. The other fields (manager, work status) are items that I need to filter by on the parent (in the portlet, not the query). I probably can remove the project name and id (not the UUID, the id visible to the user) though.

I read up a little more, and I understand now that a lot of SQL extensions require you to repeat the entire Select statement in the Group By. I wasn't aware of this because I'm used to writing MySQL where this isn't a thing.

How can I specify the grouping field (either in the query or in the portlet) so that my chart will actually group the results?

There might be other ways to build pie charts, but the way I build them (and I've built lots), the SQL delivers only the 'status' and a count (so the 'status' can be the unique non-null key).  You drill down on the "status" and the project details are only returned in the second portlet.

I've never come across "repeat the entire Select statement in the Group By" so not really sure what you mean here - I would just have a query that grouped by 'status' (in the SQL).

If you need to filter by "project attributes" in the pie-query then you can still do this, just use the NSQL parameters in your query (rather than filtering by the returned values).