Oracle Apex - Create URL region.

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

by

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:

9 thoughts on “How to Embed BI Publisher Report into a Page in Oracle Apex?”

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

    Reply

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

    • 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

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

    • 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

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

Leave a Comment