BI Publisher - Part 2 - Using Connected Queries

This article is in continuation of a previous article "BI Publisher - Part 1 – Creating First Report using Query
". Here we will create a connected query and use as a data source for a BI Publisher Report. We will also cover some Advanced Report features.

Since we covered steps involved in creating a Query and our first BI Publisher report in previous article, we will not go into all the details already covered, including setup and installation. It is assumed that BI Publisher Desktop is correctly installed on your desktop. So it is important that you go through Part 1 if you are new to BI Publisher.
 
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

We will be logged in as VP1 so we do not have to worry about security administration.

The major steps will be:
1) Describing the Connected queries and advantages.
2) Creating a Connected query.
3) Creating BI Publisher report with data source as Connected query.

Let us begin.

1) Describing the Connected queries and advantages.
The connected Query is introduced in PeopleTools 8.50. It enables you to create a single XML file based on a set of queries with parent-child relationships. Connected Query processing creates a hierarchical output where a single row of data from parent query results in a set of rows in a child query. We can create a set of simple queries to extract data from multiple tables in separate result sets first and then connect them to create a connected query instead of creating a complicated query with multiple equal, outer joins, and grouping conditions. If you have worked with sub-reports in Crystal Reports, you will find it somewhat similar to that.
In order to use a Connected Query you will need to get access to the pages through the Permission List PTPT2700.

2) Creating a Connected query.
Before creating the connected query, let us review the sample data. Suppose the requirement is to list customers based on certain criteria and list all the activities they had during a period. Even if there is no activity, the selected customers must be listed.
We create a Query for listing the customers first and save the query as AJ_CONN_CUST.
 
The SQL behind the query is 
SELECT B.CUST_ID, B.NAME1, A.COUNTRY, A.STATE
  FROM PS_CUSTOMER B, PS_CUST_ADDRESS A
 WHERE  B.SETID = 'SHARE'
   AND B.SETID = A.SETID
   AND B.CUST_ID = A.CUST_ID
   AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_CUST_ADDRESS A_ED
                   WHERE A.SETID = A_ED.SETID
                     AND A.CUST_ID = A_ED.CUST_ID
                     AND A.ADDRESS_SEQ_NUM = A_ED.ADDRESS_SEQ_NUM
                     AND A_ED.EFFDT <= SYSDATE)
   AND A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM
   AND B.CUST_ID IN ('USA04','1006','1003') ;
Copy this query as AJ_CONN_CUST_ITEMS. Add the ITEM table and use a standard join using CUST_ID. We want to see only those transactions where ACCOUNTING_DT  is 2012 or later.  The SQL behind the query is
SELECT B.CUST_ID, B.NAME1, B.CUSTOMER_TYPE, A.COUNTRY, A.STATE, C.BUSINESS_UNIT, C.ITEM, C.ITEM_LINE,
       C.ITEM_STATUS, C.ENTRY_TYPE, C.ENTRY_REASON, TO_CHAR(C.ACCOUNTING_DT,'YYYY-MM-DD'), C.PYMNT_TERMS_CD
  FROM PS_CUSTOMER B, PS_CUST_ADDRESS A, PS_ITEM C
 WHERE  B.SETID = 'SHARE'
   AND B.SETID = A.SETID
   AND B.CUST_ID = A.CUST_ID
   AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_CUST_ADDRESS A_ED
                   WHERE A.SETID = A_ED.SETID
                     AND A.CUST_ID = A_ED.CUST_ID
                     AND A.ADDRESS_SEQ_NUM = A_ED.ADDRESS_SEQ_NUM
                     AND A_ED.EFFDT <= SYSDATE)
   AND A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM
   AND B.CUST_ID IN ('USA04','1006','1003')
   AND B.CUST_ID = C.CUST_ID
   AND  TO_CHAR(C.ACCOUNTING_DT,'YYYY-MM-DD') >= '2012-01-01' ;
