Tuesday, January 16, 2024

How to Create Sub-Templates with BIP Reports in Oracle Fusion Cloud

Let's see how we can create a subtemplate in a BI Publisher report in Oracle Fusion Cloud.

The Custom Report and Layout:

Before delving into the world of subtemplates, let’s take a moment to understand the foundation of a simple BI Publisher report. Let's assume we're working on a custom report, which includes a few key columns from the AP invoices and has some standard conditions.

When running this query, we can see an output, typically, a few invoices displaying their numbers, dates, and amounts.

In the BI Publisher catalog, under 'Custom -> Financials -> Payables' folder, we have a custom report titled ‘Custom Invoices’ with an attached RTF layout based on our data model. 

Existing RTF Template:

Creating the Sub Template:

Step 1: Create Your RTF File

Begin with a plain Word document. You will need to save it as an RTF file, 'subtemplate.RTF', for example.

Step 2: Define the Template Tag

Within the Word document, define your template tag. Here’s how you do it:

<?template:cust_template?>... Your content here ...<?end template?>

We just named our template 'cust_template'. For illustrative purposes, let's say we add an image representing a logo within this tag.

Step 3: Uploading to BI Publisher

In BI Publisher:

Navigate to the 'New' menu.

Select 'Sub Template'

Upload your RTF file

Save it. 

Now, the Subtemplate resides in the catalog and it's ready for use.

Embedding the Subtemplate into our mail RTF:

To incorporate the Subtemplate into the main RTF file, we first need to locate the Subtemplate's path in the catalog. It usually ends with an XSB extension.

Navigate to More -> Properties on the Subtemplate

This will confirm that the Subtemplate is a XSB file

Note down the path from Location field (exclude /shared part)

So in this case, our path to XSB (Subtemplate) file is /Custom/Financials/Payables/SubTemplate.xsb

Now, we need to add the import tag within our main RTF template like so:




- Do not include 'shared' in the path.

- Use double slashes after 'xdoxsl:' to denote the root of the path.

- Import as well Call to the SubTemplate is a must to make it work correctly.

- Remember, 'cust_template' corresponds to the template name we defined earlier.

Note: If you have multiple Subtemplates in a single XSB file, they can be included and called upon selectively, based on varying conditions.

Extending the Data Model and Subtemplate Usage:

What happens if we decide to extend the data model? 

Let's say we add a 'GL_DATE' column to the model and we want to show this column in the SubTemplate ?

Could we reference this new column in our Subtemplate? Absolutely! Simply add a placeholder for 'GL_DATE' in the Subtemplate, just like any other field. Since the Subtemplate is part of the main RTF template, it automatically has access to the entire data model.

Now, let's update our SubTemplate with the modified RTF file.

Re-upload the updated 'subtemplate.RTF'.

Overwrite the existing one if prompted.

To ensure your changes are reflected, clear the BI Publisher cache.

Navigate to 'Manage BI Publisher' section under 'Administration'.

Clear Object Cache:

Now, you're all set to run your reports with the new changes effectively displayed!

The Final Output:

As a result of these steps, the final report would showcase the company logo and any other elements defined in the Subtemplate. For instance, the 'GL_DATE' will appear alongside other invoice details, as demonstrated:


Subtemplates in Oracle Fusion Cloud's BI Publisher are incredibly versatile. They allow us to incorporate reusable elements, such as logos or standard headers, fostering consistency and saving time when running multiple reports.