This blog was originally published on Nov 6, 2021.

In APEX 20.2, the APEX Development Team included several great features for Report Printing, such as Built-in PDF printing and Excel download for Interactive Reports, Interactive Grids and Classic Reports and new APIs to generate reports programmatically. To learn more, please read Exploring Report Printing in APEX 20.2.

In APEX 21.2, the APEX Development Team keep amazing the community with new features for Reports:

Images in Report Downloads
Image columns are now supported for downloaded reports where they will be embedded in the PDF, XLSX, or HTML export file.

Download Dialog Improvements
The download dialog now provides an option to “Strip Rich Text”, allowing users to enable/disable the property when downloading Interactive Reports or Interactive Grids as CSV or Excel files.
This property is only available when one of the following conditions is met:

  • Column of Rich text type. For both type of format options: Markdown or HTML.
  • Column which has Escape Special Characters turned off

In addition, the PDF accessibility property is now stored as a user preference.

Interactive Report Subscriptions
Interactive Report Subscriptions now support all download formats, no longer send any email if no rows were found.

To explore these new features, let’s import some data from employees.

Install the Dataset

  1. Download the SQL script: Employees.sql
    Note: This script will create a new table: Employees
  2. Navigate to SQL Workshop and click SQL Scripts
  3. Click Upload
  4. Upload the script and click Upload
  5. Click Run
  6. Click Run Now.

You should get 10 Statements Processed and Successful.

Create the App

  1. Navigate to App Builder
  2. Click Create 
  3. Click New Application
         For Name – enter What’s New for Report Regions
  4. Click Add Page
  5. Click Interactive Report
         For Page Name – enter Employees Report
  6. Click SQL Query 
  7. Enter the following SQL Query in the editor:
      
    	select EMPNO,
	ENAME AS NAME,
	JOB,
	SALARY,
	dbms_lob.getlength(IMAGE) AS IMAGE,
	HTML_DETAILS,
	MARKDOWN_DETAILS
	from EMPLOYEES
  8. Click Add Page
  9. Click Create Application

Edit the Interactive Report Page

  1. Click on Page 2-Employees Report
  2. Navigate to Employees Report region
  3. Expand the Columns section and navigate to the IMAGE column
  4. In the Property Editor, apply the following changes:
    Under Identification:
          For Type – select Display Image
    Under BLOB Attributes:
          For Table Name – enter EMPLOYEES
          For BLOB Column – select IMAGE
          For Primary Key Column 1 – EMPNO
  5. In the Rendering Tree, navigate to the HTML_DETAILS column
  6. In the Property Editor, apply the following changes:
    Under Identification:
          For Type – select Rich Text
    Under Settings:
          For Format – select HTML
  7. In the Rendering Tree, navigate to the MARKDOWN_DETAILS column
  8. In the Property Editor, apply the following changes:
    Under Identification:
          For Type – select Rich Text
    Under Settings:
          For Format – select Markdown
  9. Click Save and Run the page.

Run the page

  1. Enter your credentials
  2. Navigate to Actions and click Download
  3. Select the report format (PDF, Excel, HTML) and click Download.

This is the Excel report when enabling the Strip Rich Text property:

To highlight:

  • Employee images will be embedded in the PDF, XLSX, and HTML reports.
  • The Strip Rich Text property is only available for CSV and Excel reports.

Generating the report programmatically

In case that you prefer to generate the report programmatically, you can use APEX_DATA_EXPORT to include the image column. Let’s explore that option by creating a button on page 2.

  1. Navigate to Page Designer – Page 2.
  2. Navigate to Employees Report Region and right-click Create Button
  3. In the Property Editor, apply the following changes:
    Under Identification:
         For Button Name – enter Download
         For Label – enter Download Report
    Under Layout:
         For Position – select Right of Interactive Report Search Bar
    Under Appearance:
         For Button Template – select Text with Icon
         Enable Hot button
         For Icon – enter fa-download
  4. Navigate to Rendering Tree and click on Processing Tab
  5. Right-click on Processing and click Create Process
  6. 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_columns               apex_data_export.t_columns;

BEGIN
    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => '  select EMPNO,
                            ENAME AS NAME,
                            JOB,
                            SALARY,
                            dbms_lob.getlength(IMAGE) AS image
                            from EMPLOYEES' );

    apex_data_export.add_column(
        p_columns             => l_columns,
        p_name                => 'NAME',
        p_heading             => 'Name' );

    apex_data_export.add_column(
        p_columns             => l_columns,
        p_name                => 'JOB',
        p_heading             => 'Job' );

    apex_data_export.add_column(
        p_columns             => l_columns,
        p_name                => 'SALARY',
        p_heading             => 'Salary' );                

    apex_data_export.add_column(
        p_columns             => l_columns,
        p_name                => 'IMAGE',
        p_heading             => 'Image',
        p_format_mask         => 'IMAGE:EMPLOYEES:IMAGE:EMPNO' );                     

  l_print_config := apex_data_export.get_print_config( p_body_font_color => '#4B4540',
  p_page_header => 'Report of Employees', 
  p_page_header_font_color => '#4B4540', 
  p_page_header_font_size => 14, 
  p_page_header_font_weight => apex_data_export.c_font_weight_bold, 
  p_page_footer => 'Generated by '|| :APP_USER, 
  p_page_footer_font_color => '#4B4540', 
  p_page_footer_font_size => 10,
  p_page_footer_font_weight => apex_data_export.c_font_weight_bold,
  p_border_width => 1, p_border_color => '#4B4540'); 
       

    l_export := apex_data_export.export (
        p_context               => l_context,
        p_format                => apex_data_export.c_format_pdf,
        p_print_config => l_print_config,
        p_columns               => l_columns,
        p_supplemental_text     => '',
        p_file_name             => 'Employees' );

    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
  7. Navigate to Rendering Tree and click on Page 2
  8. Under Advanced
         For Reload on Submit – select Always.
  9. Click Save and Run Page.

Interactive Report Subscriptions

You provide an email address (or multiple email addresses, separated by commas), email subject, frequency, and start and end dates when you add a subscription. The resulting emails include an CSV, HTML, Excel and PDF  version of the interactive report containing the current data using the report setting that was present when the subscription was added. Also, you can define if you prefer to skip sending the email if there’s no data found.

To subscribe for the Employees report, follow these steps:

  1. Run Page 2.
  2. Navigate to Actions and click Subscription
  3. Choose the report format
  4. Provide the email address, subject and frequency
  5. Enable or Disable the report if there’s no data found
  6. Define the Starting and Ending Dates
  7. Click Apply.

You will get the email depending on the frequency and when the report is started.

Test right now these new enhancements and other new features in APEX 21.2 on apex.oracle.com or download this release from apex.oracle.com/otn/.