Skip to main content

APEX Features

Oracle APEX offers various regions for displaying data in effective ways, including Interactive Reports, Interactive Grids, Classic Reports and Oracle Charts. AOP simplifies and streamlines the process of exporting data from APEX to Word, Excel, PDF and other document types.

To gain a deeper understanding and explore practical examples, you can refer to APEX Features section of the APEX Office Print Sample Application, which provides a comprehensive demonstration of how to export data from various reports, charts, calender and many more to AOP.

Interactive Reports(IR)​

Available From: v2.0

In an APEX application, users have the option to export an interactive report using the {&interactive} tag in a designated template. This tag will be substituted with the selected interactive report in APEX. Apart from the standard interactive report, users can also choose from three other options: Control Break, Group By, and Pivot. These options will be demonstrated below. It is important to note that the tag {&interactive} will always be replaced, no matter which of the above options is selected in APEX.

StaticID and Data Type

When exporting Interactive Reports, it's important to include the staticID and specify the data type as Region(s):Classic Report, Interactive Report/Grid, SVG, Canvas, HTML, and others.

Layout for IR

Some aop_api_pkg global variables available for IR Layout are available here: Layout for IR.

Example​

The interactive report with static Id row is as shown below.

Data Source​

The dynamic action settings to export the IR is as shown below. The data type is Region(s):Classic Report, Interactive Report/Grid, SVG, Canvas, HTML and other. You also need to provide the unique staticId for the IR Region.

Template​

The template uses the {&interactive} to process the exported IR. The {&interactive} is replaced by the IR as shown in application.

 IR-template.docx    IR-template.xlsx  

Output​

The output with given template and IR export with staticID row is as shown below:

 IR-Output.docx    IR-Output.xlsx  

Control Break​

By clicking on Actions and selecting Control Break, one or more columns can be selected on which the table should be broken. For the purpose of illustration, assume we select Quantity.

This splits the table up into partitions based on the value of the selected column. Thus, rows sharing the same value for Quantity, will be grouped together into the same partition. The resulting table can be seen below.

As a final note, this option also supports the application of aggregation of the table data.

Group By​

By clicking on Actions and selecting Group By, one or more columns can be selected on which the table data should be grouped by. For the purpose of illustration, assume we select Product Name. Furthermore, one or more functions can be applied on the table data. In this example, we request the sum of the unit price and average of Quantity for every group.

The result is a new table with one column for every selected column on which the data should be grouped by, and one column for every selected function to be applied on the group data.

The resulting table can be seen below:

Additionally, a custom label and format may be selected for each function column and a sum can be performed over the values of each function column.

Pivot​

By clicking on Actions and selecting Pivot, the user is prompted to select at least one pivot column, at least one row column and at least one function over a particular column. Important to note is that the row column, pivot column and function column need to be different. For the purpose of illustration, we select Quantity as pivot column, Product Name as row column and sum over the Unit Price as function.

The resulting table can be seen below:

The additional options supported in this case are the same as the ones in Group By.

Chart​

AOP can also print your Interactive Report Chart view directly in your template by using {$interactive}D

The resultant chart from the above setup is as follows:

The result in PowerPoint can be seen below:

Note that this is a native PowerPoint chart, so you can adapt, make bigger, change colors, etc. directly in PowerPoint.

More

Examples for IR Features like Compute and Aggregate, Filter and Highlight, Multiple Highlights, Number and Date Format, Multiple Reports and orthers important functionalities are available in AOP Sample Application with practical implementation.

Width Manipulation​

Available From: v18.2.2

Since 18.2.2 it is possible to manipulate the widths of the interactive columns. You can do so by specifying the width in an HTML_EXPRESSION. <span data-aop-width-weight="2"></span>#COLUMN_NAME#
The default weight for each column is 1. Let's say you have 4 columns and you provide this html expression in the first column. This will double the size of the first column in comparison to the remaining 3.

The following formula is used to calculate the percentage of width a column gets:

(weight of column)/(total weight provided for all the columns) * 100. Please note that these widths are the desired widths. If the content does not fit, it could be that the minimum width that fits will be taken by Word/LibreOffice.

Since AOP 19.3, it is possible to specify the total width in Word instead of taking the full page width. You can do so by putting the following line of code in the custom attribute section of the region.

