AnsweredAssumed Answered

Three dimensional portlet with different values in third dimension

Question asked by ClareLeonard76040337 on Aug 1, 2017
Latest reply on Aug 3, 2017 by Samik Basu

Hi,
I'm creating a portlet that has 3 dimensions:

  • Project
  • Task Type
  • Task

The aim of the portlet is to show task finish date and status across multiple projects for a specific subset of tasks. These tasks have 1 of 2 possible task type values. Task group one is Type = RACM, and the 4 tasks in this group are" Draft, Review, Upload, Validate. Task group two is Type = Access, and the 4 tasks in this group are "Analyze, Build, Test, Assign.

 

I would like the portlet to look like the attachment called portlet1 but it looks like portlet2 (I just edited the original screenshot to show the desired outcome in portlet1, fyi). I want to limit the 3rd dimension options to those that align with the value in the subtype. IE: Where Dim2 = RACM, only columns for tasks where type = RACM should appear under that dimension header. Where it = Access, only the access tasks should appear.  Here is my query:

 

 

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:PROJECT:i.id:project_uuid@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.code:project_id@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:i.name:project_name@
, @SELECT:DIM:USER_DEF:IMPLIED:TASKTYPE:ot.bb_task_sub:subtype_id@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASKTYPE:lc.name:subtype_name@
, @SELECT:DIM:USER_DEF:IMPLIED:TASK:t.prexternalid:task_id@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:t.prName:task_name@
, @SELECT:METRIC:USER_DEF:IMPLIED:t.prName:task_name_metric@
, @SELECT:METRIC:USER_DEF:IMPLIED:TO_CHAR(t.prfinish,'MM/DD/YY'):task_finish_metric@
, @SELECT:METRIC:USER_DEF:IMPLIED:t.prStatus:task_status_metric@
FROM prtask t
JOIN odf_ca_task ot on ot.id = t.prid
JOIN inv_investments i on i.id = t.prprojectid
LEFT JOIN cmn_lookups_v lc on lc.lookup_type = 'bb_task_sub' and lc.lookup_code = ot.bb_task_sub and lc.language_code = 'en'
WHERE @FILTER@
AND ot.bb_task_sub in (1,2)
AND i.is_active = 1


Is there something I can do in the query to divide this up this way? I don't think it's possible to do on the portlet side. I am also not seeing the Dim2 headers but I believe this is probably related.

Attachments

Outcomes