Generating the report with APEX_DATA_EXPORT

-

 With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON.


Step 1: Create a table and populate it with some sample records.

CREATE TABLE emp
  (
    empno        NUMBER,
    first_name   VARCHAR2(240),
    last_name    VARCHAR2(240),
    mgr          NUMBER,
    deptno       NUMBER,
    sal          NUMBER,
    created_date TIMESTAMP (6),
    comm         NUMBER,
    hiredate     DATE,
    JOB          VARCHAR2(240),
    ename        VARCHAR2(240),
    PRIMARY KEY (empno) USING INDEX ENABLE
  );
/
  
INSERT INTO emp (empno, first_name, last_name, mgr, 
                 deptno, sal, created_date) 
       VALUES
                (1, 'Larry', 'Ellison', ,
                 10, 5000, LOCALTIMESTAMP);
 
INSERT INTO emp (empno, first_name, last_name, mgr, 
                 deptno, sal, created_date) 
       VALUES
                (2, 'Juan', 'Juan', 1,
                 10, 3500, LOCALTIMESTAMP);

INSERT INTO emp (empno, first_name, last_name, mgr, 
                 deptno, sal, created_date) 
       VALUES
                (3, 'Keith', 'Block', 1,
                 10, 3800, LOCALTIMESTAMP);
/

Step 2: Create a new blank page.  

Note: The page number I had was 13. You need to replace the reference to "P13" with your own page number if it is different.

Step 3: Create a new region on the page. In the Property Editor, apply the following changes:

Under Identification:
     For Title - enter Generating the report with APEX_DATA_EXPORT
Under Layout:
     For Position - select Breadcrumb Bar
Under Appearance:
     For Template - Hero
     For Icon - enter fa-download

Step 4: Navigate to that Region and right-click Create Button. In the Property Editor, apply the following changes:

Under Identification:
     For Button Name - enter DOWNLOAD
     For Label - enter Download Report
Under Layout:
     For Region - select Generating the report with APEX_DATA_EXPORT 
     For Position - select Next
Under Appearance:
     For Button Template - select Text with Icon
     Enable Hot button
 For Template Option - Set Style Simple
     For Icon - enter fa-download

Step 5: Create a sub region on the parent region (Generating the report with APEX_DATA_EXPORT) . In the Property Editor, apply the following changes:

Under Identification:
     For Title - enter Report Format
Under Layout:
     For Parent Region - select Generating the report with APEX_DATA_EXPORT 
     For Position - select Sub Regions
Under Appearance:
     For Template - Blank with Attributes
 
Step 6: Create a select list on the sub region (Report Format). In the Property Editor, apply the following changes:

Under Identification:
     For Name - enter P13_REPORT_FORMAT
     For Type - Select List
Under Label:
     For Label - enter Report Format
Under Layout:
     For Region - select Report Format
Under Appearance:
     For Template - Hidden
     For Template Options - set Size as Large and Right Margin as Small
Under List of Values:
     For Type - Static Values
     For Static Values - Excel:E; PDF:P; CSV:C; HTML:H; JSON:J; XML:X;
     Display Extra Values - No
     Display Null Values - No
Under Default:
     For Type - Static
    For Static Value - P

Step 8: To view the data from the emp table, create an interactive report region.

Finally, the report generation screen should look like the attached image.


Step 9: To download the report, create a PL/SQL process.

1) Navigate to Rendering Tree and click on Processing Tab. 
2) Right-click on Processing and click Create Process. 
3) In the Property Editor, apply the following changes:

Under Identification:
     For Name - enter Generate Report
Under Source:
     For PL/SQL Code enter the following code:

-------------------------- ********************* --------------------------

DECLARE
    l_context         apex_exec.t_context; 
    l_print_config    apex_data_export.t_print_config;
    l_export          apex_data_export.t_export;
    l_report_format   apex_data_export.t_format;
