E-QR: Unreadable Excel File For PSQueries With Drilling Urls (Doc ID 2040752.1)

Last updated on SEPTEMBER 12, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.54 and later
Information in this document applies to any platform.

Symptoms

PSQuery generates unreadable Excel files if the query results contain Drilling URL's and return more than ~ 100 rows. When the users attempt to open
the file, they get the following error:

"Excel found unreadable content in <file name>. Do you want to recover the contents of this workbook?"

If the user clicks "Yes", the file opens, but most of the lines are missing or blank.

If the DrillingURL expression is removed, the Excel file is valid. If the result set has less than ~ 100 rows, the Excel file is valid (even with the DrillingURL).

Steps
-------
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager

3. Create a new query with a DrillingURL expression that returns more than 100 rows

For example, use the following PS Query:

SELECT A.EMPLID, B.EMPLID, C.GPGB_ABSENCE_TYPE, D.EMPLID, E.EMPLID, 'psp/EMPLOYEE/HRMS/q/?ICAction=ICQryNameExcelURL=PUBLIC.EMPLOYEE_SALARY__PS_NVISION_'
 FROM PS_GP_ABS_EVENT A, PS_EMPLMT_SRCH_QRY A1, PS_GPGB_ABS_EVT_JR B, PS_EMPLMT_SRCH_QRY B1, PS_GPGB_ABS_TYPE C, PS_EMPLOYEES D, PS_EMPLMT_SRCH_QRY D1, PS_EMPLOYEES E, PS_EMPLMT_SRCH_QRY E1
 WHERE ( A.EMPLID = A1.EMPLID
   AND A.EMPL_RCD = A1.EMPL_RCD
   AND A1.OPRID = 'PS'
   AND B.EMPLID = B1.EMPLID
   AND B.EMPL_RCD = B1.EMPL_RCD
   AND B1.OPRID = 'PS'
   AND D.EMPLID = D1.EMPLID
   AND D.EMPL_RCD = D1.EMPL_RCD
   AND D1.OPRID = 'PS'
   AND E.EMPLID = E1.EMPLID
   AND E.EMPL_RCD = E1.EMPL_RCD
   AND E1.OPRID = 'PS'
   AND ( A.BGN_DT = B.BGN_DT
    AND A.EMPLID = D.EMPLID
    AND D.EFFDT =
       (SELECT MAX(D_ED.EFFDT) FROM PS_EMPLOYEES D_ED
       WHERE D.EMPLID = D_ED.EMPLID
         AND D.EMPL_RCD = D_ED.EMPL_RCD
         AND D_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
   AND D.EFFSEQ =
       (SELECT MAX(D_ES.EFFSEQ) FROM PS_EMPLOYEES D_ES
       WHERE D.EMPLID = D_ES.EMPLID
         AND D.EMPL_RCD = D_ES.EMPL_RCD
         AND D.EFFDT = D_ES.EFFDT)
    AND A.EMPLID = E.EMPLID ))

To define the Drilling URL set the following:

   3.1. Create a new Expression as a 'Drilling URL' Expression Type

   3.2.Click on the Query URL link

   3.3. Fill the following 'Query URL' parameters

Portal Name: EMPLOYEE  
Node Name: HRMS  
Query Name: EMPLOYEE_SALARY__PS_NVISION_   
*Format: XLS
    
4. Run the PS Query with Drilling URL to Excel

Run to Query to Excel (either from Query Manager page or scheduled).
Adjust the query criteria so that you can get two result sets: one with less than 100 rows and one with more than 100

5. Notice the result set with less than 100 rows is a valid Excel file, while the one with more than 100 is 'unreadable'

6. Remove the Drilling URL from the querY

Notice the Excel file results will always be valid files, regardless of the number of rows returned.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms