Adding hyperlink in Email using BI Bursting
Adding hyperlink in Email using BI Bursting

 1,609 total views

In order to achieve the hyperlink in email body of BI Publisher Report, we need to write html code in the bursting SQL Query.

Want to learn Fusion Technical tools? Checkout this article

We will add the bursting query to the Data Model in the bursting query section.

Sample bursting SQL for Email delivery option:

select '1' KEY, — must be same as deliver by
'TEST' TEMPLATE,–  report template name
'RTF' TEMPLATE_FORMAT,– template format
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,– output format pdf rtf text etc..
'EMAIL' DEL_CHANNEL,– delivery channel name email,fax,print etc..
'Test Email' OUTPUT_NAME,
'test.test1@gmail.com' PARAMETER1,– to mail address
'test@abcd.com'              PARAMETER2,– CC mail address
'test.test1@gmail.com' PARAMETER3,– from mail address
'Test Subject'    PARAMETER4, — subject
'<html>Hi'||' '||'xxxx'||',<br/><br/>
The survey will take approximately 10 minutes.  
Please read each question carefully and select the response that best fits your experience.'<br/><br/>
<a href=' || ”'http://www.google.com”' || '>Click here to take the Exit Survey</a><br/><br/>'
Thank you,<br/>Company HR</html>'   PARAMETER5, — message body
'true' PARAMETER6– attachment true/false value
'test@abcd.com' as PARAMETER7, — reply-to mail address
from dual

<a href has been used to add the hyperlink..
<br/> has been used to add new line character..

Sample Output:

Sample output of BIP Bursting with email hyperlink

Similarly we can add an image to the email body using the below html command:

​'<img src=”' || https://www.w3schools.com/images/w3schools_green.jpg || '” alt=”alternate text for image”>'

We need to upload the image to UCM and then copy the UCM hyperlink and use it in the bursting SQL.

Tip: We can do bursting for Email/FTP delivery options. The output will be burst by the split by key and will get delivered to the deliver by key values.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 3 Comments

  1. Viswa

    Hi Sricharan,

    Does HTML Code support in Bursting Query as I have tried your bursting query in data model SQL to run I am getting an Error “Invalid Column Index”.

    I believe your query will accept at Bursting Level not the data model level.

    1. Sricharan

      Yes Viswa.. That’s right..we will write this sql in the bursting query as i mentioned in the article.
      In the main data set we will fetch whatever fields that are required to be on the html or in the output file.
      Let me know if you still have any questions.

      Regards,
      SM

  2. Viswa

    Thanks Sricharan for the update

Leave a Reply