BEGIN
    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select * from emp' );

    l_print_config := apex_data_export.get_print_config(
        p_orientation     => apex_data_export.c_orientation_portrait,
        p_border_width    => 2 );
    
    IF :P13_REPORT_FORMAT = 'P' THEN
        l_report_format := apex_data_export.c_format_pdf;
    elsIF :P13_REPORT_FORMAT = 'E' THEN
        l_report_format := apex_data_export.c_format_xlsx;
    elsIF :P13_REPORT_FORMAT = 'C' THEN
        l_report_format := apex_data_export.c_format_csv;
    elsIF :P13_REPORT_FORMAT = 'J' THEN
        l_report_format := apex_data_export.c_format_json;
    elsIF :P13_REPORT_FORMAT = 'X' THEN
        l_report_format := apex_data_export.c_format_xml; 
    elsIF :P13_REPORT_FORMAT = 'H' THEN
        l_report_format := apex_data_export.c_format_html;                               
    END IF;
    
    l_export := apex_data_export.export (
        p_context         => l_context,
        p_print_config    => l_print_config,
        p_format          => l_report_format);

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;

-------------------------- ********************* --------------------------

Under Server-side condition:
     For When Button pressed - select DOWNLOAD

Step 10: Navigate to Rendering Tree and click on Page (No. 13). In the Property Editor, apply the following changes:

Under Advanced
     For Reload on Submit - select Always.

Note: The default behavior for Reload on Submit is Only for success. This will result in the below error when downloading the report.


Step 11: Click Save and Run Page.

Output: Now you can programmatically generate the report by selecting the Report Format and clicking on the Generate Report button.

The demo is here.

References:

Comments

Post a Comment

Popular posts from this blog

Install and Configure OBIEE 12c on Linux VM

-
Image
Install and Configure OBIEE 12c on Linux VM · 1 Today we are going to be installing Oracle BI 12c on our Linux Virtual Machine. If you have not already created an Oracle Linux VM, I wrote a blog on how to do this  here . If you have not already installed Oracle Database 19c on your Linux VM, I wrote a blog on how to do this  here . This blog also assumes that you have access to the sample HR dataset that comes pre-installed with Oracle Database 19c. I covered how to access this in the Oracle 19c installation blog that I mentioned above. Before we begin, I just wanted to say good luck  and … Starting-up and opening the database If you have not already done so, log-in to your Linux instance’s GUI. Your screen should look like this. First, let’s start our listener and open-up our database. Open a new terminal window and execute the following command to start your listener, if it is not already started. If it has already been started, the terminal will let you know. lsnrctl s...
Read more

Project Management System ER Diagram

-
Image
  This article will discuss the step by step process on how to prepare the entity relationship diagram or ERD of the project entitled Project Management System. Project Management System is an information system that will manage the processes and transactions involved in monitoring and recording the updates of a project. The said project can be implemented in an offline settings but in most cases this is implemented in a live server or it simply means an online system. The first step in the development of the Project Management System is to prepare the ER diagram that will serve as the basis later on in the creation of the actual database. We will create and explain the process of making the entity relationship diagram of project management system. Let’s start from the symbols used in the ER Diagram. Entity is represented by the rectangle shape. The entity will be our database table of Project Management System later on. Attribute is represented by the oval shape. This will be the ...
Read more

Web Based Electricity Billing and Payment System with SMS Notification

-
  The proposed electricity billing and payment system is a web based system to be developed using PHP, MySQL and Bootstrap. It is intended to replace the manual process of operation into an automated one. The said project will have two subsystems namely the billing module and the payment module. The billing module is responsible for recording the monthly bills of its customer supported with sms notification to inform their clients about their respective bills in a faster way. Meanwhile the payment module is responsible for recording the payment of its customers, in this module collection report will be included to determine the amount collected and the amount to be collected for a specific period of time. The project will use the iTexMo SMS api for the sms notification part of the project and to make the project more convenient to use the researcher will also develop a mobile app for IOS and Android. Objectives The objectives of the proposed web based electricity billing and paymen...
Read more