Skip to main content

General Tags

Introduction

The templates are standard Office files in which tags will be replaced by structured data. These tags are quite standardized for either .docx, .pptx or .xlsx, however there are some subtle differences. They are all able to handle JavaScript angular expressions, on which some of the basics will follow.

Please visit documentation for respective template types and switch from left side bar.

This section will cover all the available options that can be applied regardless of the template type, be it logic or operation. In this documentation, curly braces are used as delimiters for the tags. Please see below on how to change delimiters.

Tag Overview

info

Please note that the column names are case sensitive, you can use double quotes to force the casing.

Tags can't start with a number and should start with an alphabetical character (a-z,A-Z)

Special Charactes in Tag Name

From AOP version 23.1, the tag name supports special Latin-1 supplement characters from Unicode 00C0-00D6 and 00D8-00FF and Devanagari characters. Please prefer the Unicode chart for supported special characters.

Example: Tag name can be {Èxàmplè}, {नाम}.

Details on Unicode can be found on Unicode charts.

In the table below is an overview of the available tags and their support in Word, Excel, Powerpoint, HTML, Markdown, Txt and CSV respectively. For more information on each tag, click on the Yes of the respective tag and template type combination.

Tag NameFormatWordExcelPPTXHTMLMDTxtCSV
Normal Substitution{...}YesYesYesYesYesYesYes
Data Loop Tag{#...}...{/...}YesYesYesYesYesYesYes
Uncertain Loop Tag{#...?}...{/...?}Yes------
Horizontal Tabular Looping{:...}...{/...}YesYes-----
Table Cell Style/Cell Markup{...$}YesYes-----
Table Row Loop/Row Merging{=...}...{/...}YesYesYes----
CSV Inline Loop Tag{:...}...{/...}------Yes
HTML Content Tag{_...}YesYes-----
Image Tag{%...}YesYesYesYesYes--
Bar Code / QR Code{|...}YesYesYesYesYes--
QR Code Image ReplacingQR Code containg image/barcode tagYesYesYes----
Slide Repeat Tag{!...}--Yes----
Excel Sheet Generation{!...}-Yes-----
Chart{$...}YesYesYes----
Chart Templating{aopchart ...}YesYesYes----
D3 Images{$d3 ...}YesYesYes----
Labels{-labels}Yes------
Interactive Report{&...}YesYes-----
Interactive Grid / Classic Report{&...&}YesYes-----
Calendar{?CALENDAR ...}Yes-----
FootNote Tag{+...}Yes------
Link Slides{+...}--Yes----
Hyperlink{*...}YesYesYes----
Auto Hyperlink Tag{*auto ...}YesYesYes----
Table of Contents{~}Yes------
Raw Tag{@...}Yes--YesYes--
Span{...#}-Yes-----
Static Condition{##...}...{/...}-Yes-----
Formula{>...}-Yes-----
Style Tag{style ...}Yes-Yes----
Watermark{watermark ...}Yes------
Page Break{?...}YesYes-----
Markdown Content{_ ... _}Yes------
Text Box{tbox ...}-Yes-----
Form tag{?form ...}Yes------
Link Target{*link ...}
{*target ...}
Yes------
Freeze Pane{freeze ...}-Yes-----
Sheet Protection{protect ...}-Yes-----
Attach/Insert Document{?insert ...}YesYesYes----
Embed Tag{?embed ...}Yes------
Hide Sheet(s) Tag{hide ...}-Yes-----
Hide Column(s) Tag{hideColumn ...}-Yes-----
Hide Row(s) Tag{hideRow ...}-Yes-----
Cell Validation Tag{validate ...}-Yes-----
Word Subtemplating{?include ...}Yes------
Comment Tag{//....} or{/*.....*/}YesYesYesYesYesYesYes
Text Box / Shape Removal Tag{...?}--Yes----

Tag Delimiters

Available From: v21.2

Using the global variables, It is possible to specify the tag delimiters used in the template. The default opening and closing tag delimiters are { and } (opening and closing curly brackets). The available options for opening and closing tag delimiters are {, {{, <, << and }, }}, >, >> respectively.

The example Init PL/SQL to set {{ and }} for the delimiters is as shown below.

aop_api_pkg.g_template_start_delimiter := '{{';
aop_api_pkg.g_template_end_delimiter := '}}';

The delimiters specified must be used in template. If you specify delimiters as mentioned in above example your template should contain tags wrapped in {{ }}, for example {{#orders}} ... {{/orders}}.

Special Tags:

There are some few pre-defined tags available in AOP, like finding index, length and so on.

current_child_index

The {current_child_index} is index of current row of a record starting from 0.

If you have template with following content:

{#orders}Order number: {current_child_index + 1}
Order Name: {order_name}
{#product} Product Number: {current_child_index + 1} Name: {product_name}
{/product}
{/orders}

Lets say, we have 1 order with 5 products as data source together with the given template is passed to AOP, the output will be as follows.

Order number: 1
Order Name: Order 1
Product Number: 1 Name: Business Shirt
Product Number: 2 Name: Trousers
Product Number: 3 Name: Jacket
Product Number: 4 Name: Blouse
Product Number: 5 Name: Skirt

As you can see, {current_child_index} is replaced with the row number of a record.

tip

The above tag {current_child_index} is useful if users wants to give index where it's not necessary to keep track of indices. If users want to keep track of previous indexes as well for example if users have different categories and its sub-categories like in nested loop condition, then AOP provides following tags for better indexing/numbering:

_index

For any loop tag, users can just add the suffix _index at the end of the required tag name in the template file and get the proper index.

For example:

{#orders}
{#products}

{orders_index} => This will resolve to index of the current order.

{products_index} => This will resolve to the index of the current product.

{current_child_index} => This will resolve to the index of the most inner loop in this case to that of products.

{/products}
{/orders}

loop_indices[number]

Similar to above tag, it also provides the index. If you have a category and its subcategory then you can use loop_indices[0] to get index of category and loop_indices[1] to get index of subcategory. Depending on the depth of items users can increase the number. This index starts from 0.
For example:

{#orders}
{#products}

{loop_indices[0]} => This will resolve to index of the current order as this is the first loop.

{loop_indices[1]} => This will resolve to the index of the current product.

{loop_indices[loop_indices.length-1]} => This will resolve to the index of the most inner loop in this case to that of products.

{/products}
{/orders}

loop_lengths[number]

It provides the length of current loop. If you have loop (say loop) consisting of another loop (say loop1) then you can use loop_lengths[0] to get the length of first loop and to get the length of another loop i.e loop1 then you can use loop_lengths[1]. Depending on the depth of nested loop (say i) you can use loop_lengths[i] to get the length of that loop.

For example:

{#orders}
{#products}

{loop_lengths[0]} => This will resolve the length of the orders as this is the first loop.

{loop_lengths[1]} => This will resolve to the length of current loop (i.e length of product) as this is the second loop.

{loop_lengths[loop_lengths.length-1]} => This will resolve to the length of the most inner loop in this case to that of products.

{/products}
{/orders}

Accessing columns of a Record

It is possible to get the columns of a record using {#recordName|keys}{.}{/recordName|keys} where recordName is the name of your record. {.} will be replaced by column name. AOP looks through the columns of first 5 rows (if exist) and return the unique column names..

tip

Can be used for horizontal looping with unknown number of columns and columns name.

{#object|keys}{.}{/object|keys}: Will iterate over the names of the keys(column) from the given object(row). If it's array(record: group of rows) then AOP looks into the unique keys(name of column) of the first 5 element. {.} will be replaced by key.

If you have template with following content:

{#product|keys}
Column Name: {.}
{/product|keys}

Lets say, we have 5 products with 4 columns as data source together with the given template is passed to AOP, the output will be as follows.

Column Name: image
Column Name: product_name
Column Name: quantity
Column Name: unit_price
Accessing column values

Similar to above, it is possible to iterate over the values of rows using |values with object name(row)
{#object|values}{.}{/object|values}: Will iterate over the values of the given object. {.} will be replaced by values in the object.

Simple angular like expressions

Available From: v1.0

Simple angular like expressions are also supported that can perform simple mathematical operations. A full list of Angular expressions can be found at https://docs.angularjs.org/guide/expression.

Simple Expressions

You can perform basic arithmetic operations like addition, subtraction, multiplication, and division using simple expressions. For instance, if you have two numbers num1 and num2, you can print their sum by using the expression {num1 + num2}.In case the given key has a boolean value then you can use {#boolean key}…{/boolean key} to print the content inside the tags if the value of the boolean key is true. Alternatively, for negation {^boolean key}…{/boolean key} is used. The content inside these tags will be printed if the boolean key has false as value or if the boolean key does not exists or if the boolean key is an empty array.

Lets see an example:

Data Source

select 'file1' as "filename",
cursor (
select cursor (
select p.product_name as "product_name",
p.product_description as "product_desc",
p.list_price as "selling_price",
p.list_price * 1.3 as "marked_price",
p.list_price * 0.5 as "cost_price",
case
when mod(p.product_id, 2) = 1 then
'true'
else
'false'
end as "product_avail",
case
when mod(p.product_id, 2) = 1 then
6
else
0
end as "quantity"
from aop_sample_product_info p
where p.product_id <= 2
) as "products"
) as "data"
from dual;

Template

For example we have the template with following content:

simple expression

 simple-expression.docx    simple-expression.xlsx    simple-expression.pptx  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

simple expression

 simple-expression.docx    simple-expression.xlsx    simple-expression.pptx  

String/Number Comparison

You can also check the value of keys with {#key=='value'}…{/key=='value'}. The content inside the tags will be only printed if the value of the key is equal to "value". Note here in {#key=='value'}, that straight single quotes are used!

Numbers can also be compared in the same way as strings {#key>50}…{/key>50}.The content inside the tags will be only printed if the value of the key is greater than 50. The following operators are supported: <, >, <=, >=, ==, !=

Data Source

select 'file1' as "filename",
cursor (
select cursor (
select p.product_name as "name",
p.list_price * 9.5 as "price",
p.list_price as "quantity",
case
when mod(p.product_id, 2) = 1 then
'true'
else
'false'
end as "availableForDelivery"
from aop_sample_product_info p
where p.product_id <= 5
) as "products"
) as "data"
from dual;

Template

For example we have the template with following content:

simple expression

 string-number-comparison.docx    string-number-comparison.xlsx    string-number-comparison.pptx  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

simple expression

 string-number-comparison.docx    string-number-comparison.xlsx    string-number-comparison.pptx  

Number/BigNumber Operations

AOP provides support for several methods, such as toExponential, toFixed, toPrecision, toString, parseInt, parseFloat, isNaN, and isFinite for numeric values. You can refer to the documentation for further information on these prototype methods.

In addition to these methods, AOP also supports big number functions for arbitrary-precision decimal and non-decimal arithmetic values. These include toNumber, isGreaterThan, isGreaterThanOrEqualTo, isLessThan, isLessThanOrEqualTo, minus, modulo, multipliedBy, plus, and dividedBy. Further information on big numbers and their functions can be found in the documentation.

Instructions for parseInt, parseFloat, isFinite and isNaN

Usage of the functions parseInt, parseFloat, isFinite and isNaN is slightly different in AOP than in the JS Syntax.

For example: For parseInt, instead of {parseInt(VAL)}, you need to do {VAL.parseInt()}.`

Data Source

select cursor (
select
98.123456 as "num0",
98765432109876544444 as "num1",
98765432109876543210 as "num2"
from dual
) as "data" from dual

Template

For example we have the template with following content:

simple expression

 big-number.docx    big-number.xlsx    big-number.pptx  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

simple expression

 big-number-output.docx    big-number-output.xlsx    big-number-output.pptx  

Conditional and Comparison operator

AOP allows one to compare variables as follows: {key == 'value' ? key2 : key3}. This way other keys/variables can also be inserted. This will print out value of key2 if the expression is true and key3 if the expression is false.

Possible conditional operators are:

  • Equal: == (equal to) or === (equal value and equal type)
  • Not Equal: != or !== (not equal value or not equal type)
  • And: &&
  • Or: ||
  • Greater than: >
  • Less than: <
  • Greater than or equal: >=
  • Less than or equal: <=

Conditional Block

AOP allows creation of conditional block based on a condition which will only be executed if the condition is truthy. If the condition is falsy, then the whole block will be removed.
To create a condition block, one can use it as loop tags. The tag has two parts: the start tag and the end tag. The start of the conditional tag is denoted by a # symbol followed by the condition enclosed in delimiters, such as {#record1.length > 1}. The end of the conditional tag is denoted by a / symbol followed by the same condition enclosed in delimiters, such as {/record1.length > 1}.

For Example:

{#orders.length > 1}
This will only show if the record named orders has more than 1 order, otherwise whole block will be removed.
{/orders.length > 1}

Other comparison operator like ===, ==, !==, !=, >, <, <=, >= can be used to create a condition block. A conditional block can have multiple condition joined using or : ||, and : && operator.
For ex:

{#(orders.length > 1 && orders.length < 4)}
This block will only be executed if the orders has more than one order but less than four.
{/(orders.length > 1 && orders.length < 4)}

Conditional example

For the data source given in example below {currency == 'EUR' ? '€'+price : price+'$'} prints €50 if true and 50$ if false and given price variable is 50.

Data Source

select 'file1' as "filename",
cursor (
select cursor (
select p.product_name as "product_name",
p.list_price * 9.5 as "unit_price",
p.list_price as "quantity",
case
when mod(p.product_id, 2) = 1
then 'true'
else 'false'
end as "onstock",
case
when mod(p.product_id, 2) = 1
then 'EUR'
else 'USD'
end as "cur"
from aop_sample_product_info p
where p.product_id <= 4
) as "products"
) as "data"
from dual;

Template

For example we have the template with following content:

simple expression

 conditional-operators.docx    conditional-operators.xlsx    conditional-operators.pptx  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

simple expression

 conditional-operatot-output.docx    conditional-operatot-output.xlsx    conditional-operatot-output.pptx  

String operations

If your tag resolves to a string then you can also use string manipulation functions like: substr, length, substring, replace, split, toLowerCase, toUpperCase, startsWith, endsWith, includes, indexOf, lastIndexOf, repeat, replace, slice and join two string with + operator . For more info of these prototype methods, visit documentation.

Source Code

Given the JSON file with following content:

select
cursor (
select
'Make it work, make it right, make it fast. - Kent Beck' as "quote"
from
dual
) as "data"
from
dual

Template

For example we have the template with following content:

simple expression

 string-operations.docx    string-operations.xlsx    string-operations.pptx  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

simple expression

 string-operations-output.docx    string-operations-output.xlsx    string-operations-output.pptx  

Array Operations

If your tag resolves to an array, i.e for using with a {#loop} tag. You can access the length of the loop array by .length property for example {loop.length}. You can also access individual array elements with the array access notation of Javascript, for example {loop[0].loop_inside_element}.

Given the JSON file with following content:

Data Source

select 'file1' as "filename",
cursor (
select cursor (
select c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor (
select o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor (
select p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price"
from aop_sample_order_items i,
aop_sample_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from aop_sample_orders o
where c.customer_id = o.customer_id
) "orders"
from aop_sample_customers c
where c.customer_id > 1
and c.customer_id < 5
) as "customers"
from dual
) as "data"
from dual;

Template

For example we have the template with following content:

array operations

 array-operations.docx    array-operations.xlsx    array-operations.pptx  

Output

When the above data source together with the given template is passed to AOP, the output will be as follows.

array operations

 array-operations.docx    array-operations.xlsx    array-operations.pptx  

Styling in AOP

Note, that the highlighted tags in template are not reflected, because AOP takes into consideration of first delimiter(in this case {). The styling of first delimiter is simple(not yellow), hence the output does not have the yellow color.
Other information like font size, color and styling are taken from the first delimiter.

Number formatting

Available From: v19.2

AOP allows you to format numbers using angular expressions. The following formats are supported:

"FML999G999G999G999G990D00"
"FML999G999G999G999G990"
"999G999G999G999G990D00"
"999G999G999G999G990D0000"
"999G999G999G999G999G999G990"
"999G999G999G999G990D00MI"
"S999G999G999G999G990D00"
"999G999G999G999G990D00PR"
"FML999G999G999G999G990PR"
"999G999G999G999G990D00PT"

Use the following structure (the following is showing how to format the number given by unit_price):

{tag|format:formatMask:decimalSeparators:currency}
concrete:
{unit_price|format:"FML999G999G999G999G990D00":".,":"$"}

The only required argument is the format mask. The default value for decimalSeparators is ".," and "$" is the default value for the currency. Please note that the format mask, decimal separators and currency can also be provided via a variable. For example the given data will also work:

...
"unit_price":180,
"mask":"S999G999G999G999G990D00"
"decimal":",.",
"cur":"$",
...

and the tag to be used is:

{unit_price|format:mask:decimal:cur}

Please note that the value returned will be a string. To get the native formatting in excel please use the {tag$} style tag.

Date formatting

Available From: v20.3

Since AOP version 20.3, it is possible to format date/time strings using angular expressions.

The structure to use is {date_data|format_date:formatMask:timeZone} where:

  • The JSON data contains a "date_data" field (arbitrarily named), in ISO 8601 format. Note that this format allows the inclusion of time zone info in the form of a UTC offset.
  • formatMask is a format mask which will determine how the output is formatted. The supported format masks are the Oracle Datetime format models.
  • timeZone is an optional standard time zone identifier like "Europe/Brussels" or "America/Los_Angeles" (complete list). If the output time zone is not specified, the output will use the same time zone as the input (as in the first example tag below). Given an invalid time zone, "Etc/UTC" is used.

For example, the tags

{D1|format_date:"D-MM-YY HH24:MI:SS"}
{D2|format_date:"DD/Mon/YYYY"}
{D3|format_date:"DD-MM-YYYY HH24:MI:SS"}
{D3|format_date:"DD-MM-YYYY HH24:MI:SS":"America/Argentina/Tucuman"}
{D4|format_date:"DD-MM-YYYY HH24:MI:SS"}
{D4|format_date:"DD-MM-YYYY HH24:MI:SS":"Asia/Kathmandu"}
{D4|format_date:"DD-MM-YYYY HH24:MI:SS":"America/Los_Angeles"}

with data

...
"D1": "2020-07-13T20:01:11Z-03:00",
"D2": "1999-05-16T10:01:11Z+05:00",
"D3": "2020-07-13T00:00:00Z",
"D4": "1999-05-16T23:00:00+00:00",
...

will result in:

13-07-20 20:01:11
16/MAY/1999
13-07-2020 00:00:00
12-07-2020 21:00:00
16-05-1999 23:00:00
17-05-1999 04:45:00
16-05-1999 16:00:00

Getting the type

Available From: v22.1.6

With this filter you can execute typeof in the tag. This is similar to executing typeof in javascript.

Example usages:
{product_name|typeof} will return "string" when the product name is of type string. {41|typeof} will return "number". {true|typeof} will return "boolean".

Data Manipulation

AOP allows you to execute various data manipulation of records from the template itself. The various data manipulation options are grouping, sorting, filtering, breaking, finding distinct value of a record. See below for detailed information with template and output.

Grouping Rows of Records

Available From: v19.3

It is possible to group records or arrays into n number of elements by appending |group:n at the start and end labels of the loop.The current group can be looped over by {#group} {/group} looping tags.

Given a record 'product' with rows of data, lets create a group of 2 each.

{#product|group:2}
{#group}
This group will have two product.
{/group}
{/product|group:2}

Example

Lets see implementation of grouping rows of records. The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference.

Data Source

Hereby examples of data source for different options.

select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor(
select
o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor(
select
p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price"
from
aop_sample_order_items i,
aop_sample_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
aop_sample_orders o
where
c.customer_id = o.customer_id
) "orders"
from
aop_sample_customers c
where
customer_id = 1
) as "data"
from
dual;
Template

The template should contain the grouping tag which follows the syntax {groupName|group:n} inside delimiters. For example we have the template with following content:

grouping template

 template.docx    template.xlsx    template.pptx  

Output

When the above data source (which results in a record named orders with another record product consisting details of product) together with the given template is passed to AOP, the output will be as follows.

Group Xlsx

 output.docx    output.xlsx    output.xlsx  

As you can see we get a table with two products each.

For powerpoint template, each slide have two products showing that it is possible to apply slide repeat by grouping.

Smart Grouping

In above example the products are grouped per 2 products. However if you want the number to be dynamic depending on the description, you can use the smart grouping filter.

{#(product|smartgroup:"PRODUCT_DESCRIPTION":20:30)}

Here we are looking at the product description attribute of product record and then grouping them with max of 20 lines given each line should contain 30 characters.

Looping n Times

Available From: v20.2

With this filter one can loop n times for record or a column. You should use #n where n is number of times followed by |loop which becomes {#n|loop} for start {/n|loop} for end. Note that you should use | followed by loop keyword and loop is not a record.

Consider we have a record name products then, the syntax for looping 3 times is as follows:

{#3|loop}
{#products}
Product name is {PRODUCT_NAME}
/products}
{/3|loop}

Instead of using the hardcoded value 3 there, one can also use dynamic value in data provided. If your sql is:

'4' as "loopLength",
--or
2 as "loopLength",

This "loopLength" value can be either integer (3) or string ("3"). Using the syntax below one would get the same result. The tag structure to use is:

{#loopLength|loop}
Contents to be repeated
{/loopLength|loop}
note

Note that this n time looping supports up to 5000 loops.

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference. Lets see the example to implement looping n times.

Data Source

Hereby examples of data source

select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
cursor(
select
p.product_name as "product_name",
i.quantity as "quantity"
from
aop_sample_order_items i,
aop_sample_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
aop_sample_orders o,
aop_sample_customers c
where
c.customer_id = o.customer_id
and c.customer_id = 1
) as "data"
from
dual;

Template

The template should contain the looping n times tag. For example we have the template with following content:

n loop

 template.docx    template.xlsx    template.pptx  

Output

When the above data source (which results in a record named product with details of product.) together with the given template is passed to AOP, the output will be as follows.

n_loop_output

 output.docx    output.xlsx    output.pptx  

Filtering

Available From: v21.2

It is possible to filter out the rows from a record based on a condition using filter method. The syntax of filter method is
{#recordName|filter:operand1:operator:operand2} thus creating a record named filtered.

The supported operators are: '<', '>', '<=', '>=', '==', '===', '!=', '!==' .

Considering a record name products with various column including unit_price, the implementation of filtering by unit_price is:

{#products|filter:"unit_price":">":"100"}
{#filtered}
{product_name} {quantity} {unit_price}
{/filtered}
{/products|filter:"unit_price":">":"100"}

This will only take the products with unit_price more than 100 and you will be able to iterate through the filtered rows using
{#filtered} ... {/filtered}.

Alternate Way to access filtered record

You might face an issue with XLSX and PPTX templates (in older version of AOP) while using the filter method using filtered loop.
For such case, access filtered records with dot(.) operator. Example:

{#(products|filter:"unit_price":">":"100".filtered)}
{product_name} {quantity} {unit_price}
{/(products|filter:"unit_price":">":"100".filtered)}

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference. Lets see the example of filter.

Data Source

Hereby examples of data source for different options.

select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
cursor(
select
p.product_name as "product_name",
i.unit_price as "unit_price",
i.quantity as "quantity"
from
aop_sample_order_items i,
aop_sample_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
aop_sample_orders o,
aop_sample_customers c
where
c.customer_id = o.customer_id
and c.customer_id = 1
) as "data"
from
dual;

Template

The template should contain the filter tag. For example we have the template with following content:

xlsx filter

 template.docx    template.xlsx    template.pptx  

Output

When the above data source (which results in a record named products with details inside it) together with the given template is passed to AOP, the output will be as follows.

xlsx filter output

 output.docx    output.xlsx    output.pptx  

Sorting

It is possible to sort the rows of record by its value or property. Along with the property, type of sorting should also be defined as "asc" for ascending and "desc" for descending. A record named sorted is created allowing you to iterate through the rows of this record.

Syntax is: {recordName|sort:"columnName":"asc" or "dec"}.

Lets say, you have a record named products with column unit_price.

{#products|sort:"unit_price":"asc"}
{#sorted}
{product_name} {quantity} {unit_price}
{/sorted}
{/products|sort:"unit_price":"asc"}
Sort based on multiple columns

If we want to sort according to multiple values, for example "unit_price" as ascending and sort by "quantity" for those "unit_price" that are equal in descending order, we can specify :

{#products|sort:"unit_price:quantity":"asc:desc"}
{#sort}
{product_name} {quantity} {unit_price}
{/sort}
{/products|sort:"unit_price:quantity":"asc:desc"}

This would sort by unit_price at first and if two values of unit_price are equal, it would sort by quantity.

Alternate Way to access sorted record

You might face an issue with XLSX and PPTX templates (in older version of AOP) while using the sort method using sorted loop.
For such case, access sorted records with dot(.) operator. Example:

{#(products|sort:"unit_price":"asc").sorted}
{product_name} {quantity} {unit_price}
{/(products|sort:"unit_price":"asc").sorted}

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference. Lets see the implementation of sorting.

Data Source

Hereby examples of data source for different options.

select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
cursor(
select
p.product_name as "product_name",
i.unit_price as "unit_price",
i.quantity as "quantity"
from
aop_sample_order_items i,
aop_sample_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
aop_sample_orders o,
aop_sample_customers c
where
c.customer_id = o.customer_id
and c.customer_id = 1
) as "data"
from
dual;

Template

The template should contain the sort tag. For example we have the template with following content:

sort template

 template.docx    template.xlsx    template.pptx  

Output

When the above data source (which results in a record named products with columns like unit_price and quantity) together with the given template is passed to AOP, the output will be as follows.

sort output

 output.docx    output.xlsx    output.pptx  

Distinct

It is possible get a distinct column values from a record using AOP. Syntax is: {recordName|distinct:"column_name":"asc" or "dec"}

For a record name products with unit_price column, we can get distinct unit_price as:

{#products|distinct:"unit_price":"asc"}
{unit_price}
{/products|distinct:"unit_price":"asc"}

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference. Lets see the implementation of distinct.

Data Source

Hereby examples of data source for different options.

select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
cursor(
select
p.product_name as "product_name",
i.unit_price as "unit_price",
i.quantity as "quantity"
from
aop_sample_order_items i,
aop_sample_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
aop_sample_orders o,
aop_sample_customers c
where
c.customer_id = o.customer_id
and c.customer_id = 1
) as "data"
from
dual;

Template

The template should contain the distinct tag. For example we have the template with following content:

                                    Distinct Quantity.  
{#product|distinct:"quantity":"asc"} {quantity} {/product|distinct:"quantity":"asc"}

 template.xlsx    template.xlsx    template.pptx  

Output

When the above data source (which results in a record named product with details of product) together with the given template is passed to AOP, the output will be as follows.

Distinct Quantity.
3
2
4

 output.xlsx    output.xlsx    output.pptx  

Breaking into Groups

Available From: v20.2

It is possible to break a record into groups of records based on the value of column of the record.

Syntax is : {#recordName|break:"columnName"}.

If you have a record named products with column named category inside it, then implementation of breaking into group would be:

{#products|break:"CATEGORY"}
CATEGORY: {break[0].CATEGORY}
The items in this category are:
{#break}
• {CATEGORY} {PRODUCT_NAME} {LIST_PRICE} {PRODUCT_AVAIL}
{/break}
{/products|break:"CATEGORY"}

This will break the products record into multiple records, each having same value for column CATEGORY and can iterate through the particular group using loop tag.

Additional Parameter for breaking into group

One can pass two additional parameter. First one is for sorting. It has two options asc for ascending order and desc for descending order. Second parameter is positioning the null value group. It has two options first and last. They are positioned as the naming suggests in the beginning or at the end of the document respectively. Using the syntax below,

{#products|break:"CATEGORY":"desc":"last"}
CATEGORY: {break[0].CATEGORY}
The items in this category are:
{#break}
• {CATEGORY} {PRODUCT_NAME} {LIST_PRICE} {PRODUCT_AVAIL}
{/break}
{/products|break:"CATEGORY":"desc":"last"}
Multiple Breaking Parameters

This tag supports multiple breaking parameters. By using the following syntax you can filter your data with multiple parameters:

{#products|break:"CATEGORY:PRODUCT_AVAIL":"desc":"last"}
CATEGORY: {break[current_child_index].CATEGORY}
The items in this category are:
{#break}
• {CATEGORY} {PRODUCT_NAME} {LIST_PRICE} {PRODUCT_AVAIL}
{/break}
{/products|break:"CATEGORY:PRODUCT_AVAIL":"desc":"last"}

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference. Lets see the implementation of breaking into groups.

Data Source

Hereby examples of data source for different options.

select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
cursor(
select
p.product_name as "product_name",
i.unit_price as "unit_price",
i.quantity as "quantity",
p.category as "category"
from
aop_sample_order_items i,
aop_sample_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
aop_sample_orders o,
aop_sample_customers c
where
c.customer_id = o.customer_id
and c.customer_id = 1
) as "data"
from
dual;

Template

The template should contain the break into groups tag. For example we have the template with following content:

breaking into groups

 template.docx    template.xlsx    template.pptx  

Output

When the above data source (which results in a record named product with details its details) together with the given template is passed to AOP, the output will be as follows.

break into records

 output.docx    output.xlsx    output.pptx  

Mathematical Operations During a Loop

Available From: v20.2

Numeric operations inside a loop are supported by using the | operator. The following operations are supported:

  • |sum calculates the total sum of a numeric property during a loop.
  • |avg calculates the total average of a numeric property during a loop.
  • |min returns the minimum numeric value of a property during a loop.
  • |max returns the maximum numeric value of a property during a loop.

The syntax is given below:

{array|(min|max|sum|avg):"attributeToApplyTo":takeNullValueIntoAccount:valueForNull}

concretely:

{product |sum:"quantity":true:0}
{product |max:"quantity":false}
{product |avg:"quantity":false}
{product |min:"quantity":false}

Since AOP 21.1, the operation can also be used across multiple hierarchy. The syntax for it would be depth1:depth2:depth3:property_to_operate_on. Lets take an example for calculating the sum of the quantities of the products across the array of orders, we can chain the attribute as product:quantity.

note

Please avoid number with precision > 15 digits.

{orders |sum:"product:quantity":true:0} => for each element of orders, take the sum of the quantity of the products of this order.
{orders |max:"product:quantity":false}
{orders |avg:"product:quantity":false}
{orders |min:"product:quantity":false}

Since AOP 21.2, mathematical operations can be performed inside these operations.Lets take an example for calculating the sum of the products of quantities of the products and unit_price, we can chain the attribute as:

{product | sum:"quantity*unit_price":true:0}.

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference. Lets see the implementation of mathematical operations inside a loop.

Data Source

Hearby the example of data source for different options.

select 'file1' as "filename",
cursor (
select c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor (
select o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor (
select p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price",
apex_web_service.blob2clobbase64(p.product_image) as "image"
from aop_sample_order_items i,
aop_sample_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from aop_sample_orders o
where c.customer_id = o.customer_id
) "orders"
from aop_sample_customers c
where customer_id = 1
) as "data"
from dual

Template

The template should contain the break into groups tag. For example we have the template with following content:

Mathematical Operations inside a loop

 template.docx    template.xlsx    template.pptx  

Output

When the above data source (which results in a record named product with details its details) together with the given template is passed to AOP, the output will be as follows.

Mathematical Operations inside a loop

 output.docx    output.xlsx    output.pptx  

Barcode and QR code

Available From v3.1

AOP allows for the insertion of barcode and QR code in Word, Excel and PowerPoint using tags with syntax pipe(|) followed by column name inside delimiters (e.g., {|barcode}). To differentiate between barcode and QR code tags, an additional option is required to confirm the type of tag i.e option type .

When the AOP identifies one of these tags in a template, it replaces it with the corresponding barcode or QR code.

For the following SQL Data Query:

select 'file1' as "filename",
cursor (
select product_id as "product_code",
'ean13' as "product_code_type"
from aop_sample_product_info
where product_name = 'Wallet'
) as "data"
from dual

The tag to use in this case would be {|product_code}.

The type should be either one of the following barcode types or "qrcode".

tag Cannot be placed inside table in PPTX

Same rules are applied to generate barcode in Powerpoint as done in Word, except the tag can cannot be placed inside table.
In PowerPoint, this tag should be given in separate textbox to get the barcode.

Supported Barcodes Types
Barcode type Description
auspost AusPost 4 State Customer Code
azteccode Aztec Code
azteccodecompact Compact Aztec Code
aztecrune Aztec Runes
bc412 BC412
channelcode Channel Code
codablockf Codablock F
code11 Code 11
code128 Code 128
code16k Code 16K
code2of5 Code 25
code32 Italian Pharmacode
code39 Code 39
code39ext Code 39 Extended
code49 Code 49
code93 Code 93
code93ext Code 93 Extended
codeone Code One
coop2of5 COOP 2 of 5
daft Custom 4 state symbology
databarexpanded GS1 DataBar Expanded
databarexpandedcomposite GS1 DataBar Expanded Composite
databarexpandedstacked GS1 DataBar Expanded Stacked
databarexpandedstackedcomposite GS1 DataBar Expanded Stacked Composite
databarlimited GS1 DataBar Limited
databarlimitedcomposite GS1 DataBar Limited Composite
databaromni GS1 DataBar Omnidirectional
databaromnicomposite GS1 DataBar Omnidirectional Composite
databarstacked GS1 DataBar Stacked
databarstackedcomposite GS1 DataBar Stacked Composite
databarstackedomni GS1 DataBar Stacked Omnidirectional
databarstackedomnicomposite GS1 DataBar Stacked Omnidirectional Composite
databartruncated GS1 DataBar Truncated
databartruncatedcomposite GS1 DataBar Truncated Composite
datalogic2of5 Datalogic 2 of 5
datamatrix Data Matrix
datamatrixrectangular Data Matrix Rectangular
dotcode DotCode
ean13 EAN-13
ean13composite EAN-13 Composite
ean14 GS1-14
ean2 EAN-2 (2 digit addon)
ean5 EAN-5 (5 digit addon)
ean8 EAN-8
ean8composite EAN-8 Composite
flattermarken Flattermarken
gs1-128 GS1-128
gs1-128composite GS1-128 Composite
gs1-cc GS1 Composite 2D Component
gs1datamatrix GS1 Data Matrix
gs1datamatrixrectangular GS1 Data Matrix Rectangular
gs1northamericancoupon GS1 North American Coupon
gs1qrcode GS1 QR Code
hanxin Han Xin Code
hibcazteccode HIBC Aztec Code
hibccodablockf HIBC Codablock F
hibccode128 HIBC Code 128
hibccode39 HIBC Code 39
hibcdatamatrix HIBC Data Matrix
hibcdatamatrixrectangular HIBC Data Matrix Rectangular
hibcmicropdf417 HIBC MicroPDF417
hibcpdf417 HIBC PDF417
hibcqrcode HIBC QR Code
iata2of5 IATA 2 of 5
identcode Deutsche Post Identcode
industrial2of5 Industrial 2 of 5
interleaved2of5 Interleaved 2 of 5 (ITF)
isbn ISBN
ismn ISMN
issn ISSN
itf14 ITF-14
japanpost Japan Post 4 State Customer Code
kix Royal Dutch TPG Post KIX
leitcode Deutsche Post Leitcode
matrix2of5 Matrix 2 of 5
maxicode MaxiCode
micropdf417 MicroPDF417
microqrcode Micro QR Code
msi MSI Modified Plessey
onecode USPS Intelligent Mail
pdf417 PDF417
pdf417compact Compact PDF417
pharmacode Pharmaceutical Binary Code
pharmacode2 Two-track Pharmacode
planet USPS PLANET
plessey Plessey UK
posicode PosiCode
postnet USPS POSTNET
pzn Pharmazentralnummer (PZN)
qrcode QR Code
rationalizedCodabar Codabar
raw Custom 1D symbology
royalmail Royal Mail 4 State Customer Code
sscc18 SSCC-18
symbol Miscellaneous symbols
telepen Telepen
telepennumeric Telepen Numeric
ultracode Ultracode
upca UPC-A
upcacomposite UPC-A Composite
upce UPC-E
upcecomposite UPC-E Composite

Tag Options

Usages

The following options should be prepended with the column name being used followed by an "_". For example, if the column name is "product_code", then the options would be "product_code_width", "product_code_height"etc. See example below.

Case Sensitive

The options are case sensitive. For example, "_width" is valid, but "_Width" is not. You will need to wrap the column name with double quotes in order to force the casing.

  • type : The type of barcode to generate. See the table above for a list of valid types.

  • height : The height of the barcode in pixels. Default is 200 for QR, 50 for the other types.

  • width : The width of the barcode in pixels. Default is 200.

  • errorcorrectlevel : The level of which the QR code should be recoverable. The options are:

    • "L" (up to 7% damage)
    • "M" (up to 15% damage)
    • "Q" (up to 25% damage)
    • "H" (up to 30% damage)

    Extra info: http://www.qrcode.com/en/about/error_correction.html

  • url : The URL to hyperlink to when the barcode/qrcode is clicked

  • rotation: The rotation angle of the barcode/qrcode (in degrees). Default is 0.

  • background_color : The background color of the barcode/qrcode. Default is white. You can provide hex value; html named colors like red, white, purple; rgb(255, 0, 0) ; or any other css supported format.

  • padding_width: the padding of the inserted qrcode/barocode. default is 10. in pixels

  • padding_height: the padding of the inserted qrcode/barocode. default is 10. in pixels

  • extra_options: if you want to include extra options like including barcode text on the bottom, please use this option. The options should be space separated and should be followed by a "=" and their value. E.g.: "includetext guardwhitespace" or "eclevel=L". Please visit: https://github.com/bwipp/postscriptbarcode/wiki/Symbologies-Reference for all option availability.

You might sometimes get the following error (might occur when the tag is inside loop):

Extra QR Code Options

Besides the option specified above, QR code can have multiple options:

  • qr_dotscale : For body block, must be greater than 0, less than or equal to 1. default is 1
  • qr_logo : Logo Image (At center of QR). can be Base64/URL/FTP/SFTP
  • qr_background_image: Background Image. Can be Base64/URL/FTP/SFTP
  • qr_color_dark: Color of dark blocks. Default is black
  • qr_color_light: Color of light blocks. Default is white
  • qr_logo_width: Width of logo. Default is 80
  • qr_logo_height: Height of logo. Default is 80
  • qr_quiet_zone: Padding around QR code, default 2
  • qr_quiet_zone_color: Color of quiet zone. Default is #ffffff
  • qr_background_image_alpha: Background image alpha. Default is 1
  • qr_po_color: Global Position Outer color. if not set, the default is colorDark
  • qr_pi_color: Global Position Inner color. if not set, the default is colorDark
  • qr_po_tl_color: Position Outer color - Top Left
  • qr_pi_tl_color: Position Inner color - Top Left
  • qr_po_tr_color: Position Outer color - Top Right
  • qr_pi_tr_color: Position Inner color - Top Right
  • qr_po_bl_color: Position Outer color - Bottom Left
  • qr_pi_bl_color: Position Inner color - Bottom Left
  • qr_timing_v_color: Vertical timing color
  • qr_timing_h_color: Horizontal timing color
  • qr_timing_color: Global Timing color. if not set
  • qr_auto_color: Automatic color adjustment(for data block) (default is false) (set to false if using background images)
  • qr_auto_color_dark: Automatic color: dark CSS color (only required when qr_auto_color is set true) (dark color preferred, otherwise may lead to undetectable QR)
  • qr_auto_color_light: Automatic color: light CSS color (only required when qr_auto_color is set true)

Example

The data source below was created using the database available in the sample data of AOP. The database contains numerous tables and views with raw data that can be used for reference.

Data Source

Hereby examples of data source for different options.`

select 'file1' as "filename",
cursor (
select product_name as "product_name",
category as "category",
18965478321
|| lpad(product_id, 1, 0) as "product_code",
'ean13' as "product_code_type",
'https://www.google.be/search?q=wallet' as "product_link",
'qrcode' as "product_link_type"
from aop_sample_product_info
where product_name = 'Wallet'
) as "data"
from dual;

Template

The template should contain the barcode or qr code tag, which starts with | followed by column name inside delimiters. For example we have the template with following content:

 template.docx    template.xlsx    template.pptx  

Output

When the above data source (which results in few rows with product name a barcode and qrcode) together with the given template is passed to AOP, the output will be as follows.

 output.docx    output.xlsx    output.pptx  

QR Code Example

For QR codes, we can specify the following options.

Data Source

select
'file1' as "filename",
cursor (
select
'Chocolates' as "product_name",
'qrcode' as "product_name_type",
1 as "product_name_qr_dotscale",
'Image(URL/Base64)' as "product_name_qr_logo",
'Image(URL/Base64)' as "product_name_qr_background_image",
'#111111' as "product_name_qr_color_dark",
'#dddddd' as "product_name_qr_color_light",
80 as "product_name_qr_logo_width",
80 as "product_name_qr_logo_height",
'#dddddd' as "product_name_qr_logo_background_color",
2 as "product_name_qr_quiet_zone",
'#000000' as "product_name_qr_quiet_zone_color",
0.3 as "product_name_qr_background_image_alpha",
'#e1622f' as "product_name_qr_po_color",
'#aa5b71' as "product_name_qr_pi_color",
'#aabbcc' as "product_name_qr_po_tl_color",
'#231333' as "product_name_qr_pi_tl_color",
'#342442' as "product_name_qr_po_tr_color",
'#ab2134' as "product_name_qr_pi_tr_color",
'#4380ba' as "product_name_qr_po_bl_color",
'#e2b454' as "product_name_qr_pi_bl_color",
'ac2059' as "product_name_qr_timing_v_color",
'376d71' as "product_name_qr_timing_h_color",
'#376d71' as "product_name_qr_timing_color",
1 as "product_name_qr_auto_color",
'#000000' as "product_name_qr_auto_color_dark",
'#ffffff' as "product_name_qr_auto_color_light"
from
dual
) as "data"
from
dual

Template

The template should contain a qrcode tag (| followed by column name inside delimiters). For example we have the template with following content:

{|product_name}

 template.docx    template.xlsx    template.xlsx  

Output

If above data source that results in a row with columns product_name and few others and above template is passed to AoP. The resulting output would be of following content:

The structure of a QR code can be represented as:

 output.docx    output.xlsx    output.pptx  

Advanced QR Codes

QR Codes can be generated for specific tasks like connecting to the WiFi, sending mails, contact details etc. , the JSON structure for these tasks is as follows.

Tag

Please note that all the following example have data_key as column name. The tag to use in the template would be {|data_key}

Wifi

Available from: v21.1

For connecting to a wireless network, wifi ssid, wifi password and encryption type can be specified, where encryption types can be WPA, WEP, WEP2 etc.

select 'file1' as "filename",
cursor (
select
'SSID of wireless' as "data_key",
'qr_wifi' as "data_key_type",
'pass123' as "data_key_wifi_password",
'WPA' as "data_key_wifi_encryption",
1 as "data_key_wifi_hidden"
from dual
) as "data"
from dual

Telephone Number

Available from: v21.1

For creating a dialing action, the telephone number should be specified as follows.

select 'file1' as "filename",
cursor(
select
'+3216298447' as "data_key",
'qr_telephone' as "data_key_type"
from dual
) as "data"
from dual

SMS

Available from: v21.1

SMS messages can be sent using QR codes generated. For this, data should be specified in following format.

select 'file1' as "filename",
cursor(
select
'+3216298447' as "data_key",
'qr_sms' as "data_key_type",
'Hello, I''d like to know about pricing of AOP on premise version.' as "data_key_sms_body"
from dual
)
as "data"
from dual