Producing Pixel-Perfect Reports

Use a Report Query with an associated Report Layout to produce a pixel-perfect PDF report.

The report sources in the Report Query definition identify the data the report requires, while the Report Layout document template formats it appropriately.

The APEX engine sends the layout file and the data in JSON format to a remote printing service. The report server combines the layout with the data and returns a formatted report. Your Report Query definition can include multiple data sources and your layout can use any formatting the template document allows. The result is a flexible system to produce whatever pixel-perfect reports your application may need.

• Setting Your App's Remote Print Server
  Use instance print server settings to generate reports with the configured remote printing service.
• Printing Report with an Excel Template
  Generate a PDF report by merging application data into an Excel template.
• Customizing Reports with Word Template
  Use a Report Layout to customize the PDF download format for an Interactive Report or Classic Report region.

Use instance print server settings to generate reports with the configured remote printing service.

While you can choose from multiple remote printing solutions, here you explore using Oracle Cloud Infrastructure Document Generator Function. It defines a set of "tags" you use to reference the data inside your layout document template. In this section's examples, you encounter a few simple tags to include data. Consult the Document Generator's reference guide for a complete tag list.

The only setup step required in your own apps is to set the Print Server Type as shown below to Use Instance Settings. This is the simplest path to pixel-perfect printing.

Your APEX instance administrator should have already setup a remote print server for use by all workspaces. When they setup the instance to use Oracle Document Generator as the Print Server, as shown below, they configure the details that let APEX automate reporting for your applications. Details include a database cloud credential for an OCI user with access to an object bucket and the Oracle Document Generator Function. They also provide OCIDs and URLs to access them. However, once they have setup the remote print server, all app developers can use it without being aware of these details. The figure shows the APEX instance administrator's view of the Report Printing tab of the Instance Settings page.

Generate a PDF report by merging application data into an Excel template.

HR Representatives at Woods Clinic asked for an easy way to generate an employee tenure report by department.

• Formatting Data in Excel with Tags
  Format an Excel report template with tags that map data sources and fields into the layout.
• Creating Layout for Document Template
  Create a Report Layout shared component to hold the document template.
• Defining Report Query Sources
  Create a Report Query definition to supply the report data.
• Assigning Sources a Data Loop Name
  Set each report source’s Data Loop Name to match the short name used in the template tags.
• Using SQL Features in Report Sources
  Use SQL features and custom functions in report sources to shape data for a report.
• Inspecting the Report Query JSON
  Inspect the generated Report Query JSON to see how data loop names and source rows are structured.
• Printing Report from a Page
  Print and download a report from a page using a button, application item, and Print Report process.
• Experiencing Report Generation as a User
  Follow the user flow from choosing report data to downloading the generated PDF.

Use a Report Layout to customize the PDF download format for an Interactive Report or Classic Report region.

• Formatting Data in Word with Tags
  Format a Word report template with tags that map a report region’s data into the layout.
• Setting Layout Data Loop Name
  Set the layout’s Data Loop Name to match the short name used in the report template.
• Overriding PDF Layout of Report Regions
  Override a report region’s default PDF output by assigning a custom Report Layout.
• Downloading Customized Report PDF
  Download a customized PDF that reflects the Interactive Report’s current filters and sort order.

Format an Excel report template with tags that map data sources and fields into the layout.

Use Microsoft Word or Excel to create a template layout for the report. For this one, you choose a spreadsheet. When thinking of the data your report requires, consider its source and the fields from it you need to incorporate. Assign each data source a short name.