data-aop-report-width="26cm" (can be in px, %, pt, in, cm, mm...)

HTML Expression​

In IR, HTML expression can be used to manipulate the different properties of data cells like width, line height, alignment and others. You can insert HTML expressions in the column of interactive report column.

Common Options​

The common options for IR in both excel and word are:

Ignore element in HTML Expression: Since AOP 23.1, it is possible to ignore the element of HTML Expression in the output by providing the aop-data-ignore=true as an attribute.

For Example: Following HTML Expression is provided for the column Last Name: 

<span style="color:#00AA00">#CUST_LAST_NAME#</span>
<span style="color:red" data-aop-ignore="true">(Ignore Text)</span>

(Ignore Text) is visible in the APEX Application but when AOP process the IR with the given HTML Expression for the column then, it ignores the element with the data-aop-ignore and renders other elements only in the output.

Header Vertical Alignment: Since AOP 23.1, it is possible to specify the vertical alignment(top, center, bottom) for specific header column, You can do so by specifying the data in an HTML_EXPRESSION.
<span data-aop-heading-vertical-alignment="center" ></span>#COLUMN_NAME#
Alternative

In order to apply heading alignment for entire header data at once, the PL/SQL is: 

aop_api_pkg.g_rpt_header_vertical_alignment := "top"

The value can be top, center, and bottom.

Data Vertical Alignment: Since AOP 23.1, it is possible to specify the vertical alignment(top, center, bottom) for specific data column, You can do so by specifying the data in an HTML_EXPRESSION.
<span data-aop-column-vertical-alignment="top" ></span>#COLUMN_NAME#
Alternative

In order to apply heading alignment for entire data except for header data only at once, the PL/SQL is: 

aop_api_pkg.g_rpt_data_vertical_alignment := "center"

The value can be top, center, and bottom.

Width Manipulation: It is possible to manipulate the widths of the interactive columns. You can do so by specifying the width in an HTML_EXPRESSION.
<span data-aop-width="20pt"></span>#COLUMN_NAME#
Break Tags: Since AOP 22.1 it is possible to add line breaks using <br /> tag in the header of a column in Interactive reports. For example,
"REPORT_LABEL": "Unit<br /> Price"

would result in Units and Prices rendered in different lines.

Exclusive to Excel

There are a few exclusive options for interactive reports available for Excel. The available options are:

Wrap Text: Text Wrapping can be enabled using HTML Expression, It can be done using property
<span data-aop-wrap-text="true"></span> #COLUMN_NAME#
Line Height, Character Per Line, and Maximum Number Of Lines: Since AOP 22.1, it is possible to specify line height, character per line, and maximin number of lines in a cell of the particular column, You can do so by specifying the data in an HTML_EXPRESSION.
<span data-aop-line-height="13pt" data-aop-max-line="4" data-aop-character-per-line="30"></span>#COLUMN_NAME#

The above example would result in each cell in a column consisting of a maximum of 30 characters per line, with line height being 13 pt, and only 4 lines displayed on the cell.

Freeze Pane

It is possible to freeze columns, rows or both rows and columns using HTML expression.

  • Freeze Row(s) : Freeze row(s) in Excel using data-aop-freeze-row as:

    <span data-aop-freeze-row="true"></span> #COLUMN_NAME#

    Using above html expression, you can freeze row containing column names. All the rows above will also be freezed.

  • Freeze Column(s) : Freeze column(s) in Excel using data-aop-freeze-column as:

    <span data-aop-freeze-column="true"></span> #COLUMN_NAME#

    Using above html expression, you can freeze column(s). All the columns to the left will also be freezed.

  • Freeze row(s) and column(s) : Freeze both rows and column at a specific cell of Excel as data-aop-freeze as:

    <span data-aop-freeze="true"></span> #COLUMN_NAME#

    Using above expression, you can freeze both rows and columns at a certain cell. All the row(s) above and column(s) to the left will be freezed.

The above example would result in freezing the corresponding column. For more reference: Freeze Pane

caution

Note: For accurate resulting width and height, please use points(pt) or Excel Units(eu) instead of pixels(px), due to units of measurements used in Excel points are exactly rendered while pixels are approximately rendered.

Multiple Interactive Reports in one template​

