Creating Reports with Peoplesoft BI publisher(XML Publisher) and Connected Query

PeopleSoft BI Publisher(formerly XML Publisher)

 

Oracle Business Intelligence Publisher (BI Publisher, formerly XML Publisher) is an enterprise reporting solution that streamlines report and form generation.

A primary feature of Oracle’s BI Publisher product is the separation of the data extraction process from the report layout. BI Publisher enables you to design and create report layout templates with the more common desktop applications of Microsoft Word and Adobe Acrobat, and renders XML data based on those templates.

With a single template, it can generate reports in many formats (PDF, RTF, Excel, HTML, and so on) in many languages. This approach to reporting can dramatically reduce report maintenance, enabling power business users to adjust report templates without involvement of IT resources.

For creating reports, there are basically three steps:

1. Create Data source: BI PUBLISHER allows you to have 3 types of data source: PSQUERY, CONNECTED QUERY and XML file.
2. Creation of Report: Create the report template as per requirement.
3. Uploading report template into PeopleSoft and configuring other parameters.

Let’s begin :

Let’s create a simple report where we will print the basic profile information of an employee such as:

• Personal Information: Name, DOB, Photo of the employee.
• Current Job Information.
• Past Job Information
• Past Employments.
• National Id information .
• Profile data(Education, Certificates and Qualification).

We will make use of connected query in order to achieve this.

A Connected query is way to maintain parent-child relationship between queries.

You can create a parent query and its children and their children and so on to show on report.

This is really helpful as most of the reports are structured in Parent-Child manner.

We will create a parent query which will have Parent Data such as: Emplid, Name, Age, DOB, Photo of the employee i.e. is only one row per employee.

And rest of details will be child of this this query.

All the queries (Parent and Child are to be created as PSQUERY and then linked using Connected Query).

In Case you are not aware how to create PSQUERY , please refer the tutorial on creation of PSQUERY.

Creation of Parent PSQUERY:

• Personal Information: Name, DOB, Photo of the employee.

The SQL of parent query looks something like below.

And the output looks like below.

You may create a query similar.

Creation of Child PSQUERIES:

• Current Job Information.

SQL for Current job should look similar to this.

And output should look similar to below.

• Job History

SQL should like something below.

Output should look something like below:

• Prior Work Experiences .

SQL should like something below.

Output should look something like below:

• National Id information .

SQL should like something below.



Output should look something like below:

• Profile data(Degree).

SQL should like something below.

Output should look something like below:

SO, we have created all necessary PSQUERIES.

Now time to create Connected Query.

Add a new Connected Query:

Add the Parent-Children queries as shown below:

Map Parent with child:

Do this for each of the query.

Learn about how to create connected query for more details.

Each of the child query is mapped with parent.

After that change the status to Active and save the page.

One can test the Connected query by clicking on PreviewXML and check if the result is coming according to the expected result.

I have added additional criteria on our parent query to prompt for employee id for which the query has to be triggered. Otherwise the query will return a huge number of rows.

There might be some minor tweaking needed for all queries, you can modify it accordingly.

SO the first part of creation of PSQURIES has completed.

Now we have reached the second step in this to create the report.

For creating report, you may take copy of any existing report and can start editing.

Sample reports are also available at path where you would have installed BI publisher.

For my machine, the path looks something like below.

C:Program Files (x86)OracleBI PublisherBI Publisher DesktopTemplate Builder for Wordsamples

There you can see a number of samples.

Since we will be using RTF to create the report template we will go inside RTF template folder.

Inside RTF templates folder , there are many sample report, you may pick any and start working.

I have taken the copy of Balance Letter and rename it as Employee Profile.

Let’s clear out the existing content in the report.

To work with the report we need a sample XML data, so that we can test the report.

The same XML data can be fetched from the report definition.

We still haven’t created the report definition and will create now.

Registering a report starts with creation of Data Source.

Add a new value:

You can generate a sample file by clicking on Regenerate/Generate link , which can be used for the report.

Copy the content of XML and create and XML file with that content.

Now open the template and go to BI Publisher tab, and click on Sample XML and load the .xml doc you saved just before.

Do the same for all the Child Query and you will be template something like below:

Rename the Column Headers, remove the unnecessary columns. Add header for each of the section .The template might look like something below:

You can Preview the template, by clicking on the PDF/HTML/EXCEL etc link at the top on the BI PUBLISHER ribbon.

Tempate design is over, Third and Last step is to create Report Definition.

Navigate as below and click on add a new value.

Give a name to your report.

Select the data source type as Connected Query and select the data source id which we created earlier.

Enter the details as below. Make sure “Report Status” is set to Active once you are done with the data entry.

Upload the template you created here.

Change the Status to Active once you are done.

Leave the other Output tab as it is unless you want to change the default setting.

Keep this tab as it is.

Add the Security for the Report.

Keep the Bursting Tab as it is, we need not use it now.

It can be used for bursting option.

Before saving the component make sure Status are active as shown below.

Save the page.

Trigger the report by scheduling it.

Navigate as below and add a runcontrol id.

Provide the details as below for the report definition which was created earlier.

Click on run.

Select Format as PDF or any other preferred format.

Click on OK to run the report.

Go to report Manager to view the report.

Click on the link to view the report.

And our report is generated.

Report template can be modified to adjust look and feel.

The PSqueries and connected query can be modified to adjust the data available to XML report.

There are other useful features also which comes with BI PUBLISHER.

1. Sorting
2. Grouping
3. Conditional Formatting
4. Condition Region
5. Additional Coding using XSLT.

Apart from Connected Query and PSQUERY, BI PUBLISHER can also use XML file as input. For that we create the XML file using the Peoplecode or any other utility and pass it to the Report.

In that manner the data can be manipulated with much more flexibility.