• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:October 28, 2020
  • Reading time:8 mins read
You are currently viewing Adding External PDF attachments to bursted PDF output
Adding External PDF attachments to bursted PDF output

In this article we will look into how to add a static PDF/conditional static pdf to the existing bursted PDF output.

There could be scenarios where notifications should be sent to employees along with an additional pdf content which would change frequently. Normally we will keep the entire content on the rtf template and generate pdf output with bursting query and send it to employees. However whenever there is a change, we will have to modify the template everytime.

Table of Contents

Usecases:

  1. Sending New Hire Notification to employees. You can include a PDF with Induction, Do’s, Don’ts, Contact persons list which could change frequently.
  2. Sending New Year notification for all employees. You can include PDF with holiday list for that specific year and this holiday list will change every year.
  3. Sending Appraisal/Compensation Notifications to employees. You can include info of compensation raises across the organization for that particular year.

In all of the above cases, there is a content which will change by employee and that part is covered in the rtf template that is created. For the remaining part that will be changed once in a while, we can upload the PDF file directly to UCM and use the attachment feature of the bursting query to directly add that PDF file to the existing bursted PDF output.

As per Oracle’s documentation we can even attach PDFs of Invoice , “You may have a requirement to attach PDFs along with invoices for customers. You can now attach these documents as an attachment along with the invoice while bursting“. However I was not successful in achieving this part yet.

Adding External attachment feature works only for the PDF output and the existing bursting query should also send the PDF output only.

Let’s look into more details on this functionality with a sample use case.

I will take up an existing Person details report and will try to add a static sample pdf attachment to it using bursting query.

The output of the Person Details Report will look like below on the normal run of the report:

image 13 - Adding External PDF attachments to bursted PDF output

Now, let’s take a static PDF like below and try to add it to the output pdf:

First, we will have to upload the static pdf to UCM using Content Server URL – https://abcd-dev.fa.us2.oraclecloud.com/cs. Use the Security Group as UCM_Spaces, else the BI Report won’t be able to fetch the document.

image 18 1024x450 - Adding External PDF attachments to bursted PDF output

Now, Let’s navigate to the Data Model and add dummy bursting query for dynamic file name

image 16 1024x406 - Adding External PDF attachments to bursted PDF output

Bursting Query:

SELECT
'DUMMY' AS "KEY",
'Demo' TEMPLATE,
'PDF' OUTPUT_FORMAT,
'Bursted Output File' as OUTPUT_NAME
FROM dual

With this bursting query, if we run the BI Report we would get the output as below:

Now, Let’s add the static PDF that we uploaded to UCM to the bursting section.

  1. Click the Attachment tab.
  2. Select the content server name from the Attachment Repository LOV. By default it would be FA_UCM_PROVISIONED.
  3. Define the SQL Query for the attachment in the Content Server.
image 17 - Adding External PDF attachments to bursted PDF output

We can fetch the document details of uploaded file using the REVISIONS table (This is a hidden table for which there isn’t any documentation available).

Query to fetch the Document ID for the uploaded file:

select 'DUMMY' Key, Max(did) as ATTACHMENT_ID 
FROM revisions 
WHERE did IN (SELECT did FROM revisions WHERE UPPER(ddoctitle) = 'DEMO')

The column alias names KEY and ATTACHMENT_ID are mandatory. Key should refer to the Split by and Deliver by Keys defined on the “Bursting Query” tab and should be present on the Data Model SQL Query as well.

Once, the SQL Query is added, save the Data Model, enable the Bursting for BI Report and run the Report.

image 19 - Adding External PDF attachments to bursted PDF output

We can see that the output PDF contains two pages now with the second page from the external pdf that we uploaded to UCM.

References: Oracle Documentation

Hope this demonstration was useful and will help you when you have similar requirements.

If you have any questions, please post them in comments section.