When exporting multiple interactive reports, the template needs to have tags of the form {&interactive_1},{&interactive_2} and these tags will be replaced by the corresponding interactive reports. Many interactive reports can be inserted by giving their static IDs in a particular order (ir1,ir2,...). It is in that order that their data will be inserted in the template. Thus, {&interactive_1} gets replaced by the data in the first interactive report (static Id: ir1) and similarly for {&interactive_2} that will get replaced by the data in the second interactive report (static Id: ir2) and so on.

Example​

This examples shows the export of multiple interactive reports with static Ids ir1, ir2 and ir3.

The IR with static ID ir1 has following contents.

The IR with static Id ir2 has used control break for order name and order total column. (Shows the export of IR with control break)

The IR with static Id ir3 is as shown below. The ir3 is converted to chart before exporting to get its chart in output. (Shows the export of IR as chart)

Data Source​

The dynamic action settings to export the IG has the data type Region(s):Classic Report, Interactive Report/Grid, SVG, Canvas, HTML and other selected and unique staticIds ir1,ir2,ir3 are provided in Region Static ID(s) field (seperated by comma).

Template​

 multiple-IR-template.docx    multiple-IR-template.xlsx  

{?!pagebreak}

{?!pagebreak} inserts the page break in word template. For more information how pagebreak tag works, click here.

Output​

On processing the template and the given output, AOP produces the output with multiple IRs as shown below.

 multiple-IR-output.docx    multiple-IR-output.xlsx  

Interactive Grid(IG)​

Using the interactive grid tag {&...&}, user can get the exported IG in the designated template. The tag consist of interactive grid static id, with & on both sides enclosed by the delimiters.
Like the Interactive reports, it also supports Highlights, Filters, Aggregates, Break, Charts and many more. For more detail on its uses, please refer to AOP Sample Application > APEX Features > Interactive Grid.

HTML Expression​

In IG, HTML expression can be used to manipulate the different properties of data cells like width, line height, alignment and others. You can insert HTML expressions in the column of interactive grid column. The below-mentioned options are available on both docx and xlsx templates.

Ignore element in HTML Expression: Since AOP 23.1, it is possible to ignore the element of HTML Expression in the output by providing the aop-data-ignore=true as an attribute.

For Example: Following HTML Expression is provided for the column Last Name: 

<span style="color:#00AA00">&CUST_LAST_NAME.</span>
<span style="color:red" data-aop-ignore="true">(Ignore Text)</span>

(Ignore Text) is visible in the APEX Application but when AOP process the IR with the given HTML Expression for the column then, it ignores the element with the data-aop-ignore and renders other elements only in the output.

Header Vertical Alignment: Since AOP 23.1, it is possible to specify the vertical alignment(top, center, bottom) for specific header column, You can do so by specifying the data in an HTML_EXPRESSION.
<span data-aop-heading-vertical-alignment="center" ></span>&COLUMN_NAME.
Alternative

In order to apply heading alignment for entire header data at once, the PL/SQL is: 

aop_api_pkg.g_rpt_header_vertical_alignment := "top"

The value can be top, center, and bottom.

Data Vertical Alignment: Since AOP 23.1, it is possible to specify the vertical alignment(top, center, bottom) for specific data column, You can do so by specifying the data in an HTML_EXPRESSION.
<span data-aop-column-vertical-alignment="top" ></span>&COLUMN_NAME.
Alternative

In order to apply heading alignment for entire data except for header data only at once, the PL/SQL is: 

aop_api_pkg.g_rpt_data_vertical_alignment := "center"

The value can be top, center, and bottom.

It is possible to freeze columns, rows or both rows and columns using HTML expression.

  • Freeze Row(s) : Freeze row(s) in Excel using data-aop-freeze-row as:

    <span data-aop-freeze-row="true"></span> #COLUMN_NAME#

    Using above html expression, you can freeze row containing column names. All the rows above will also be freezed.

  • Freeze Column(s) : Freeze column(s) in Excel using data-aop-freeze-column as:

    <span data-aop-freeze-column="true"></span> #COLUMN_NAME#

    Using above html expression, you can freeze column(s). All the columns to the left will also be freezed.

  • Freeze row(s) and column(s) : Freeze both rows and column at a specific cell of Excel as data-aop-freeze as:

    <span data-aop-freeze="true"></span> #COLUMN_NAME#

    Using above expression, you can freeze both rows and columns at a certain cell. All the row(s) above and column(s) to the left will be freezed.

