E-QR: Unreadable Excel File For PSQueries With Drilling Urls
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.
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).
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_
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.
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