Oracle Apex - Create URL region.

How to Embed BI Publisher Report into a Page in Oracle Apex?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

In this tutorial, you will learn how to embed BI Publisher Report into a page in Oracle Apex. Follow these steps:

Embed BI Publisher Report into a Page in Oracle Apex

  1. In Oracle Apex, click on the Shared Components > Report Queries and then click on the report query you want to embed the report for it. Then set the Output Format as PDF, View File as Inline and then copy the URL. As shown in the below image:

Copy BI Report URL - Oracle Apex

  1. Now save the changes and open the page in which you want to embed to BI Report in Oracle Apex page designer.
  2. Then create a region and select its type as URL and then click on the Attributes and paste the URL in the URL field as shown in the below image:

Oracle Apex - Create URL region.

Now save the changes and run. You will see the preview of the BI report you created.

Note: If you are using the page items in the BI report query, then you have to create page items in the page you are embedding the URL and populate the values to produce the output for the report.

See also:

This Post Has 9 Comments

  1. Bheem Vangi August 13, 2020

    how to use where condition for report query & pass page items

    1. Vinish Kapoor August 13, 2020

      Simply add the where clause to your SQL query and refer page items with colon, for example, :p2_empno.

      So the page having BI report must be submitted before viewing the report or open another page by passing parameters.

  2. Varduhi Kababjyan October 17, 2020

    Hi Vanish,

    Is there a way to dynamically append date and time to Report Query Time at run time?

    I tried report_name_%d%m%y and file_name_{$SYSDATE()$} with no luck.

    BTW thank you for all your blog post. I refer to your tutorials very often and they are very informative and helpful.

    1. Vinish Kapoor October 17, 2020

      Thank you, Varduhi.

      But I didn't get your question correctly.

      Do you mean you want to download the report with the name and date suffix?

    2. Varduhi Kababjyan October 18, 2020

      Hi Vinish,

      Yes, I want the downloaded file to have the file name + the date and the time appended to it.

      ex: file_name_10_17_2020_11_15_12.

      Thank you

    3. Vinish Kapoor October 18, 2020

      To download a report directly on a button click, create an application process of Ajax type. Below is an example:

      DECLARE
  vBlob blob;
  vmimetype varchar2(500) := 'application/pdf';
  e_error exception;
  v_filename varchar2(100);
BEGIN


  vBlob := Apex_Util.Get_Print_Document(p_Application_Id     => :App_Id,
                                               p_Report_Query_Name  => 'your_bi_query_name',
                                               p_Report_Layout_Name => 'your_bi_layout_name',
                                               p_Report_Layout_Type => 'rtf',
                                               p_Document_Format    => 'pdf');


    if nvl(dbms_lob.getlength(vblob), 0) = 0 then
       raise e_error;
    end if;


    v_filename := 'file_name_'||to_char(sysdate, 'dd/mm/yyyy');
    
  owa_util.mime_header(vmimetype,false);
  htp.p('Content-Length: ' || dbms_lob.getlength(vBlob)); 

  htp.p('Content-Disposition: attachment; filename=' || v_filename);
  htp.p('Set-Cookie: fileDownload=true; path=/');


  owa_util.http_header_close;  
  wpg_docload.download_file(vBlob);
  exception 
     when others then
     htp.p('No data found.');
   
END;

      Now copy the application process URL and call it on click of the button. Try this and let me know if any issues.

    4. Varduhi October 19, 2020

      Hi Vinish,

      Thank you for the example :).

      I change the file format from Pdf to xls and the file name is 'f' when I run export the query.

      See the attached screenshot.

      Thank you so much.

      Varduhi

    5. Vinish Kapoor October 19, 2020

      Oh actually I made the mistake. The file name does not allow the back or forward slash characters. So replace the v_filename as following:

      v_filename := 'file_name_'||to_char(sysdate, 'yyyymmdd')||'.xls';

      Or change it with any name with valid characters.

    6. Varduhi October 19, 2020

      Thank you so much! I got it working.

Comments are closed.