The above example would result in freezing the corresponding column. For more reference: Freeze Pane

Example​

Here, Interactive Grids with static id igrid1 and igrid2 are used to export to AOP.

The interactive grid with staticId igrid1 is as shown below.

The interactive grid with staticId igrid2 is as shown below.

Data Source​

The dynamic action settings to export the IG is as shown below. The data type is Region(s):Classic Report, Interactive Report/Grid, SVG, Canvas, HTML and other and unique staticIds igrid1,igrid2 are provided in Region Static ID(s) field.

Template​

The template consists of tags {&igrid1&} and {&igrid2&} for the two interactive grid as shown below:

 igrids-template.docx    igrids-template.xlsx  

Output​

Upon processing, AOP produces the following output which includes the interactive grid shown in APEX.

 igrids-output.docx    igrids-output.xlsx  

Classic Report​

AOP also supports the data exported of classic reports. The tag is similar to interactive grid tag where static id, with & on both sides enclosed by the delimiters like {&crStaticID&}. Upon processing, the tag is replaced by the data from the classic report. A simple example to demonstrate the use of classic report tag is as shown below.

Example​

Hearby, the classic reports with static ID products.

Classic reports with static ID customers.

Data Source​

The dynamic action settings consist of the data type Region(s):Classic Report, Interactive Report/Grid, SVG, Canvas, HTML and other and unique staticIds products,customers are provided in Region Static ID(s) field.

Template​

The template consist of {&products&}, {&customers&} and a table with loop tag (to show the possibility of using staticID in loop tag to insert CR data in used defined table) as shown below.

 classic-reports-template.docx    classic-reports-template.xlsx  

Output​

Upon processing with the given data source and template, AOP produces the following output.

 classic-reports-output.docx    classic-reports-output.xlsx  

Calendar​

AOP also supports the export of APEX Calendar. The tag used in the template should be ?CALENDAR followed by static id of calendar on APEX, enclosed by delimiters eg. {?CALENDAR cal} .

Settings for Calendar

Some aop_api_pkg global variables available for the settings of calendar are available here: Settings for Calendar.

Example​

The example shows the export of calendar of static id cal which is shown below.

Data Source​

In the dynamic action, the static id for calendar cal is provided and Data type selected is Region(s):Classic Report, Interactive Report/Grid, SVG, Canvas, HTML, and others . Also in Init PL/SQL aop_api_pkg.g_separate_pages := 'true'; to get separate pages for each months.

Template​

The template consists the calendar tag {?CALENDAR cal} as shown in the image below.

 template.docx  

Output​

Upon processing, AOP provides the output with following contents.

 output.docx    output.pdf  

More on AOP Sample Application > APEX Features

In addition to Interactive Reports (IR), Interactive Grids (IG), and Classic Reports, AOP supports the export of various other regions, including charts, calendars, and many more. The AOP Sample Application provides practical examples that demonstrate the insertion of these regions in a meaningful way. By exploring the AOP Sample Application, you can gain hands-on experience and a better understanding of how to utilize AOP for exporting charts, calendars, and other supported regions.

Oracle Charts​

Available From: v24.1

AOP enables the direct export of Oracle Charts to native office Charts. This functionality is achieved by using the staticID of the chart region, similar to how we export Interactive Grids (IG), Interactive Reports (IR), and Classic Reports (CR).

The charts types that are currently supported are as follows:

  • Area
  • Bar
  • Bubble
  • Combination
  • Pie and Donut
  • Line
  • Line with Area
  • Radar
  • Scatter
  • Stock

The unsupported chart types are :

  • Box Plot
  • Status Meter Gauge
  • Funnel
  • Gantt
  • Polar
  • Pyramid
  • Range

Example​

The following example shows the export of Bar Chart with a vertical orientation.

Data Source​

The dynamic action settings to export the chart is as shown below. The data type is Region(s):Classic Report, Interactive Report/Grid, SVG, Canvas, HTML and other. You also need to provide the unique staticId for the Chart Region.

Template​

The template consists the chart tag {$column}, where column is staticID of chart region and $ means it is a chart tag enclosed by the delimiters {..}.

Output​

Upon processing the template and the chart data, AOP generates the following output.