• Post category:BI Publisher
  • Post comments:3 Comments
  • Post last modified:January 22, 2021
  • Reading time:3 mins read
You are currently viewing Adding hyperlink in Email using BI Bursting
Adding hyperlink in Email using BI Bursting

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

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,
'[email protected]' PARAMETER1,– to mail address
'[email protected]'              PARAMETER2,– CC mail address
'[email protected]' 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
'[email protected]' 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 - Adding hyperlink in Email using BI Bursting

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.

  • 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.

    • 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

  • In visible box by plugintheme