In your document template, reference the short name of your data source using Document Generator tags {#shortname} and {/shortname} to surround {fieldname} tags that reference the data source field values by name.

{#dep}Department {dname} ({deptno}) in {loc}{/dep}

In a similar way, under the Employees by Tenure heading, it references the fields of the Employee data source ordered by tenure using the following tags. A vertical bar below represents a cell boundary. Document Generator repeats the row of cells between {#emp_t} and {/emp_t} for each row in the data source. When data source row repetition produces new spreadsheet rows, it's known as horizontal repetition of cells.

{#emp_t}{ename}({empno})  |  {tenure}  |  {job}{/emp_t}

Under the Employees by Name heading, it uses slightly different tags to repeat the Employees ordered by name vertically instead. It references the fields of the Employee data source ordered by name using the following tags. A horizontal bar represents a cell boundary. Document Generator repeats the column of cells between {:emp_n} and {/emp_n} for each row in the data source. When data source row repetition produces new spreadsheet columns, it's known as vertical repetition of cells.

{:emp_n}{ename}
—
{hiredate}
—
{job}{/emp_n}

The figure shows the Woods Clinic Tenure Report in Excel, with a graphic featuring two seasoned physicians in the upper left corner, the report title to its right, and Document Generator tags and text in the cells below it to format department and employee information.

Create a Report Layout shared component to hold the document template.

As shown below, you give it a name like Tenure Report and assign a Static ID used when calling the GENERATE_DOCUMENT function in the APEX_PRINT package to produce a PDF report in your business logic. To add the woods_clinic_tenure_report.xlsx spreadsheet, either drag and drop it onto the File drop zone, or click the area and pick it from the system file dialog.

Create a Report Query definition to supply the report data.

As shown below, this Department Employee Tenure definition includes three report sources and references the Tenure Report layout. Each source you include can be from any supported data source type including local or remote queries, tables, and views, REST Data Sources, and more. Here all three sources use a local database SQL query.

For example, the Department source uses the SQL query below that references the G_DEPTNO_FOR_REPORT application item as a bind variable.

select deptno, dname, loc
  from dept
 where deptno = :G_DEPTNO_FOR_REPORT

Notice the (Set Bind Values) button. It opens a dialog in which you can set example values for any bind variables your report source queries might reference. These example values are used when you click the (Download) button to produce a sample JSON file representing the data for your report. If you click (Test Report) the same JSON file is sent along with the layout to the remote print server so you can validate your report output.

Set each report source’s Data Loop Name to match the short name used in the template tags.

{#dep}Department {dname} ({deptno}) in {loc}{/dep}

Use SQL features and custom functions in report sources to shape data for a report.

Your report source queries can use any SQL functionality, including custom function calls, to retrieve the data your report needs. The Employees in Department (by Tenure) report source with data loop name emp_t has the following query. It calls the TENURE_YEARS_MONTHS function in the EBA_DEMO_WOODSHR_REPORT package to format the employee's tenure as a phrase like "1 year, 5 months".

select empno,
       ename,
       job,
       eba_demo_woodshr_report.tenure_years_months(hiredate) as tenure
  from emp
 where deptno = :G_DEPTNO_FOR_REPORT
 order by hiredate

The source for this helper function appears below.

-- In package eba_demo_woodshr_report
function tenure_years_months (p_date in date)
  return varchar2
is
  l_months      number;
  l_years       number;
  l_rem_months  number;
begin
  if p_date is null then
    return null;
  end if;

  -- Whole months completed between the dates
  l_months := floor(months_between(trunc(sysdate), trunc(p_date)));

  -- If p_date is in the future, clamp to 0
  if l_months < 0 then
    l_months := 0;
  end if;

  l_years      := trunc(l_months / 12);
  l_rem_months := mod(l_months, 12);

  return  l_years || ' ' ||
          case when l_years = 1 then 'year' else 'years' end || ', ' ||
          l_rem_months || ' ' ||
          case when l_rem_months = 1 then 'month' else 'months' end;
end;

Inspect the generated Report Query JSON to see how data loop names and source rows are structured.

The (Download) button in the Report Query definition page lets you inspect the contents of the JSON file your report uses .It has the following format (edited slightly for brevity). Each Data Loop Name is a top-level JSON object property whose value is an array of JSON objects representing the source's rows. Lowercase JSON property names match the names of each report source column.

{
    "dep": [
        {
            "deptno": 20,
            "dname": "RESEARCH",
            "loc": "DALLAS"
        }
    ],
    "emp_t": [
        {
            "empno": 7566,
            "ename": "JONES",
            "job": "MANAGER",
            "tenure": "44 years, 6 months"
        },
        ⋮
        {
            "empno": 7788,
            "ename": "SCOTT",
            "job": "ANALYST",
            "tenure": "1 year, 2 months"
        }
    ],
    "emp_n": [
        {
            "empno": 7876,
            "ename": "ADAMS",
            "job": "CLERK",
            "hiredate": "12-JAN-1983"
        },
        ⋮
        {
            "empno": 7788,
            "ename": "SCOTT",
            "job": "ANALYST",
            "hiredate": "14-JUL-2024"
        }
    ]
}

Print and download a report from a page using a button, application item, and Print Report process.

To print the report from a page, just configure a button to trigger its generation and download. The PRINT button below appears in the same row as the P35_DEPTNO select list HR Reps use to choose the desired department.

When an HR Rep selects a department and clicks the (Print and Download Report) button, it submits the page. As shown below, an After Submit computation sets the value of the G_DEPTNO_FOR_REPORT application item to the value of the P35_DEPTNO select list containing the user's selected department number. This ensures the report source queries have the right value when they reference this application item as a bind variable.

With the application item value assigned, the Print Report page process generates and downloads the pixel-perfect PDF using the native Print Report process. It configures its Report Query property to use the Department Employee Tenure definition. Notice the Filename uses a substitution &P35_DEPTNO. as part of the PDF file name to download. A similar native Print Report dynamic action is also available. It does the same job, just triggered from a page event instead of a page submit.

Follow the user flow from choosing report data to downloading the generated PDF.

Susan visits the Tenure Report page visible only to HR Representatives, selects the RESEARCH department in the list, and clicks (Print and Download Report).

Susan opens the PDF to view the finished report shown below.

The figure shows the PDF Woods Clinic Tenure Report for the RESEARCH department that Susan requested. It includes an Employees by Tenure section with employees listed in a tabular style, one per row. It also includes an Employees by Name section, with employee information one per column across the page.

Format a Word report template with tags that map a report region’s data into the layout.

Use Microsoft Word or Excel to customize the PDF download for the Interactive Report on the Employee Directory page from Shaping Experience with Rules and Roles. For this report, you choose a Word document.

Here, you use Oracle Document Generator tags in a Microsoft Word layout document template, just as you did in an Excel Spreadsheet. For the Employee Directory layout, you need data like the following, to which you associate a convenient short name:

• Employee directory info (short name dir)
  • with fields: ename, job, mgr, deptno

In your document template, reference the short name of your data source using Document Generator tags {#shortname} and {/shortname} to surround {fieldname} tags that reference the data source field values by name.

As shown below, the Word document includes a formatted table, with a single row containing the four fields of the associated region's data source. It references the dir short name and field names using the following tags. The vertical bars below represent table cell boundaries.

{#dir}{ename}   |   {job}   |   {mgr}   |   {deptno}{/dir}

Set the layout’s Data Loop Name to match the short name used in the report template.

While each Report Query source specifies its own Data Loop Name, when pairing a layout with a report region, set its short name in the layout definition itself. As shown below, the Employee Directory layout specifies dir as its Data Loop Name to match the tags used in the template. It also includes the emp_directory.docx Word document as the template file.

Override a report region’s default PDF output by assigning a custom Report Layout.

In the Employee Directory page, select the Interactive Report region and the Printing tab in the Property Editor. There, as shown below, you can select the Employee Directory layout to override the default report PDF formatting.

Download a customized PDF that reflects the Interactive Report’s current filters and sort order.

Now when a user chooses Download from the Interactive Report's Actions menu, if they choose the PDF report format they get a pixel-perfect Employee Directory. Notice below that the user uses features like filtering on the Job column and sorting on the Deptno column before performing the PDF download. This affects which rows appear and how they are sorted in the downloaded report, too.

When the user opens the PDF file, they see the filtered, sorted Employee Directory contents presented now in your pixel-perfect layout.