BI Publisher - Part 1 - Creating your first Report

In this article, we will create our first report using PS Query and BI Publisher.
If you have worked on Crystal reports, you will notice that there are some similarities between Crystal reports and BI Publisher reports. In crystal reports you use a query as data source and create a layout (template) using Crystal Reports application. Here we will use query as the data source and create a template using BI Publisher Desktop.

The example has been created and tested on the following software environment:
   Peoplesoft Application: FSCM 9.2
   Peoplesoft Peopletools: 8.53.02
   Peoplesoft Virtual Machine Image id: FSCMDB-85308-PI004
   Database: Oracle 11.2.0
   MS Office Professional 2010 32-bit

We will be logged in as VP1 so we do not have to spend too much time in security.

The major sections will be:
1) Setting up BI Publisher on your desktop.
2) Assigning BI Publisher Permissions to Users.
3) Creating a PS Query for using as a data source.
4) Creating BI Publisher template using MS Word.
5) Creating Report Definition.
6) Running BI Publisher Query Reports.
7) Scheduling BI Publisher Query Reports.
8) Running BI Publisher Reports in Process Scheduler.

Let us begin.

1) Setting up BI Publisher on your desktop
Assuming the BI Publisher Desktop is not installed on your desktop, we will download and install. Logon to My Oracle Support and navigate to http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html. Download and install the BI Publisher for Windows. Once installed, open MS Word and there should be a new Menu Item bar “BI Publisher” as shown below.

During template creation using BI Publisher a schema or sample XML data file is required (like for creating a new crystal report we use a query with sample data). We will use a sample XML data file and preview the template offline during the design process.

2) Assigning BI Publisher Permissions to Users
BI Publisher menu access is permission-list driven and depends on permission list and role assignment. PeopleTools delivers following permission-list security for BI Publisher report developers and BI Publisher power users.
   Permission list PTPT1000 - For access to only the BI Publisher Report Repository.
   Permission list PTPT2500 - For power users. Provides access to Query data sources for ad hoc reporting through Query Report Viewer and Query Report Scheduler, Design Helper, Report Definition, Content Library and Report Repository. 
   Permission list PTPT2600 - For report developers. Provides access to all BI Publisher components, including setup capability on the advanced feature Report Definition Bursting page.
Let us verify that user VP1 has the required access. We execute following SQL to see which of the above three Permission-Lists VP1 has.
    SELECT DISTINCT B.*
      FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
     WHERE A.CLASSID = B.CLASSID
       AND B.ROLENAME = C.ROLENAME
       AND A.CLASSID in ('PTPT2600','PTPT2500','PTPT1000')
       AND C.ROLEUSER = 'VP1';

So user VP1 has all the three Permission-Lists. We are good to go using userid VP1.

3) Creating a PS Query for using as a data source
In BI Publisher, the data extraction is separate from the data presentation. We will use sample data to design our Word RTF template. We will use PeopleSoft query for data extraction. Let us create a Query as shown below. This query is getting all accounts under tree node CURASSETS in the tree ACCTROLLUP for business_unit = US005, Fiscal_Year = 2001, Ledger = LOCAL.

The query is saved with the name CURRENT_ASSETS_BY_BU_FISCAL.
When the SQL is run, the following data is presented.

Let us download this resulting data to an XML file using the link above and save the file as CURRENT_ASSETS_BY_BU_FISCAL.XML.
In real life you would like to have prompts for business_unit and Fiscal_Year. Now that we have got a sample XML file, we will go back to the query and make prompts for business_unit and Fiscal_Year. So the Criteria page will look like the following.

When we execute the query, the prompt will look like

And the result set for those prompt values will look like

4) Creating BI Publisher template using MS Word
Open a new document in MS Word. Go to Menu bar item “BI Publisher”. The toolbar looks like the following.

There are icon groups like “Online”, “Load Data”, “Insert”, “Preview”, “Tools” and “Options”.
In the “Load Data” icon group, click on “Sample XML” icon and select the file CURRENT_ASSETS_BY_BU_FISCAL.xml that we downloaded from query results. Click Ok on the following dialog box.

In the “Insert” icon group, click on the “Table/Form” icon and drag the "row" from Data Source area to Template area. On the popup menu, select Drop All Nodes. The Insert Tables/Form should look like the following.

Drag the @rownumber back from Template area to Data Source area. Select Ok. The RTF template looks like the following.

The layout looks as shown below.

Before we can preview the data, we need to save the template. Let us save the file as type Rich Text Format (RTF) with the file name as CURRENT_ASSETS_BY_BU_FISCAL.RTF in an appropriate folder.
In the “Preview” icon group, click on the “RTF” icon. The output looks like the following.

In the “Preview” icon group, click on the “PDF” icon. The output looks like the following.

In the “Preview” icon group, click on the “HTML” icon. The output looks like the following.

In the “Preview” icon group, click on the “Excel” icon. The output looks like the following.

In the “Preview” icon group, click on the “PowerPoint” icon. The output looks like the following.

We will not pay too much attention to formatting and other cosmetic aspects at this moment. We will move forward to next step.

5) Creating Report Definition
Report definitions associate a data source with template files. To define report, let us navigate to Reporting Tools => BI Publisher =>Report Definition

Click on Add button. Enter Definition as shown below.

On the next tab “Template”, click on “Upload” button and load the template file CURRENT_ASSETS_BY_BU_FISCAL.rtf that we saved earlier. The page will look like the following.

We will leave the “Output”, “Properties” and “Bursting” pages as default. On the “Security” page, we will update security as follows.

On the Template page we will change the status of Template files from “In-Progress” to “Active”. Similarly on the Definition page we will change the Report Status from “In-Progress” to “Active”. Let us save the Report definition.

6) Running BI Publisher Query Reports
Let us navigate to Reporting Tools =>BI Publisher => Query Report Viewer.

Click on View Report Link. We are prompted for Business_Unit and Year. Let us enter those as US005 and 2000 respectively. The following report in HTML format is presented.

Let us change the format from HTML to XLS in the Query Report Viewer.

Click on View Report link and enter when prompted for Business_Unit and Year, enter those as US005 and 2001 respectively. The following report in XLS format is presented.

7) Scheduling BI Publisher Query Reports
Let us navigate to Reporting Tools => BI Publisher => Query Report Scheduler. Add a new run control. Data Source type is Query. When the Report Name is selected, we are prompted for Business_Unit and Year. Let us enter those as US005 and 2005 respectively. Template id is populated automatically for us. If there were multiple templates, we could have selected the one we want.

Click on the Run button.

Select the RTF format. Click on Process Monitor.

The process completed successfully. Navigate to Reporting Tools=> Report Manager. Click on Administration page. Our report is there on top.

Click on the link in the Description column. The report is downloaded and shown as below.

8) Running BI Publisher Reports in Process Scheduler
Running BI Publisher Reports in Process Scheduler is just like any other process type except that the process Type here is “XML Publisher”.
 
That is it for our first BI Publisher report.

Now go ahead and read the peoplebooks. I think you will be able to grasp more from peoplebooks after going through the above example. After that try one additional feature and go on. In the following articles I will cover advanced topics related to BI Publisher.