The above query returns rows related to customers 'USA04' and '1003' only. Only these two customers have transactions on or after 01-JAN-2012. In order to include the remaining customers, even if they do not have transactions, we need to change the Standard join with ITEM table to OUTER JOIN. After outer join, all expected customers show up. The query looks like the following.
SELECT B.CUST_ID, B.NAME1, B.CUSTOMER_TYPE, A.COUNTRY, A.STATE, C.BUSINESS_UNIT, C.ITEM, C.ITEM_LINE,
       C.ITEM_STATUS, C.ENTRY_TYPE, C.ENTRY_REASON, TO_CHAR(C.ACCOUNTING_DT,'YYYY-MM-DD'), C.PYMNT_TERMS_CD
  FROM PS_CUSTOMER B, PS_CUST_ADDRESS A, PS_ITEM C
 WHERE  B.SETID = 'SHARE'
   AND B.SETID = A.SETID
   AND B.CUST_ID = A.CUST_ID
   AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_CUST_ADDRESS A_ED
                   WHERE A.SETID = A_ED.SETID
                     AND A.CUST_ID = A_ED.CUST_ID
                     AND A.ADDRESS_SEQ_NUM = A_ED.ADDRESS_SEQ_NUM
                     AND A_ED.EFFDT <= SYSDATE)
   AND A.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM
   AND B.CUST_ID IN ('USA04','1006','1003')
   AND B.CUST_ID = C.CUST_ID (+)
   AND to_date('2012-01-01','YYYY-MM-DD') <= C.ACCOUNTING_DT (+) ;
The data looks like the following, with (null) in columns from ITEM tables for customer '1006'.

We want to present the data in a format like

To create a report like above, we will create a connected query. We will use the query AJ_CONN_CUST as the parent query to retrieve the customer data. We will create a child query to retrieve the transaction data from the ITEM table that will have the following SQL behind it.
SELECT C.BUSINESS_UNIT, C.CUST_ID, C.ITEM, C.ITEM_LINE, C.ITEM_STATUS, C.ENTRY_TYPE, C.ENTRY_REASON, TO_CHAR(C.ACCOUNTING_DT,'YYYY-MM-DD'), C.PYMNT_TERMS_CD
  FROM PS_ITEM C
 WHERE to_date('2012-01-01','YYYY-MM-DD') <= C.ACCOUNTING_DT;
Note that this child query does not have any criteria for CUST_ID. The criteria will be applied when this query is connected to the parent query that we will see shortly. Save this child query as AJ_CONN_ITEM.

Navigate to Reporting Tools => Connected Query => Connected Query Manager and click on ADD a New Query link and specify the values as shown below.

In the Parent Query edit box, click on search icon and select AJ_CONN_CUST as the parent query. The Connected Query Structure area is displayed as shown below.

Click the Insert Child Query icon, right next to AJ_CONN_CUST in the Connected Query Structure area and select the query AJ_CONN_ITEM query from the dialog box presented. The Connected Query Structure area will look like the following.

Click the Select Related Field icon, right next to X icon in the Connected Query Structure area, to relate fields from child query to parent query so the child query will retrieve rows related to the current parent row only while processing a row of the parent query. The Connected Query Structure area should look like the following.

Save the query. Click on the Preview XML button to preview XML. Save the XML in the preview window as an XML file with the file name AJ_CONN_CUST_ITEM.xml in appropriate folder.

3) Creating BI Publisher report with data source as Connected query.
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 AJ_CONN_CUST_ITEM.xml that we downloaded from XML preview. Click Ok on the “Data loaded successfully” dialog box. In the “Insert” icon group, click on the “Table/Form” icon and drag the "AJ_CONN_CUST " node from Data Source area to Template area. On the popup menu, select Drop All Nodes. Click on AJ_CONN_CUST in the Template area and select Style as Free-Form and Grouping as B.CUST_ID. Select Style as Free-Form on B.CUST_ID. The Insert Tables/Form should look like the following.

Select Ok.
The layout looks as shown below.

Save the file as type Rich Text Format (RTF) with the file name as AJ_CONN_CUST_ITEM.RTF in an appropriate folder.
In the “Preview” icon group, click on the “RTF” icon. The output looks like the following.

As expected, the customer 1006 does not have any transaction but is still listed and the customer 1003 has transactions listed as expected.
We will now make a few adjustments to the layout, formatting the column headings, removing cust_id from table, etc.. The final RTF looks like the following.

Let us preview the report in HTML format. The report looks like

That completes creation of a BI Publisher report with Connected Query as the data source.
As discussed in Part 1, you can create a Report Definition with Data_SOURCE_TYPE as connected-query and create Process Definition, if you want to.


Once you are comfortable with what we have done in this post, please go ahead and try the following things.
1) In the parent query, remove the hard coding of four customers and make it driven by prompts on CUST_ID range, COUNTRY and STATE, etc.
2) Have a page break before each customer (except the first one to avoid first page a blank).
3) Think about removing the hardcoding to_date('2012-01-01','YYYY-MM-DD') <= C.ACCOUNTING_DT and replacing it with a prompt.