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.0In 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.
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.
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:
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.
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.2Since 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.
<span data-aop-heading-vertical-alignment="center" ></span>#COLUMN_NAME#
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.
<span data-aop-column-vertical-alignment="top" ></span>#COLUMN_NAME#
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.
<span data-aop-width="20pt"></span>#COLUMN_NAME#
<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#
<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 PaneIt 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-rowas:<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-columnas:<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-freezeas:<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
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} 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
Export of IR With Saved Report
To export IR with saved report , use the format IR_StaticId|Saved_ReportName.
Give the report's static ID followed by the saved report’s name, separated by a pipeline.
This allows AOP to export the specific saved report you've defined.
For an example, refer to the AOP feature in the sample app.
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.
<span data-aop-heading-vertical-alignment="center" ></span>&COLUMN_NAME.
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.
<span data-aop-column-vertical-alignment="top" ></span>&COLUMN_NAME.
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-rowas:<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-columnas:<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-freezeas:<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} .
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.
Output
Upon processing, AOP provides the output with following contents.
output.pdf 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.1AOP 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.
Screeenshot of Regions
With AOP, you can export a screenshot of a specific region by assigning a static ID to the region and using supported custom attributes.
To utilize this feature, use the tag {%region_static_id}.
This feature is available for standard regions such as static content and others.
However, regions like Oracle charts,
interactive report,
classic report,
interactive grid,
or calendar are handled differently.
For more detailed information, you can visit the "Screenshots of Region" sample application example on AOP features.