E-QR: Download Query Results to Excel With NULL Fields Are Truncated If Contains Component Drilling URLs (Doc ID 2170768.1)

Last updated on DECEMBER 12, 2016

Applies to:

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

Symptoms

When attempting to download the PS Query results to Excel with Drilling URL either directly to Excel or in Query Manager/Viewer, then download to Excel the output results are truncated for NULL date values.

Full results are displayed in HTML and CSV.

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 PS Query (e.g.SB_URL_TEST) based on the delivered records CLASS_TBL - Class Table and CLASS_MTG_PAT Class Meeting Pattern Table left outer joined with A
4. View Query SQL:
SELECT A.CRSE_ID, A.CRSE_OFFER_NBR, A.STRM, A.SESSION_CODE, A.CLASS_SECTION, A.INSTITUTION, B.CRSE_ID, B.CRSE_OFFER_NBR, B.STRM, B.SESSION_CODE, B.CLASS_SECTION, B.CLASS_MTG_NBR, B.FACILITY_ID, TO_CHAR(CAST((B.MEETING_TIME_START) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((B.MEETING_TIME_END) AS TIMESTAMP),'HH24.MI.SS.FF'), B.MON, B.TUES, B.WED, B.THURS, B.FRI, B.SAT, B.SUN, TO_CHAR(B.START_DT,'YYYY-MM-DD'), TO_CHAR(B.END_DT,'YYYY-MM-DD'), B.CRS_TOPIC_ID, B.DESCR, B.STND_MTG_PAT, B.PRINT_TOPIC_ON_XCR
 FROM (PS_CLASS_TBL A LEFT OUTER JOIN PS_CLASS_MTG_PAT B ON A.CRSE_ID = B.CRSE_ID AND A.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND A.STRM = B.STRM AND A.SESSION_CODE = B.SESSION_CODE AND A.CLASS_SECTION = B.CLASS_SECTION )
 WHERE ( A.STRM = '0570')

5. Go to Expression tab and create a new Expression with Drilling URL type

6. Add the following:
Portal Name: EMPLOYEE
Menu Name: ESTABLISH_COURSES
Market: GBL
Component:CLASS_DATA_SCTN
Page: CLASS_MTG_PAT_SCTN
Menu Action: Update

7. Click on Search Keys
8. Click on Map Columns and enable the check box near the B.STRM field
9. Click Ok
10. Click Ok
11. Save the query again
12. Download the output results to HTML
13. Then Download to Excel
14. No rows are retrieved for NULL date values.

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