Tuesday, 11 July 2017

Creating Oracle BI Publisher Report using Template Builder

Template Builder is an add-in to Microsoft Word that simplifies the development of RTF templates for creating Reports.
In this example we will create a template for an Invoice which will be used to create an Invoice Report in BI.
  1. Create a New Data Model .
  2. In This example , the following SQL Query is used and the Data is generated.
/* Formatted on 7/11/2017 10:15:32 AM (QP5 v5.114.809.3010) */
  SELECT   C.PARTY_NAME,
           C.ADDRESS1,
           C.CITY,
           C.STATE,
           O.NAME,
           O.OPTY_ID,
           O.EFFECTIVE_DATE,
           R.QTY,
           R.UNIT_PRICE,
           R.REVN_AMT,
           R.COST_AMT,
           R.EXPECT_AMT,
           P.INTERNAL_NAME
    FROM   HZ_PARTIES C,
           MOO_OPTY O,
           MOO_REVN R,
           QSC_PROD_GROUPS_B P
   WHERE       (C.PARTY_TYPE = 'ORGANIZATION')
           AND (O.NAME = 'New Invoice')
           AND (O.CUST_PARTY_ID = C.PARTY_ID)
           AND (O.OPTY_ID = R.OPTY_ID)
           AND (R.PROD_GROUP_ID = P.PROD_GROUP_ID)
ORDER BY   O.LAST_UPDATE_DATE DESC



Note: (Create Sample Data by selecting the option Save As Sample Data)
  1. Then, we shall create a RTF Template using Microsoft Word Template Builder.
Log in to the BI Publisher from Microsoft Word by giving in the credentials and the Report server URL
 



 

  1.  Select the Data Model from the Catalog and Click on Create Report.
   

5.After the report is created the template can be designed by adding fields, Tables or forms. The functionalities of Word can be used to enhance the template design.

 


6.Save the template as RTF file and on the BI, and create a New Report. In this Case we are using the Data Model that was created earlier to build the Report.

 

Select the option Use Report Editor and click next.

7.Select the Layout for the Report. Here we choose to upload a RTF Template

 

8.Give a suitable Name for the layout and browse the template file and click Upload.



9. After the layout is uploaded, Click on view Report.

 

10.The Report will be generated according on the template that we had created.

 

The report can also be downloaded in PDF, RTF or any other format.

1 comment: