Add a Custom Attribute to a query based portlet

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

How do you add an OBS field or other custom attribute to a query based portlet (such as the Status Report Listing portlet list view) when it is only available in the filter?

Environment

Release: All
Component: Clarity Studio

Cause

The query behind the portlet needs to be modified to pull in the related field.

In the example of the Status Report Listing portlet (Which is part of PMO Accelerator), it is using a query (Status Report Listing Query) as a source. In the query, you can see that OBS Filter (or other custom attribute) is coming from "obs" which is a parameter in the query. Because it is only used as a parameter, it will not be available to be included in the List section of the portlet until you add it to the query. In order for an attribute to be available to be included in the List section it needs to be part of the select list in the query itself.

Resolution

To add a custom attribute to the portlet:

1. Note the Attribute ID from the Status Report Object
2. Go to Administration->Studio->Queries->Status Report Listing->NSQL tab. 
3. Add a new line at the bottom (replacing "test_att" in both places below with the attribute ID)

       @select:dim_prop:user_def:implied:statusreport:sr.test_att:test_att@

       4. Note: Since this is now the last line in the query, add a , to the end of the line above the new line. Example of last 2 rows for this example:

       @select:metric:user_def:implied:sr.cop_cost_eft_status:cost_effort_status@,   
       @select:dim_prop:user_def:implied:statusreport:sr.test_att:test_att@

       5. Click Save and Return or Save and Continue
       6. Navigate to Administration->Studio->Portlets and click on the Status Report Listing portlet
       7. To add the attribute as a column, click on the List Column Section tab and select Layout. 
       8. Move the new attribute from Available Columns to Selected Columns
       9. Click Save and Return

Additional Information

Note: For custom portlets, additional modifications may be needed to be able to add an OBS attribute. You can take a look at the Status Report Listing NSQL as an example, or another suggestion is building upon the below query (or something similar).

• The OBS Attribute ID for the query can be found by going to the applicable object then clicking on the OBS attribute created when you associate the object to the attribute. 

INNER  JOIN  prj_obs_associations assoc on assoc.record_id =invi.id
             AND  assoc.table_name = 'SRM_PROJECTS'
inner join prj_obs_units unit ON   assoc.unit_id = unit.id
INNER  JOIN  prj_obs_object_types object_assoc
             ON   unit.type_id = object_assoc.type_id
             AND  object_assoc.table_name = 'SRM_PROJECTS'
             and object_assoc.assoc_att_Code ='<Insert OBS Attribute ID>'
INNER  JOIN  prj_obs_units_flat flat
             ON   unit.id = flat.branch_unit_id