When sorting in some portlets, data in the column being sorted on displays duplicate data

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS - Application
• Clarity PPM On Premise

Issue/Introduction

When sorting in some portlets, such as the Investment Timesheet Summary  portlet, data in the column being sorted on displays duplicate data if the query used in the portlet contains a Group By clause in the underlying query. 

Steps to Reproduce:

Show »

1. Create an NSQL query with the following code: SELECT @SELECT:DIM:USER_DEF:IMPLIED:IDEA:i.id:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.code:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.name:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_code:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_name:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_name:[email protected] FROM( select i.id, i.code, i.name, i.stage_code, l2.name stage_name FROM inv_investments i JOIN odf_ca_idea oci ON i.id = oci.id LEFT JOIN cmn_lookups_v l2 ON l2.lookup_type = 'INV_STAGE_TYPE' AND l2.lookup_code = i.stage_code AND l2.language_code = 'en' WHERE 1 = 1 GROUP BY i.id, i.code, i.name, i.stage_code, l2.name ) i where @[email protected] 2. Then create a grid portlet with that query as the data provider. 3. Add all fields to the layout. 4. Add portlet to a page. 5. Navigate to the portlet and click on any header other than idea_id to sort ascending (Ex idea_name). 6. Then click on the same header again to sort descending.

1. Create a NSQL query with the following code:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:IDEA:i.id:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.code:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.name:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_code:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_name:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:i.stage_name:[email protected]
FROM(
select i.id,
i.code, i.name, i.stage_code, l2.name stage_name
FROM inv_investments i
JOIN odf_ca_idea oci ON i.id = oci.id
LEFT JOIN cmn_lookups_v l2 ON l2.lookup_type = 'INV_STAGE_TYPE' AND l2.lookup_code = i.stage_code AND l2.language_code = 'en'
WHERE 1 = 1
GROUP BY i.id, i.code, i.name, i.stage_code, l2.name
) i
where @[email protected]

2. Then create a grid portlet with that query as the data provider
3. Add all fields to the layout
4. Add portlet to a page
5. Navigate to the portlet and click on any header other than idea_id to sort ascending (Ex idea_name)
6. Click on the same header again to sort descending

Expected Results: The results sorts by the value in descending order

Actual Results: Every row in the column ends up having the same value that you are sorting on

Cause

This issue is caused by: CLRT-79985

Environment

This issue has been reproduced in 14.3 patch 3 and 4 but is not reproducible in 14.3 base or 14.4.

Resolution

Workaround:

Resort the data for the column that has the duplicating data in ascending order:

1. Navigate to the portlet with the issue

2. Click on the header row for the column that is duplicating data

This will resort the column in ascending order removing the duplicated data shown.

However, there is no workaround to use descending sort in NSQL portlets.

Resolution:

This issue is no longer reproducible in CA PPM 14.4 and higher.