E-BI/XMLP: BI Publisher(XML Publisher) Reports Show Empty Data If The Query Used As Data Source Has Duplicate Field Names (Doc ID 1504476.1)

Last updated on MAY 20, 2015

Applies to:

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

Symptoms

When attempting to create BI Publisher(XML Publisher) Reports using query data source, the BI Publisher(XML Publisher) Reports shows empty data if the field names being used in the report template do not match field names in the select list of the query data source. This issue may occur in one of the following situations:

  1. An error occurs while creating  templates and try to validate them
  2. Applying changes to the source file and/or to the query after the templates have been created
  3. The templates have been created while using PeopleTools version prior to 8.50 with query reports running on tools version 8.50 or later.

This issue happens for all BI Publisher(XML Publisher) Reports that are using XML sample files for adding fields into a RTF template or mapping fields with PDF template

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, for example using the following Query SQL:

SELECT A.DESCR1,

           A.DESCR2,

           A.EMPLID,

           A.NEW_EMPLID,

           TO_CHAR(A.ROW_ADDED_DT,'YYYY-MM-DD'),

           A.PHONE,

           A.DESCR80,

           A.NEW_NUMBER,

           A.DESCR

FROM PS_TEMP1_TBL A,

         PS_TEMP2_TBL B

WHERE B.OPRID = :1
        AND B.RUN_CNTL_ID = :2
        AND ( B.NEW_FLAG = 'Y'
        OR ( B.NEW_FLAG = 'N'
        AND A.NEW_NUMBER = (SELECT C.NEW_NUMBER
                                         FROM PS_TEMP2_DET C
                                         WHERE C.OPRID = :1
                                             AND C.RUN_CNTL_ID = :2)))
ORDER BY 11, 13, 5, 8

Note:

The Query SQL provided in the example above has the following repeated fields OPRID, RUN_CNTL_ID, NEW_NUMBER

4. Navigate to Reporting Tools > XML Publisher > Data Source

5. Create a new data source

6. Navigate to Reporting Tools > XML Publisher > Report Definition

7. Create a new Report Definition

8. Navigate to Reporting Tools > XML Publisher > Query Report Viewer

9. View the report with empty data

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