While working as a BI Developer I’ve had a chance to use several Oracle technologies on different projects. Usually clients tend to combine a least two products from the same tech stack as they are intuitively integrated and easy to use together.
However, recently I worked on a project that involved two Oracle technologies, namely PeopleSoft and BI Publisher (Oracle Business Intelligence Publisher), but I could only use them separately. That didn’t make much sense to me so I wanted to find out if there is a way to leverage report generating technology of BI Publisher directly within PeopleSoft and in that way make life easier for both end users and developers.
I’ll use this post to show you how to use BI Publisher for PeopleSoft and enable developers and/or business users to manage data sources and create reports based on user-defined criteria in PeopleSoft.
Creating PeopleSoft Reports with BI Publisher
Firstly, a quick description of both tools and how they work together.
Oracle PeopleSoft applications are designed to address the most complex business requirements. PeopleSoft PeopleTools is a development toolset that supports the development and runtime of PeopleSoft applications by leveraging metadata and other efficiencies.
Oracle Business Intelligence Publisher (also known as XML Publisher) is a template-based reporting tool that uses standard technologies for data extraction and display. It divides Data Logic from the Presentation Layout during development of the reports and combines them later during run time. BI Publisher enables users to create report layout templates with common applications such as Microsoft Word and it renders XML data based on those templates.
PeopleSoft Query (PSQuery) as well as any PeopleTools applications providing XML data can be used in BI Publisher as a data source. So in practice, data extraction logic could be designed using Application Engines/PeopleCode or PSQuery independently and an end user can design the Presentation using common desktop tools such as Microsoft Word based on the data source created. Because queries can also be used as a data source, BI Publisher extends the flexibility of the tool to the same people who need to view the reports – end users.
What do you need?
For this example I’ll use PeopleSoft Query as a source for a BI Publisher report created using Microsoft Word. You should be familiar with Oracle and specifically PeopleSoft and BI Publisher in order to follow the steps described below.
For more info on both products visit the following sites:
- PeopleTools running with existing PS Query saved and accessible under Query Manager
- Java Runtime Environment (JRE) and Microsoft .Net
- Microsoft Word
These are the steps I’ll go over:
Install BI Publisher Desktop – > Configure BI Publisher – > Create an RTF Template – > Define a Report
Install Oracle BI Publisher Desktop
BI Publisher Desktop Tool is used to modify RTF templates which also includes a Microsoft Word plug-in for template development. You can download it from the Oracle Technology Network: http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html
There is also an option to get the XML Publisher version (older) shipped along with PeopleTools:
- Log in to PeopleSoft
- Navigate to: Reporting Tools > XML Publisher > Setup > Design Helper
Both options will download a file you will execute and it will install the BI Publisher toolbar in Microsoft Word:
Configure BI Publisher (XML Publisher)
In order to develop BI Publisher reports you need to have XMLP Report Developer role so you can change the data source type to PS Query when adding a new report definition. The report definition is based on the report category ID and author ID so if you want to check if you have the appropriate author ID role you can use the following query:
select * from PSXPRPTCATAUTH
To configure roles and users that will utilize the reports in the category you need to predefine Editors/Viewers of a group of reports:
- Navigate to: Reporting Tools > XML Publisher > Setup > Report Category
- Add Role under Report Definition Editors and Viewers:
Before creating any report you need to create and register a data source:
- Navigate to : Reporting Tools > XML Publisher > Data Source
- Click Add a New Value and select PS Query as the Data Source Type and click the search icon for Data Source ID
- Find and click on the PS Query you want to use in the Search results and click the Add button on the Data Source page
- In the Data Source Properties section, enter the Description and Generate under the Generate File column for Sample Data File and Schema File – save the XML and XSD files the browser displays
Create an RTF Template
To create report template in Microsoft Word you need to import the XML and XSD file generated from the Data Source Creation. For that you will use the Load Data group of commands which enables you to load a saved sample data or sample schema to Template Builder:
From that step forward you have several options and the starting point would be to use the Table Wizard under Insert menu that guides you smoothly through the creation of tables used in typical reports:
Here you will go through a series of steps for the generation of the initial table such as: selecting table characteristics, selecting fields for the report from your Data Source, selecting table criteria (group by, sort by), editing fieldnames, adding headers/footers and so on. Finally, save the generated file as an .RTF document and you will use it in your report definition.
Define a Report
All you have to do now is to define a report with the RTF template you created. Here is how you do it:
- Navigate to: Reporting Tools > XML Publisher > Report Definition
- On the Report Definition page click on Add a New Value and enter the report name
- For the Data Source ID, click on the Look up Datasource icon and select your PS Query as the data source with the Add button
- On the Definition tab, enter a description for the report, select Active as the Report Status and click the lookup icon for Report Category ID to select the ALLUSER category
- Switch to the Template Here you will upload your RTF template and set the template Status to Active
- Finally, switch to the Output tab where you can select the default output format of the report. You can choose from multiple formats (PDF, HTML, RTF, Excel and so on)
The last step is to actually run and see your report and to do that you need to go back to the left navigation bar in PeopleSoft Applications and select: Reporting Tools > XML Publisher > Query Report Viewer.
Here you will see a Search button you can use to find your report and select View Report option from the search results.
And that’s how you create a report in PeopleSoft using XML Publisher.
This is just a basic example of the functionalities of both products and how well they work together. Modern reporting needs are constantly changing and analytical options are embedded directly into business process flows so users can generate reports in context, within transactions.
PeopleSoft now offers Simplified Analytics among these new reporting capabilities. Simplified Analytics are new reporting features that enable end users and business experts to take control and generate the reports they need without having to rely on technical professionals.
Nevertheless, enterprises will still need conventional reports for activities such as financials and scheduling and distribution of other regulatory reporting. In those cases and for similar report needs as the one described in this post, powerful and integrated BI Publisher remains available and fully supported by PeopleSoft.