Creating PeopleSoft Reports Using XML Publisher
Creating PeopleSoft Reports Using XML Publisher
This tutorial covers the fundamental
features of XML Publisher when it is integrated with Oracle PeopleSoft. You
learn how to create templates and reports as well as how to publish your reports.
Approximately one hour
This tutorial covers the following topics:
Place the cursor over this icon
to load and view all
the screenshots for this tutorial. (Caution: This action loads all screenshots
simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking
it.
XML Publisher is now also called "Oracle Business Intelligence
Publisher" (BI Publisher). It is a template-based reporting tool that leverages
standard technologies for data extraction and display. XML Publisher segregates
the Data Logic from the Presentation Layout during development of the reports
and combines them at run time. As a result, although a technical consultant
might independently design generalized data extraction logic (using Application
Engines/PeopleCode or PSQuery), a functional consultant or end user can design
the Presentation using common desktop tools such as Microsoft Word based on
the data source created by the technical consultant. Because queries can also
be used as a data source, XML Publisher extends the flexibility of the tool
essentially to the same people who need to view the reports (end users). This
means that end users with minimal training are able to design templates and
reports based on generally designed data sources, thus greatly reducing the
cost of ownership.
Back to Topic
List
The report that you build in this tutorial is a PeopleSoft
query that shows the annual rate of salary for three departments: ADMIN, ADMISSION,
and ALL_DEPTS. This data is only for the demonstration purposes of this tutorial.
You build two charts and then a table. If you want, you can skip creation of
the charts and create only the table.
Back to Topic List
To complete this tutorial, you should have:
|
1.
|
PeopleTools 8.48 running
|
|
2.
|
Java Runtime Environment (JRE) 1.4. and Microsof .Net
2.0
|
|
3.
|
Microsoft Word 2000 (or later versions)
|
| |
Note: Screen captures for this tutorial
were taken in a Windows XP environment. Start menu options may thus
vary slightly.
|
Back to Topic List
The XML Publisher Desktop/Template Builder is shipped with
PeopleTools 8.48. You can also download the software from the PeopleSoft XML
Publisher page.
| 1. |
From the left navigation bar in PeopleSoft Applications,
select XML Publisher> Setup > Design Helper to download the
Template Builder.
|
| 2. |
Download the Template Builder by clicking the Plug-in
link. You can save the download anywhere (for example, in c:\temp).
|
| 3. |
After the download is complete, install XML Publisher
Desktop. Open File Explorer and navigate to the download directory (for
example, c:\temp). The following example shows the expanded files
under c:\temp\xmlp_desktop. Double-click setup.exe to start
the installation process. A set of installation wizard pages guide you
through the installation process.
|
| 4. |
Select English, and then click Next.
|
| 5. |
Ensure that no previous version of XML Publisher Desktop
is installed. Click Next.
|
| 6. |
The XML Publisher Desktop requires the installation
of Java Runtime Environment (JRE) version 1.4 (or later versions). Enter
the location for Java Runtime Environment and click Next.
|
| 7. |
Deselect Open the Word Template Builder Tutorial
file and click Next. Enter the name of the folder where you
will install XML Publisher Desktop and click Next.
|
| 8. |
Click Install for the installation to start.
|
| 9. |
When the installation is complete, click Finish.
|
| 10. |
After XML Publisher Desktop is installed, open Microsoft
Word. Notice a menu item named Template Builder in the menu bar
and a new toolbar.
|
Back to Topic List
Initially you modify an existing query, PERSONAL_DATA_PAY,
and apply criteria on department id to reduce the volume of data. To
modify the query, perform the following steps:
| 1. |
Log in to your PeopleSoft Application. Then, from the
left menu, select Reporting Tools > Query > Query Manager.
Enter PERSONAL_DATA_PAY in the begins with field. The query
PERSONAL_DATA_PAY will return.
|
| 2. |
To modify the query, click the Edit link under
the Edit column. All the fields in the query are displayed.
|
| 3. |
Because the filter will be on DeptID, click the
filter icon on DeptID.
|
| 4. |
On the Edit Criteria Properties page, select
in list from the Condition Type drop-down list.
|
| 5. |
Click the search icon for List Members in the
Edit List box.
|
| 6. |
Enter ADMIN and click Add Value.
|
| 7. |
Similarly, add the departments ADMISSION and
ALL DEPTS. When all the departments are entered for the criteria,
click OK.
|
| 8. |
On the Edit Criteria Properties page, click OK.
|
| 9. |
Click the Run tab and run the report. Make sure
only three departments (ADMIN, ADMISSION, and ALL DEPTS) are returned.
The query should return only 30 rows.
|
Back to Topic List
Before you create any report, you must create and register
a data source. User queries are data sources that the application uses to retrieve
data for an XML Publisher report. XML Publisher supports PS Query, rowsets,
XML files, and various other types of data sources. In this tutorial, the query
you just modified will be your data source.
| 1. |
Log in to your PeopleSoft Application. Then, in the
left navigation bar, expand Reporting Tools > XML Publisher >
Data Source.
|
| 2. |
Because you will use a PS query, click Add a New
Value. Select PS Query as the Data Source type and click
the Look Up Query search icon for Data Source ID.
|
| 3. |
Enter P in the begins with field and click
the Lookup button.
|
| 4. |
Scroll down and click PERSONAL_DATA_PAY.
|
| 5. |
Click the Add button on the Data Source
page.
|
| 6. |
On the Data Source page, in the Data Source
Properties section, enter the Description as Departmental
Annual Salary. Click Generate under the Generate File
column for Sample Data File.
|
| 7. |
After the data source is generated, an XML file for
PERSONAL_DATA_PAY is created. Click the Save button.
|
|
| 8. |
Click the PERSONAL_DATA_PAY.XML file. The browser appears and
displays the XML data in the file. Save the XML file in the C:\temp
folder.
|
Back to Topic List
You use a Microsoft Word plug-in to build an RTF template.
You use a preexisting RTF template file named Personal_Data_Pay.rtf.
Download the template from here and
save the RTF file under c:\temp.
You open a base template and load the XML data. To load the
XML data, perform the following steps:
| 1. |
Open the file named Personal_Data_Pay.rtf in
Microsoft Word
|
| 2. |
Click Data and select Load XML Data.
|
| 3. |
In the Please select XML data dialog box, select
the PERSONAL_DATA_PAY.xml file and click Open.
|
| 4. |
When the "Data loaded successfully" message
appears, click OK.
|
Back to Sub Topic
Back to Topic List
If you do not want to create a pie chart or a bar chart, skip
this section.
To create a pie chart and a bar chart, perform the following
steps:
| 1. |
From the first cell in the table, delete the text <insert
a pie chart here>. From the menu, select Insert > Chart.
|
| 2. |
In the Chart window, select Row and enter
the details as shown below.
|
| 3. |
Click OK. Resize the pie chart to fit within
the width of the cell.
|
| 4. |
Similarly insert a bar chart. Delete the text <insert
a vertical bar chart here>. Select Insert>Chart from
the menu. In the Chart window, select Row and enter the
details as shown.
|
| 5. |
Click OK. Resize the bar chart.
|
| 6. |
Save the template and preview in PDF format. Click Preview
and select PDF from the drop-down list.
|
Back to Sub Topic
Back to Topic List
You create a tabular report for which you have already loaded
the data. To create a table, perform the following steps:
| 1. |
Move the cursor below the table for the charts, add
some blank lines, and insert a new table. Click Insert and select
Table/Form.. from the drop-down list.
|
| 2. |
The Insert Table/Form window appears. Select Row
from the Data Source list on the left.
|
| 3. |
Drag Row from the left to the Template
column and select Drop all Nodes.
|
| 4. |
Delete the following fields by selecting the field and clicking the delete
button:
- @rownumber
- Position Nbr
- Acct Cd
- Monthly Rt
- Hourly Rt
As a result, you should have four fields.
|
| 5. |
Provide grouping details. Select Row in the template
column and, from the properties window, select DEPTID from the
drop-down list for the Grouping property.
|
| 6. |
Click OK. You are returned to Word. Resize the
table to match the image below by dragging the sizing handles for the
columns.
|
| 7. |
Use Word features and select the G DEPTID field and
cut and paste above the table.
|
| 8. |
Select the inner table and the control character E (as
shown).
|
| 9. |
Cut and paste the selected area a few lines below the
outer table.
|
| 10. |
Delete the outer table. Select the outer table and,
from the menu, select Table > Delete > Table.
|
| 11. |
Enter a new row at the end of the table to display the
department average annual rate. Place the cursor inside the row of the
table and, from the menu, select Table > Insert > Rows Below. Enter
Average Rate for the first column in the new row.
|
| 12. |
Change the column headings as shown.
|
| 13. |
Add a department average rate field to the last row
in the Annual Salary Rate column. Click anywhere in the empty Annual Salary
Rate column of the Total row to position your cursor. Select Insert
> Field.
|
| 14. |
In the Field window, select Annual Rt and
average for Calculation. Select the On Grouping check
box. Click Insert and then click Close.
|
| 15. |
Your table should look like the image below:
|
| 16. |
Change the format of the column data for Annual Salary
Rate. Double click ANNUAL_RT. The Text Form Field Options
window appears. Change Type to Number. Select the Number
format $#,##0.00 and edit it as shown. Enter $999 in the Default
number field. Click OK.
|
| 17. |
Similarly change the format for average ANNUAL_RT.
|
| 18. |
Save the template and preview in PDF format. Select
Preview > PDF. The preview report displays dummy data.
You can add other Word features to the template.
|
Back to Sub Topic
Back to Topic List
You now define a report with the RTF template that you created.
To define the report, perform the following steps:
| 1. |
Select XML Publisher > Report Definition.
|
| 2. |
On the Report Definition page, click Add a
New Value. Enter the report name PERSONAL_PAY.
|
| 3. |
For Data Source ID, click the Look up Datasource
icon.
|
| 4. |
Enter P in the begins with field and click
the Lookup button.
|
| 5. |
From the list, select PERSONAL_DATA_PAY as the
data source. Click the Add button.
|
| 6. |
On the Definition tab, enter a description for
the report. Select Active as the Report Status.
|
| 7. |
Click the lookup icon for Report Category ID.
|
| 8. |
Select the ALLUSER category.
|
| 9. |
On the Template tab, upload the RTF template.
Set the template Status to Active.
|
| 10. |
Click the Upload button. Browse for the Personal_Data_Pay.rtf
template and click Upload.
|
| 11. |
On the Output tab, select PDF
as the default output. Click Save. You can now view the report
in the Report Viewer.
|
Back to Topic List
| 1. |
From the left navigation bar in PeopleSoft Applications,
select Reporting Tools > XML Publisher > Query Report Viewer.
|
| 2. |
On the Query Report Viewer page, click the Search
button.
|
| 3. |
From the list of reports, select PERSONAL_PAY
and then click View Report.
|
| 4. |
While the report is being processed, the word Processing
appears at the top. After processing is complete, a three-page PDF report
is displayed with charts and a table.
Congratulations! You just created a report in PeopleSoft
Applications using XML Publisher!
|
Back to Topic List
In this lesson, you learned how to:
 |
Install
Oracle XML Publisher Desktop |
|
Modify an existing
query |
|
Create and register
a data source |
|
Create an RTF template |
|
Define a new report |
|
Run and view the XMLP report |
Back to Topic List
In this tutorial, you learned how to create reports using
XML Publisher in a PeopleSoft environment. XML Publisher is also available as
a stand-alone product known as "Oracle Business Intelligence Publisher"
(BI Publisher). BI Publisher has easy-to-use wizards and additional features that
enable access to a wider range of data sources, such as relational databases that
provide JDBC connections, XML feeds via HTTP, and Web services. BI Publisher speeds
up template creation and report definition, and it provides a greater variety
of template types and output formats (you can create templates using Excel to
create Excel worksheets or Adobe Flex to create Flash output). To learn more about
Oracle BI Publisher, choose several other OTN tutorials here.
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
Bookmark
