• Post author:
  • Post category:Apex
  • Reading time:2 mins read
Oracle Apex - Create URL region.

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:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

This Post Has 9 Comments

  1. Bheem Vangi

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

    1. Vinish Kapoor

      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

    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

      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

      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

      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

      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

      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

      Thank you so much! I got it working.

Comments are closed.