XML Publisher

Repeating first item in XMLP reports

In an XMLP report, I was finding that the first item (first data entry) was being repeated in the report. The remainder of the items in the report were displaying as expected. After some investigation, a colleague pointed out that this was caused by the root element and data tags having the same value:

For example:

<?xml version='1.0'?>
<DATA>
  <DATA>
  </DATA>
</DATA>

The key is that the tag <DATA> is both the root level element and the tag used by the data elements.

The XML format actually required is this:

<?xml version='1.0'?>
<ROOT_ELEMENT>
  <DATA>
  </DATA>
</ROOT_ELEMENT>

What was causing the issue in this case was the XML file layout field tag. This is set in the file layout properties in the use tab. There is a field there called File Definition Tag. This is where you set the root element tag:

file-layout-file-definition-tag.png

Dates in XML Publisher

XML publisher uses coordinated universal time or UTC to display dates and times in report output. Depending on where you are in the world, this can throw out dates and times in your report.

For example, if the date being passed to the report from the database is 27/10/2009 00:00:00 (UTC+10 for Brisbane, Queensland Australia), this will actually be displayed in UTC (0, or essentially Greenwich mean time). In this case this is 10 hours behind so due to the time zone difference, the date can appear a day behind as 26/10/2009 (depending on the current time).

To fix this to use the appropriate UTC formatting or time zone:

Change it to include UTC or time zone formatting using one of these options:

Note for the third option, you will need to adjust to your time zone.

XML Publisher PDF Security

Any PDFs created by XML publisher will have security applied to them according to the contents of the file xdo.cfg which can be found in the PS_HOME\appserv directory.

Here's a sample of what this file looks like:

<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
    <properties>
        <!-- System level properties -->
        <property name="xslt-xdoparser">true</property> 
        <property name="xslt-scalable">true</property>
        <property name="system-temp-dir">CACHE</property>
        <property name="system-cachepage-size">50</property>
 
        <!-- PDF compression -->
        <property name="pdf-compression">true</property>
 
        <!-- PDF Security -->
        <!-- <property name="pdf-security">true</property> -->
        <!-- <property name="pdf-open-password">user</property> -->
        <!-- <property name="pdf-permissions-password">owner</property> -->
    </properties>
</config>

The PDF security section sets:

Failed to Generate or Read Data Source XML

While developing an XML publisher report, I was receiving the following error:

Failed to generate or read Data Source XML file (235,2305)
PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute  
Name:ProcessReport  PCPC:44256  Statement:1039

The code involved setting the run time data XML file using the following line:

&report_defn.SetRuntimeDataXMLFile(&path_to_xml_file);

Before calling ProcessReport. This didn't seem to work, however changing the code to read an XML file on the application server, creating an XMLDoc object out of the contents of the file and using SetRuntimeDataXMLDoc did the trick. Basically code like this (note only relevant code is shown):

/* Read the contents of the XML data file */
&xml_file = GetFile(&xml_data_file_path, "R", %FilePath_Absolute);
While &xml_file.ReadLine(&xml_data_line)
   &xml_data = &xml_data | &xml_data_line;
End-While;
 
/* Create XMLDoc object from file */
&xml_doc = CreateXmlDoc(&xml_data);
 
/* Set runtime data XML document */
...
&report_defn.SetRuntimeDataXMLDoc(&xml_doc);
...
NOTE: it appears the problem was trying to use the SetRunTimeDataXMLFile method for an XMLDoc data source. Changing over to a data source that is an XML file resolves the issue. In other words, the SetRunTimeDataXMLFile is designed for a data source of XML file (not XML Doc).

Template File Missing

The following SQL links together the XML publisher Report Definition, Data Source, Template, and Template file, to identify any reports that have a template where the template file definition is missing.

Here's what the template definition looks like when the template file definition is missing:

Reporting Tools > XML Publisher > Report Definition > Template tab

xmlp-report-template-file-missing.png

If you a run an XML publisher report with a template file missing, you will generally get a message like this in your standard output log:

Active template file not found in the template definition {TEMPLATE_NAME} for date {EFFDT}. (235,2515) PSXP_RPTDEFNMANAGER.TemplateDefn.OnExecute Name:GetActiveTemplateFile PCPC:15872 Statement:346

SQL to search for report templates missing a file:

