Export to Excel with Department 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. Error may be thrown : Processing another export request. Please try again

Environment

Release : 16.0.3, 16.1.0, 16.1.1, 16.1.2

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, which will be addressed by a user story, targeted in 16.1.3

Workaround:

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

 

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

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