STRING_LIST NSQL Construct doesn't pass multiple values to the portlet.

Products

• Clarity PPM SaaS

Issue/Introduction

SUMMARY: STRING_LIST NSQL Construct doesn't pass multiple values to the portlet.

STEPS TO REPRODUCE: 

1. Login to Clarity as Administrator
2. Create dynamic lookup with query as below
SELECT 
                @SELECT:RESOURCES.UNIQUE_NAME:[email protected],
                @SELECT:RESOURCES.FULL_NAME:[email protected]
                
                FROM SRM_RESOURCES RESOURCES               WHERE
                @WHERE:SECURITY:RESOURCE:[email protected]               AND
                @[email protected]               AND RESOURCES.PERSON_TYPE != 0                             
                @BROWSE-ONLY:
               AND      case when resources.user_id is null then 0 else resources.user_id end != -99
               AND     RESOURCES.IS_ACTIVE = 1
               :[email protected]
3. Create Filter portlet with a Field pointing to the lookup created in Step 2
Data Type = Lookup
Display Type = Pull Down
Lookup Style = Multiple-select
4. Create Query with NSQL as below and under Attributes tab click on param_managers and point to lookup created in Step 2 and Extended Data Type is Lookup - String
SELECT
 @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:[email protected],
        @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:[email protected],
        @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT:[email protected]
FROM
 INV_INVESTMENTS I,
         SRM_RESOURCES R,
         SRM_RESOURCES MR,
         CMN_SEC_USERS U
WHERE    I.CREATED_BY = U.ID
AND      U.ID = R.USER_ID
AND      R.MANAGER_ID = MR.USER_ID
AND       (MR.UNIQUE_NAME IN (@WHERE:PARAM:USER_DEF:STRING_LIST:[email protected]))
AND      @[email protected]
GROUP BY R.FULL_NAME,
         MR.FULL_NAME
HAVING   @[email protected]

5. Create Grid Portlet based on the query created in Step 4
6. Create Portlet page and add Filter portlet created in step 3 and grid portlet created in Step 5
7. Click on Page Filters and map filter to the param_managers
8. Place portlet page you created in Step 6 under Home->Personal and navigate to that page
9. Select multiple values and click Filter but no data returned

Note: SQL Trace showing only 1st value in the list from selected is passed and not all values selected.
       
Expected Results: It should pass all selected values to filter

Actual Results: it is only passing 1st value from the list

Workaround: N/A

Environment

Release : 15.8.1

Component : CA PPM SAAS STUDIO

Resolution

DE58533, the STRING_INLINE_LIST, FLOAT_INLINE_LIST, and INTEGER_INLINE_LIST are the correct constructs that can be used for both SVL and MVL Lookups with different data types.  It can also be used in the SOAP example described in the online documentation. .

The documentation has been updated.