select 
    RD.REPORT_DEFN_ID,
    RD.DESCR as REPORT_DESCR,
    RD.PT_REPORT_STATUS,
    RD.DS_ID as DATA_SOURCE_ID,
    RD.PT_TEMPLATE_TYPE,
    RT.TMPLDEFN_ID,
    RT.IS_DEFAULT as IS_DEFAULT_TEMPLATE,
    RD.LASTUPDOPRID as REPORT_LASTUPDOPRID,
    RD.LASTUPDDTTM as REPORT_LASTUPDDTTM,
    DS.DS_TYPE,
    DS.DESCR as DATA_SOURCE_DESCR,
    DS.ACTIVE_FLAG,
    DS.SCHEMAFILE,
    DS.SAMPLEDATFILE,
    DS.REGISTERED_BY as DATA_SOURCE_REGISTERED_BY,
    DS.LASTUPDOPRID as DATA_SOURCE_LASTUPDOPRID,
    DS.LASTUPDDTTM as DATA_SOURCE_LASTUPDDTTM,
    TD.DESCR as TEMPLATE_DESCR,
    TD.REGISTERED_BY as TEMPLATE_REGISTERED_BY,
    TD.LASTUPDOPRID as TEMPLATE_LASTUPDOPRID,
    TD.LASTUPDDTTM as TEMPLATE_LASTUPDDTTM
from
    PSXPRPTDEFN RD inner join PSXPRPTTMPL RT    
    on  RD.REPORT_DEFN_ID = RT.REPORT_DEFN_ID
    inner join PSXPDATASRC DS
    on  DS.DS_TYPE = RD.DS_TYPE
    and DS.DS_ID = RD.DS_ID
    inner join PSXPTMPLDEFN TD 
    on  RT.TMPLDEFN_ID = TD.TMPLDEFN_ID
    left outer join PSXPTMPLFILEDEF TFD
    on  TD.TMPLDEFN_ID = TFD.TMPLDEFN_ID
where
    TFD.TMPLDEFN_ID is null
order by RD.REPORT_DEFN_ID, RT.TMPLDEFN_ID;

XML Publisher Report Security

XML publisher reports can be configured to restrict distribution of reports to certain users or roles.

This is defined in:

Reporting Tools > XML Publisher > Report Definition > {open the relevant report} > Security (tab)

xmlp-report-definition-security.png

NOTE: that no roles or users means that no security applies and everyone can view the report (not that no one has access).

If you find that a report is running to success but you cannot view the output (e.g. PDF report), then it could be that you do not have one of the roles the report is distributed to (or you aren't one of the distributed users if this is hard coded).

For example, the XRFWIN report shown has been modified so that it requires you to have the PeopleTools role in order to view the output. If you don't have the PeopleTools role, you won't see the output in Report Manager.

If this is the case, you can either add the role (or user) to the XML publisher report security settings or give yourself a role that the XML publisher report has already been configured to distribute to (e.g. PeopleTools for the XRFWIN report shown).

Changing security will let you see older report output that you couldn't see before. If you remove security you also won't be able to see reports you have run previously. Note that distribution security is stored for each report instance.

Here's a query that gives you report information (PS_CDM_LIST) and the distribution security (PS_CDM_AUTH):

select
    L.PRCSINSTANCE,
    L.CONTENTID,
    L.PRCSNAME,
    L.PRCSTYPE,
    L.PRCSOUTPUTDIR,
    L.CONTENT_DESCR,    
    A.DISTID,
    A.DISTIDTYPE
from 
    PS_CDM_LIST L inner join PS_CDM_AUTH A    
    on  L.CONTENTID = A.CONTENTID
    and L.PRCSINSTANCE = A.PRCSINSTANCE
order by PRCSINSTANCE desc;

Another option is that if the Allow viewer ID assignment at report runtime check box is set in the XML publisher report security, you can simply add yourself into the distribution list when you run the report. To do this, when you go to run the report, click on the distribution hyper link (last column), then add yourself into the distribution list.

distribution-detail-add-your-userid.png

This is a good work around if you don't want to play with security but need to view a report as a once off.

One final workaround is to give yourself the ReportDistAdmin report distribution super user role. This will allow you to see all reports regardless of distribution security.

Including XML Publisher Definitions

The best way I've found is to add the XMLP Data Source Defn and then the XMLP Report Defn and include the related definitions (XMLP File Defn and XMLP Template Defn).

A quick note about the File Definition - it doesn't use the file name of your template, instead it uses a unique key. How do you know which key refers to your file? Use the following query:

select TMPLDEFN_ID, EFFDT, TEMPLATE_FILEID
from PSXPTMPLFILEDEF
where TMPLDEFN_ID = '{Your Template ID}';

XMLP Report Developer Role

In order to develop XML publisher reports, make sure that you have the XMLP Report Developer role. If you don't have this role you won't be able to change the data source type from PS Query when adding a new report definition.

report-definition-data-source-type.png

The report definition search page is based on the report category ID and author ID. So if you can't see any reports it may be because your operator ID is not an author ID or you do not have the appropriate author ID role.

Here's a query to check:

select * from PSXPRPTCATAUTH
Note that the delivered reports tend to use the PeopleSoft User role as the author ID. If you are configuring security stick to roles - rather than assigning security by individual users.

You can also change this through:

Reporting Tools > XML Publisher > Setup > Report Category