Configuration of a multiselect filter in a portlet

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The scenario is as follows:

• An NSQL query is written
• It has a listagg() function to create a single record out of multiple sub-records
• A Portlet is created

Now, is it possible to create a multiselect filter, in the Portlet, to choose individual sub-records that were aggregated using listagg() ?

Resolution

This is not possible. A multiselect filter could have been defined if listagg() was not used.

Additional Information

Sample steps that can be used to define a multiselect filter, when listagg() is not used:

1. Query:
   
   SELECT
    @select:dim:user_def:implied:d0:ii.id:inv_id@,
    @select:dim_prop:user_def:implied:d0:ii.name:inv_name@,
    @select:dim_prop:user_def:implied:d0:ii.code:inv_code@,
    @select:dim_prop:user_def:implied:d0:sr.id:manager_id@,
@select:dim_prop:user_def:implied:d0:sr.full_name:manager_name@
FROM inv_investments ii
    JOIN srm_resources sr on sr.user_id = ii.manager_id
WHERE @filter@
   
   
2. In the 'Attributes' tab of the query, click on 'manager_id'. Change 'Extended Data Type' to 'Lookup - Numeric'. In the 'Lookup' choose lookup with ID 'SCH_BROWSE_RESOURCE'. Save
3. Create a Grid Portlet
4. In the 'List Filter Section' -> 'Fields' -> Properties of 'manager_id' (icon with 3 horizontal lines), choose 'Lookup Style' as 'Multi-select'
5. Now display this field in the filter. You can choose multiple resources and get results for them.