USM Regional Conference
XML/BI Publisher
PeopleTools Release 8.48
November 16, 2007
What is XML Publisher?
Starting in release 8.48, Oracle XML
Publisher (XMLP) was incorporated
into PeopleTools to enhance the
reporting capability of PeopleSoft
Applications.
XML Publisher is synonymous with BI
Publisher (Business Intelligence). The
two terms are interchangeable.
Page 2
What is XML Publisher?
XML Publisher is a Java-based reporting
tool that makes report generation more
efficient by separating the data
extraction logic from the report layout.
Data
Extraction
XML Report
Publisher Output
Report
Layout
Page 3
What is XML Publisher?
Report templates can be created using
common software such as Microsoft Word,
Excel, Adobe Acrobat, etc., while data
sources can come from PeopleSoft Queries,
rowsets, XML document objects, or XML files.
The separation of data sources and report
templates allow end users to modify the
report layout, using familiar desktop
applications, without the assistance of
technical developers once the data source of
the report is created.
Page 4
Running a Query-Based
XMLP Report
Main Menu > Reporting Tools > XML Publisher >
Query Report Viewer
Page 5
XMLP Report Output Types
There are four XMLP report output
types:
HTM
PDF
RTF
XLS
Page 6
Data Source
Let’s examine the data source for
the delivered report that we just ran:
Main Menu > Reporting Tools > XML
Publisher > Data Source
Page 7
Types of Data Source
There are four types of XMLP report data
sources:
PS Query
Rowset
XML File
XMLDoc
Page 8
Report Definition
It is in the Report Definition that a Data
Source is associated with one or more
layout template files.
Navigate to the Report Definition
component. Open up the report
definition named JPM_CATI_RPT
(“Content Items Report”).
Page 9
Report Definition
Main Menu > Reporting Tools > XML Publisher >
Report Definition
Page 10
Report Definition - Template
Page 11
Report Definition - Template
Notice the Level 1 and Level 2 scroll areas
on the Template page. Each report can
have multiple templates. But only one
template can be the default template. When
the report is run, any of the templates can
be specified.
Notice that each template can have one or
more template files associated with it. Each
template file assigned to a template must
have a unique effective date.
In order to be available at runtime, a
template file must be “Active”.
Page 12
Report Definition - Template
You will not be able to save a report
definition unless it has been associated with
at least one active template file. Therefore,
you must design the layout of your report
(the template) before you can create the
report definition.
On the Template page, template files can be
uploaded, downloaded, and previewed.
When you preview a template, generic data
is used to provide an example of the layout
of the report.
Page 13
Setting Up XML Publisher
Setting up your PeopleSoft system to use XML
Publisher requires just a few steps.
The XDO configuration file identifies the
location of the cache directory for your
application server domain.
There must be at least one “Report Category,”
which is a required attribute for all report
definitions and sub-templates.
It is a good idea to install the Template
Builder plug-in for Microsoft Word. This will
make it easier to design RTF templates for
your reports.
Page 14
Setting Up the XDO
Configuration File
In the “appserv” directory, under “PSHOME”
you will find a file called “xdo.cfg”.
One of the “property” tags in this file
identifies the “system-temp-dir.”
The value for this property must point to the
CACHE directory for your application server
domain.
For example, if your application server
domain is called CSDMO90, the system-temp-
dir setting in the xdo.cfg file would be:
[PS_HOME]/appserv/CSDMO90/CACHE.
Page 15
Setting Up the XDO
Configuration File
This example is from a Windows server environment,
where the name of the application server domain is
HRDMO90 (The Application Server does not need to
be restarted after the xdo.cfg file is modified):
<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">T:\PT848\appserv\HRDMO90\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>
Page 16
Setting Up Report Categories
Report Category is a required field for
all report definitions and sub-
templates.
Report categories define groups that
enable control over who can access
and edit report definitions and sub-
templates.
There is one delivered report category:
ALLUSER.
Page 17
Setting Up Report Categories
Main Menu > Reporting Tools > XML Publisher >
Setup > Report Categories
Page 18
The Template Builder for
Microsoft Word
This plug-in for Microsoft Word helps facilitate
the development of RTF template documents
in Microsoft Word.
It allows you to associate an XML file with a
report template, and easily map the tags in
the XML file to fields that appear in the report.
Much of the basic syntax that is necessary will
be imbedded in the form fields in the Word
document.
You must have Microsoft word installed prior
to installing the plug-in.
Page 19
The Template Builder for
Microsoft Word
The following navigation will allow you to
download the Template Builder for Microsoft
Word:
Main Menu > Reporting Tools > XML Publisher >
Setup > Design Helper
Page 20
The Template Builder for
Microsoft Word
You can also find the Design Helper in the
<PSHOME> directory:
<PSHOME>\webserv\<webservername>\
applications\peoplesoft\PORTAL\ps\xmlp\
xmlp_desktop.zip
Page 21
Steps to Create Your Own
XMLP Report
You can create your own XMLP report
using the following steps:
1. Create a Query.
2. Create the Data Source.
3. Create the Template.
4. Create the Report Definition.
5. Run and test the XMLP Report.
Page 22
XMLP Roles
In order to run or develop XMLP reports,
the users need these roles:
XMLP Power User (Running XMLP
reports).
XMLP Report Developer (Creating XMLP
definitions).
Page 23
Step 1: Creating a Query
Main Menu > Reporting Tools > Query > Query Manager
Page 24
Step 2: Creating the
Data Source
On the “Data Source” search page, select Add a New
Value:
Main Menu > Reporting Tools > XML Publisher >
Data Source
Page 25
Step 2: Creating the
Data Source
When the Data Source page displays, click on each of
the Generate hyperlinks near the bottom of the page.
Save the Data Source definition.
Page 26
Step 2: Creating the
Data Source
Click on the hyperlink for the Sample Data
File.
Page 27
Step 2: Creating the
Data Source
Save that XML file to an appropriate directory. You
will need to retrieve it in the next step, when you
import it into your report template.
Page 28
Step 3: Creating the Template
Open up Microsoft Word. The Template
Builder, that was installed earlier, provides a
new pull-down menu and a tool bar menu.
Page 29
Step 3: Creating the Template
Start a new document in Word. Under the “File”
menu, select “Page Setup…”. Set the left and
right margins to 0.5”.
Page 30
Step 3: Creating the Template
On the Template Builder menu, click on the
Data menu, and select “Load XML Data…”:
Page 31
Step 3: Creating the Template
Select the XML file that was created when
you saved the sample data file while
defining the data source. A message will
appear indicating that the data was
successfully loaded.
Page 32
Step 3: Creating the Template
Save the Word document as an RTF file.
Page 33
Step 3: Creating the Template
Put a title at the top of the document and
center the text:
Below the title, on the left side, type the
following text: “Query Name:”. One line
below, type “Number of Rows:”
Page 34
Step 3: Creating the Template
Place your cursor in your document to the
right of the text “Query Name.” From
the Template Builder menu, select “Insert,
Field…”. The Field Creator dialog will
appear.
Find the @queryname field in the list.
Double-click on it so it appears to the
right of the corresponding label you have
created in your report. Apply the same
technique with the @numrows field.
Page 35
Step 3: Creating the Template
Page 36
Step 3: Creating the Template
Create a blank line or two in your document, under the
fields you just added.
A table is a convenient way of displaying the results of
our query because there are multiple rows of data.
Click on the Insert menu and select “Table/Form…”:
Table
Structure
Window
Page 37
Step 3: Creating the Template
In the Data Source window on the left,
click on the word “Row” (which appears
bold) and drag it into Table Structure
window in the middle. When the pop-up
prompts you to choose from three menu
items, select “Drop All Nodes”.
Page 38
Step 3: Creating the Template
You have defined the structure of the row
for the table.
Page 39
Step 3: Creating the Template
Click the “OK” button to insert the table
into your document. Save the document
again.
Page 40
Step 3: Creating the Template
Click Preview on the Template Builder menu
and select PDF. Notice that four different
types of output are available for preview:
PDF, HTML, Excel, and RTF. The preview
does not use real data.
Page 41
Step 3: Creating the Template
Page 42
Step 4: Creating the
Report Definition
Main Menu > Reporting Tools > XML Publisher >
Report Definition
Page 43
Step 4: Creating the
Report Definition
Page 44
Step 4: Creating the
Report Definition
Note: You will not be
able to save a report
definition unless it has
at least one active
template file assigned
to it.
Click the Upload
button. Select the
RTF template file that
you have previously
defined.
Page 45
Step 5: Running and Testing
the XMLP Report
Main Menu > Reporting Tools > XML Publisher >
Query Report Viewer
Page 46
Modifying the Template
After you test a report, you may decide to make
changes to it to improve the layout.
Your modified template will not be applied to the
report until you re-associate it with the report
definition.
Each report definition can have multiple templates
defined. Only one template can be the default
template for a report definition.
Each template must have at least one active template
file associated with it. Within each template, each
template file must have a unique Effective Date.
Page 47
Modifying the Query
Definition
Any changes you make to the criteria in the query
that affects the number of rows returned will be
reflected the next time the XMLP report is run. There
is no need to change or update any setting in the data
source definition or report definition.
If you add more fields to an existing query (modifying
the SELECT clause), this will not be reflected in the
report until you regenerate the sample data and
schema files associated with the Data Source
definition.
Also, you need to modify the report template to show
the new field(s) and associate the new template with
the report definition.
Page 48
Sorting Data
You have the option of defining the sorting
rules in the query definition. But you can
also specify sorting preferences in the
template of the report.
You can dictate the sorting rules while
defining a table or you can do it in a more
manual way by inserting the code into the
template directly.
When you are defining a table, you have the
option of sorting the rows by any column in
that row.
Page 49
Sorting Data
When you click on the word “Row” in the middle of
the Insert Table/Form dialog box, the “Sort by”
field appears along with several other options on
the right.
Page 50
Adding Images to
Your Reports
You can insert a .jpg, .gif, or .png file into
a template.
After you decide where you would like an
image to appear in your report, place
your cursor in that position. In MS Word,
click Insert > Picture > From File…
Use the “Insert” menu that is part of the
regular Word menu, not the “Insert”
menu item in the Template Builder menu.
Page 51
Conditional Formatting
You can choose to change the formatting
of your report output based on whether
or not a certain condition is met. XML
Publisher supports the use of “if”
statements.
The following syntax is placed at the
beginning of the conditional area:
<?if:condition?>.
The following syntax is placed at the end
of the conditional area: <?end if?>.
Page 52
Conditional Formatting
To insert an “if” statement in your MS Word
template, you can insert the appropriate
syntax directly into the template or place the
syntax in a “Text Form Field.”
If you choose to hide the syntax in a form
field, you have to define a “Text Form Field.”
Make sure the “Forms” toolbar is visible in
Word.
Page 53
USM Regional Conference
Wrap Up and
Additional Questions
Page 54
