X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Exploring Report Printing in APEX 20.2

Monica Godoy
Principal Product Manager

APEX 20.1 included significant new enhancements for Report Printing, such as PDF printing supported for Interactive Grids. For APEX 20.2, the APEX Development Team has included even more features that you can test right now on apex.oracle.com or download this release from apex.oracle.com/otn/.

The new enhancements are:

  • Built-in PDF printing and Excel download for Interactive Reports, Interactive Grids and Classic Reports. Make sure to enable PDF and Excel as an additional download format in your region attributes. Users can change the page orientation and size in the download dialog.
  • Interactive Report - Send E-Mail: All download formats can now be attached.
  • Enhanced integration with BI Publisher.
  • New APEX_REGION.EXPORT_DATA and APEX_DATA_EXPORT APIs to programmatically generate PDF, CSV, Excel, HTML, JSON and XML files.
  • Built-in PDF now supports Chinese, Japanese and Korean languages.

To explore them, I'm going to utilize the EMP / DEPT Sample Dataset in Japanese. So, the first step is to install the Sample Dataset:

  1. Navigate to SQL Workshop and click Utilities.
  2. Click Sample Datasets
  3. Install EMP / DEPT.
  4. For language, select Japanese.
  5. Click Next.
  6. Click Install Dataset.
  7. Click Create Application.
  8. Add the following pages in your application:
    1. Classic Report
    2. Interactive Grid
    3. Interactive Report
    4. Faceted Search
  9. Click Create Application

Customize the Application

Let's customize each page of the application, in order to review the new enhancements.

Built-in PDF and Excel Download

Start customizing the Classic Report to add the new Built-in Excel download to this report. Follow these steps:

  1. Navigate to the Classic Report Region
  2. Navigate to attributes and enable report printing.
  3. Navigate to the Printing region.
  4. For Format, select PDF.
  5. Apply Changes.

When you run the application, you're going to find the Print option at the bottom of the report.

Clicking on that option, the application will download the Employees Report in PDF format.



Just changing the format attribute to Excel, users can download the same report in Excel format, but keep in mind that format can be set dynamically using the Derived from Item attribute.
 

Employees Report exported in Excel


The previous example was a plain report. If you want to learn how to print a report with additional formattings, such as highlights, control breaks, or aggregated columns as in the screenshot below, keep reading!


Let's explore the new way to export your Interactive Reports and Interactive Grids:

Users can choose three options:

  • Report format: Available options are CSV, HTML, Excel and PDF.
  • Data Only: Users can choose between downloading the plain report and downloading the report with the format defined in the APEX Application.
  • Send as Email: Users can send the report by Email.

When users download the report in Excel, some cells could be frozen. In this example, the heading of the columns (row) and the sum (column) are frozen so when users scroll down or over to view the rest of the sheet, the first column and/or top row remains on the screen.
 

Programmatic and Customizable Reports

You can implement the new APIs to programmatically generate reports in multiple formats, even when the page doesn't have a report region. In the next sample, you might enable report printing for Faceted Search page (remember that Faceted Search includes a Classic Report Region) to export the data. Instead of that, let's explore how to implement a custom Download Report button, following these steps:

  1. Navigate to Faceted Search Page.
  2. Create a Select List Item to allow users to select their preferred report format. In my case, the item is called P3_FORMAT.  Include the following static values:
  3. Create a button to download the report.
  4. Navigate to Faceted Search and for Static ID, enter faceted_search.
  5. Create a Before Header process to execute the following code when users click on Download button and when the format item (P3_FORMAT) is not null.
    This process exports the information depending on the SQL query you have in the specified region.
    Note: Replace the item name depending on your number page.
    declare
        l_export       apex_data_export.t_export;
        l_region_id    number;
    begin
       select region_id into l_region_id
         from apex_application_page_regions
        where application_id = :APP_ID
          and page_id = :APP_PAGE_ID
          and static_id = 'faceted_search';
        
        l_export := apex_region.export_data (
             p_format       => :P3_FORMAT,
             p_page_id      => :APP_PAGE_ID,
             p_region_id    => l_region_id);
             
        apex_data_export.download( l_export );
    end;
  6. Apply Changes.

Users can easily download the report by selecting the format report they need:

If there is no SQL query on the page, you can define your own SQL code and include different formats to your reports, such as column groups, highlights, aggregates, and other additional print configurations.
To generate this type of report, change the process code in the Faceted Search page to the code below which defines:

  • SQL query for the report
  • Configuration and condition for the row highlight
  • Print configuration for the header, footer and border table.
declare
    l_highlights     apex_data_export.t_highlights;
    l_context        apex_exec.t_context;
    l_export         apex_data_export.t_export;
    l_print_config    apex_data_export.t_print_config;
begin
    apex_data_export.add_highlight(
        p_highlights          => l_highlights,
        p_id                  => 1,
        p_value_column        => 'HIGHLIGHT1',
        p_display_column      => null,
        p_text_color          => '#FFFF',
        p_background_color    =>  '#F5B642');

    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select ename as "Name - 従業員名",
                                job as "Job - ジョブ",
                                MGR as "Manager - マネージャー" ,
                                Hiredate as "Hiredate - 採用日",
                                sal as "Salary - 給与",
                                case when sal >= 3000 then 1 end as HIGHLIGHT1,
                                Comm as "Commision - コミッション"
                            from emp' );

l_print_config := apex_data_export.get_print_config(
        p_body_font_color             => '#4B4540',
        p_page_header                 => 'Header - ヘッダー',
        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                 => 'Footer - フッター',
        p_page_footer_font_color      => '#4B4540',
        p_page_footer_font_size       => 14,   
        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       => :P3_FORMAT,
                        p_print_config => l_print_config,
                        p_highlights   => l_highlights );

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );

exception
    when others then
        apex_exec.close( l_context );
        raise;
end;


And below is the generated report:

Summary

The new enhancements for Report Printing provide many options for your reports, and both you and your users will greatly benefit.

Join the discussion

Comments ( 3 )
  • ahmed Tuesday, October 27, 2020
    but this issue still not solved(Arabic characters )
    9.1.11 Language Support for Native PDF Printing in Interactive Grid
    When you print unicode characters from scripts other than Latin and Cyrillic, those characters are substituted with a missing character (□) in the PDF document.
    There is no resolution at this time.
  • Mónica Tuesday, November 3, 2020
    Hi Ahmed,

    20.2 supports Chinese, Japanese and Korean languages. Arabic is planned for next future releases.

    Regards.
  • Luis Medina Thursday, November 19, 2020
    Excellent. Opens up a lot of possibilities
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.