Export to Excel with a parameterized lookup is very slow (Classic)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When a large customer adds a parameterized lookup, (i.e. the OOTB Department) to Project List or other similar pages in Classic, the Export to Excel is extremely slow and times out

STEPS TO REPRODUCE:

1. On a large system with 10k of projects and connect to Project List
2. Open Project List with all 10k projects - click Export to Excel Data Only
3. Notice the export completes within expected time
4. Now add the field "Department" - lookup Department Browse to the list
5. Go to Options - Export to Excel Data only

Expected Results: The export to work within reasonable time

Actual Results: Export to Excel never completes, the Department browse query is getting stuck on the database for hours (very large query with many lines). Error may be thrown : Processing another export request. Please try again

 

Note: This may be happening on other parameterized lookups, OOTB and custom lookups as well

Environment

Release : 16.0.3, 16.1.0, 16.1.1, 16.1.2, 16.1.3, 16.2.0, 16.2.1

Cause

There are investments which have the department code but are missing captions, because they are not associated with any location

Resolution

This is DE68536 / DE77285, which is addressed by a user story, fixed in 16.2.2+

Workaround 1:

Also valid for custom parameterized lookups or OOTB ones such as Project Category / Project Type.

Fix the missing Department / Location captions:

1. In Project List add Department and Location attribute to the columns
2. Click on Department twice to sort on columns
3. For the departments with empty/null values, go in the project and set a value
4. Repeat the same for blank Locations
5. Set the affected lookup attributes to Required after adding values in each combination - this will avoid the problem from reoccurring

 

You may use the below query to identify the problematic records for Departments:

SELECT  ii.code FROM INV_INVESTMENTS ii
WHERE ii.code IN (SELECT ii2.code FROM PAC_MNT_PROJECTS pmp, INV_PROJECTS ip, INV_INVESTMENTS ii2
    WHERE pmp.id = ip.PRID AND ii2.id = ip.prid AND pmp.DEPARTCODE NOT IN ( SELECT departcode FROM LOCATIONDEPT l))

Note that we recommend not to remove location association of any department to avoid this issue happening going further

 

Workaround 2:

Valid for other lookups than Departments, will not work for Department due to constraints

0. Identify the lookup code for the parameterized lookup

1. On the Clarity app server, run the command:
admin system-options -add LOOKUP_CACHE_OPTION <LOOKUP_CODE> -multi

Note: Replace <LOOKUP_CODE>  with the actual lookup code for the parameterized dynamic lookup that has the issue

2. Restart the services

 

Workaround 3: 

For Project Category / Project Type OOTB lookups which include User Lookup Mapping 

1. Go to Home - Lookup Mapping

2. Ensure all the combinations you see on database side for Project Category/Type values exist as User mappings

3. If anything missing, recreate the user mappings and you should see the value appear in Project List