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------

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"

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

URL​

Available from: v21.1

A QR code can be used to be redirected to a URL to any webpage (Facebook, Twitter, Linkedin, Instagram etc.). The URL should be in standard format eg. https://united-codes.com/ 

select 'file1' as "filename",
    cursor(
      select
        'https://united-codes.com/' as "data_key",
        'qr_url' as "data_key_type"
      from dual
    )
  as "data"
from dual

vCard​

Available from: v21.1

A VCF or vCard can be specified on a QR code with the options shown in following JSON.

select 'file1' as "filename",
    cursor(
      select
        'John' as "data_key",
        'qr_vcard' as "data_key_type",
        'Doe' as "data_key_vcard_lastname",
        'support@apexrnd.com' as "data_key_vcard_email",
        'https://united-codes.com/' as "data_key_vcard_website"
      from dual
    )
  as "data"
from dual

MECard​

Available from: v21.1

A MECard can be specified on a QR code with the options shown in following JSON.

select 'file1' as "filename",
    cursor(
      select
        'John' as "data_key",
        'qr_me_card' as "data_key_type",
        'Doe' as "data_key_me_card_lastname",
        'johnny' as "data_key_me_card_nickname",
        'support@apexrnd.com' as "data_key_me_card_email",
        '+3216298447' as "data_key_me_card_contact_primary",
        '+33216298447' as "data_key_me_card_contact_secondary",
        '+33216298447' as "data_key_me_card_contact_tertiary",
        'https://united-codes.com/' as "data_key_me_card_website",
        '1997/05/05' as "data_key_me_card_birthday",
        'Lodata_key' as "data_key_me_card_notes"
      from dual
    )
  as "data"
from dual

GeoLocation​

Available from: v21.1

Location on a map can be specified using longitude latitude and altitude as follows.

select 'file1' as "filename",
    cursor(
      select
        '50.86217975966617' as "data_key",
        'qr_geolocation' as "data_key_type",
        '4.671840782417369' as "data_key_geolocation_longitude",
        '100' as "data_key_geolocation_altitude"
      from dual
    )
  as "data"
from dual

Events​

Available from: v21.1

Events on a calendar can be specified on a QR code with the options shown in following JSON. Summary, event_startdate, event_enddate and event_format are compulsory to get a valid QR code,

select 'file1' as "filename",
    cursor(
      select
        'AOP Training Session' as "data_key",
        'qr_event' as "data_key_type",
        '2020/01/23' as "data_key_event_startdate",
        '2020/01/24' as "data_key_event_enddate"
      from
        dual
    )
  as "data"
from dual

Zatca Invoice QRCodes​

Available from: v24.1

QRCode to validate Zatca invoice can be generated using AOP. The properties that are required to generate QRCode are as follows:

  • seller_name - Name of the seller
  • vat_number - Tax number of the seller
  • invoice_time - Date and time of the invoice
  • invoice_amount - Amount of the invoice
  • vat_amount - VAT amount of the invoice

Here is an example to create QRCode for Zatca invoice.

select 'file1' as "filename",
    cursor(
      select
        'AOP Training Session' as "data_key",
        'qr_zatca' as "data_key_type",
        'Ramchandra KC' as "data_key_zatca_seller_name",
        '32131237' as "data_key_zatca_vat_number",
        'Z15:30:00T2022-04-25' as "data_key_zatca_invoice_time",
        210000.0 as "data_key_zatca_invoice_amount",
        1300.0 as "data_key_zatca_vat_amount"
      from
        dual
    )
  as "data"
from dual

All Types of QR Codes​

Below is the example of all types of qr codes.

Data Source​

declare 
l_return clob;
begin apex_json.initialize_clob_output(dbms_lob.call, true, 2);
l_return := q'[
  [
      {
          "filename": "file1",
          "data": [
              {
                  "cust_first_name" : "John",
                  "cust_last_name": "Doe",
                  "qrcodes": [
                      {
                          "data_key": "SSID of wireless",
                          "data_key_type": "qr_wifi",
                          "data_key_wifi_password": "pass123",
                          "data_key_wifi_encryption": "WPA",
                          "data_key_wifi_hidden": 1
                      },
                      {
                          "data_key": "+3216298447",
                          "data_key_type": "qr_telephone"
                      },
                      {
                          "data_key": "+3216298447",
                          "data_key_type": "qr_sms",
                          "data_key_sms_body": "Hello, I\'d like to know about pricing of AOP on premise version."
                      },
                      {
                          "data_key": "https://united-codes.com/",
                          "data_key_type": "qr_url"
                      },
                      {
                          "data_key": "John",
                          "data_key_type": "qr_vcard",
                          "data_key_vcard_lastname": "Doe",
                          "data_key_vcard_email": "support@apexrnd.com",
                          "data_key_vcard_website": "https://united-codes.com/"
                      },
                      {
                          "data_key": "John",
                          "data_key_type": "qr_me_card",
                          "data_key_me_card_lastname": "Doe",
                          "data_key_me_card_nickname": "johnny",
                          "data_key_me_card_email": "support@apexrnd.com",
                          "data_key_me_card_contact_primary": "+3216298447",
                          "data_key_me_card_contact_secondary": "+33216298447",
                          "data_key_me_card_contact_tertiary": "+33216298447",
                          "data_key_me_card_website": "https://united-codes.com/",
                          "data_key_me_card_birthday": "1997/05/05",
                          "data_key_me_card_notes": "Lodata_key"
                      },
                      {
                          "data_key": "50.86217975966617",
                          "data_key_type": "qr_geolocation",
                          "data_key_geolocation_longitude": "4.671840782417369",
                          "data_key_geolocation_altitude": "100"
                      },
                      {
                          "data_key": "AOP Training Session",
                          "data_key_type": "qr_event",
                          "data_key_event_startdate": "2020/01/23",
                          "data_key_event_enddate": "2020/01/24"
                      }
                  ]
              }
          ]
      }
  ]
]';

return l_return;

end;

Template​

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

{#qrcodes}
    {data_key_type}            {|data_key} 
                                            {/qrcodes}

 template.docox    template.xlsx    template.pptx  

Output​

When the above data source (which results in rows of qr codes) together with the given template is passed to AOP, the output will consist all qr codes.

 output.docx    output.xlsx    output.pptx  

Barcode Info

For barcodes, instead of doing the above, you could also choose to install a barcode font, for example Free 3of9 or http://www.dafont.com/3of9-barcode.font. Barcode fonts are more performant than images. See the other section for more information about language and font support.

Frequent Errors:​

  • In Word templates, you might sometimes get the following error (might occur when the tag is inside loop):

    The tag "qrcode" is not inside a paragraph

    In this case, just change the style of the tag (the whole {|qrcode}), for example change the text color to red, and the error should be resolved.

  • In PowerPoint templates, you might get empty slide or tags are not replaced accordingly, this issue is probably caused because image tag or bar code is not inside a text box.

QR Code Image Replacing​

Available From: v18.1

AOP allows you to replace a QRcode image with another image. The QR code can contain the image tags and barcode tags, i.e {%key} or {|key}. This method can be used if you are inserting a floating image, or need to have certain border styles on the image. The tag replacing will work as look as there are no artistic effects used for the image themselves.

Please note that the scope always starts at the default place, so the {#loop} tags have no effect.

Ignoring Check for QRCode Image Replacing

AOP verifies every image if an QRCode consists image tag or not. Thus, for a request with many images, it could consume some extra time to generate output.
From AOPv 23.1.1, it is possible to ignore the image check for QR code using ignore_qrcode_replacement in the Init PL/SQL Code.

aop_api_pkg.ignore_qrcode_replacement := '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.
The following example demonstrate the usage of qrcode image replacing.

Data Source​

Hereby examples of data source for different options.

select
  'file1' as "filename",
  cursor (
    select
      '/9j/4AAQSkZJRgABAQEAYABgAAD/4QBoRXhpZgAATU0AKgAAAAgABAEaAAUAAAABAAAAPgEbAAUAAAABAAAARgEoAAMAAAABAAIAAAExAAIAAAARAAAATgAAAAAAAABgAAAAAQAAAGAAAAABcGFpbnQubmV0IDQuMC4yMQAA/9sAQwAUDg8SDw0UEhESFxYUGB8zIR8cHB8/LS8lM0pBTk1JQUhGUlx2ZFJXb1hGSGaMaG96fYSFhE9jkZuPgJp2gYR//9sAQwEWFxcfGx88ISE8f1RIVH9/f39/f39/f39/f39/f39/f39/f39/f39/f39/f39/f39/f39/f39/f39/f39/f39//8AAEQgArQCXAwEiAAIRAQMRAf/EAB8AAAEFAQEBAQEBAAAAAAAAAAABAgMEBQYHCAkKC//EALUQAAIBAwMCBAMFBQQEAAABfQECAwAEEQUSITFBBhNRYQcicRQygZGhCCNCscEVUtHwJDNicoIJChYXGBkaJSYnKCkqNDU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6g4SFhoeIiYqSk5SVlpeYmZqio6Slpqeoqaqys7S1tre4ubrCw8TFxsfIycrS09TV1tfY2drh4uPk5ebn6Onq8fLz9PX29/j5+v/EAB8BAAMBAQEBAQEBAQEAAAAAAAABAgMEBQYHCAkKC//EALURAAIBAgQEAwQHBQQEAAECdwABAgMRBAUhMQYSQVEHYXETIjKBCBRCkaGxwQkjM1LwFWJy0QoWJDThJfEXGBkaJicoKSo1Njc4OTpDREVGR0hJSlNUVVZXWFlaY2RlZmdoaWpzdHV2d3h5eoKDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uLj5OXm5+jp6vLz9PX29/j5+v/aAAwDAQACEQMRAD8A7GiiigAooooAKKKKACiiigBrSIn33VfqcUK6uMqwYexzXFSszyMzEkk8kmiOV4nDRuyMO4OK7/qWm5xfW9djt6KydJ1X7SRDPgS9m/vf/XrWrjnBwdpHVCamroKKKKgsKKKKACiiigAooooAKKKKACiiigAooooAKKKKAOIlGJXH+0abUlwMXEo9HP8AOo691bHivcVGZGDKcMpyCO1dhY3AurSOUdSOR6HvXHVveG5cxzRH+Ehh+P8A+quXFwvDm7HThZ2ny9zbooorzD0QooooAKKKKACiiigAooooAKKKKACiiigApGYKpZjgAZJpax9evfLiFsh+Z+W9hV04OclFEVJqEeZmBK/mSu/95iabRRXtrQ8cK2PDn/HxN/uf1rHrY8Nj/SJj/sD+dY4j+Ezah/ER0NFFFeOeqFFFFABRRRQAUUUUAFFFFABRRRQAUUUUARXM6W0Dyv0Ufn7Vx08z3EzyyHLMcmtLXb3z5/IQ/u4zz7tWVXqYWlyR5nuzzcTV5pcq2QUUUV1HMFbGgzwW4nM0qoWwBk/WseioqQ548rLpz5JcyOv/ALRs/wDn5j/76o/tGz/5+Y/++q5CiuX6nHudH1uXY6/+0bP/AJ+Y/wDvqj+0bP8A5+Y/++q5Cij6nHuH1uXY7Bb+0Y4FxFn/AHhVgEEZByK4erVjfy2UgKsTHn5kPQ1M8HZe6y44vX3kdfRTY3WWNXQ5VhkGiuA7Shd6vBaXDQukhZccgDFRf8JBbf8APOX8h/jWXrf/ACE5fov8hVCvSp4anKKbPPniJxk0jo/+Egtv+ecv5D/Gj/hILb/nnL+Q/wAa5yir+qUyPrVQ7O1uFurdZkBCtnAPXriodUvPsdozA/vG+VPr603Rf+QXD/wL/wBCNZfiNj9riXPAjzj8TXFTpqVbl6HXOo1S5upkE5OTRRRXrHmBRRRQAUUUUAFFFFABRRRQAUUVJbwSXMyxRjLH9KG0ldgld2R02isW0yHPbI/U0VatoFtrdIV6IMfWivDm05No9mCtFJnNa3/yE5fov8hVCr+t/wDITl+i/wAhVCvYpfw4+h5NX436hRRRWhB1ei/8guH/AIF/6EayvEX/AB+x/wDXMfzNaui/8guH/gX/AKEayvEX/H7H/wBcx/M151H/AHh/M76v8BfIyaKKK9E4AooooAKKKKACiiigAooooAKs2N69lP5iAEHhlPcVWopSipKzGm4u6O0t50uYVljOVYUVleG3YwzIfuqwI/H/APVRXjVYck3E9enPngpGfrf/ACE5fov8hVCr+t/8hOX6L/IVQr1qX8OPoeVV+N+oUUUVoQdXov8AyC4f+BfzNZXiL/j9j/65j+ZrV0X/AJBcP/Av/QjWV4i/4/Y/+uY/ma86j/vD+Z31f4C+Rk0UUV6JwBRRRQAUUUUAFFFFABRRRQAUqKzsFUEsTgAd6Suj0IWz226OMCZeHJ5NZVqns481jSlT9pK1yzpVmbK1Ct/rGO5v8KKu0V5EpOTuz1oxUVZHK63/AMhOX6L/ACFUKv63/wAhOX6L/IVQr2KX8OPoeRV+N+oUUUVoQdXov/ILh/H+ZrK8Rf8AH7H/ANcx/M1q6L/yC4fx/mayvEX/AB+x/wDXMfzNedR/3h/M76v8BfIyaKKK9E4AooooAKKKKACiiigAooooAK1/Dm77VLj7uzn654/rWZBby3EgSJCze3aup02xWxt9ucu3LGuXFVEoOPVnThqbc+boi5RRRXlnpHK63/yE5fov8hVCr+t/8hOX6L/IVQr2qX8OPoePV+N+oUUUVoQdXov/ACC4fx/mayvEX/H7H/1zH8zWrov/ACC4fx/mayvEf/H7H/1zH8zXnUf94fzO+r/AXyMmiiivROAKKKKACiiigAooooAKKKKAOs0mRJLCNkRU7MAMcirtUdGhMOnRhuC2W/Or1eJUtzux7FO/IrhRRRUFnK63/wAhOX6L/IVQq/rf/ITl+i/yFUK9ql/Dj6Hj1fjfqFFFFaEHV6N/yC4fx/mayvEf/H5H/wBc/wCprV0b/kFw/j/M1dIB6gV5KqezquXqep7P2lJR9Dh6K7javoPyo2r6D8q3+u/3TD6n5nD0V3G1fQflRtX0H5UfXf7ofU/M4eiu42r6D8qNq+g/Kj67/dD6n5nD0V3G1fQflRtX0H5UfXf7ofU/M4cAnoK1dM0iSZ1kuFKRDnaerf8A1q6PaB2FLUTxcpK0VYuGFSd27h0ooorjOsKKKKAMy80ZLu5aZpWUtjgD2qD/AIR6P/nu/wD3yK2qK2VeolZMydGm3doxf+Eei/57v/3yKX/hHov+e7/kK2aKPrFXuL2FPsQ2luLW2SFWLBc8n65qaiism23dmqVlZBRRRSGFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAf/9k=' as "base64_image",
      'https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png' as "http_image",
      'ftp://demo:password@test.rebex.net/pub/example/pocketftp.png' as "ftp_image",
      '0075678164125' as "barcode_image",
      'ean13' as "barcode_image_type"
    from
      dual
  ) as "data"
from
  dual;

Template​

The template contains the qrcodes with encoded text/tags eg: {%base64_image} and when the base64 of the image is provided in the base64_image then, the qrcode in the templare is replaced by the provided image.

QRCODE image replacing

 template.docx    template.xlsx    template.pptx  

Output​

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

QRCode image replacing

 output.docx    output.xlsx    output.pptx  

Chart​

Available From: v2.0

It is possible to insert charts in Word, Excel and PowerPoint using chart tags denoted by a dollar sign ($) followed by the name of the cursor. The tag is substituted with the corresponding chart, and there are many chart types and options available for insertion using this feature.

Creation of new sheet in Excel.

When the chart tag is used in Excel, a new sheet is created and kept hidden. This is done to store the data used for the created chart.

PowerPoint Note

Please put the chart tag inside a separate text box, otherwise you will get an error.

Simple Chart Query​

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'line' as "type",
          'My Line Chart' as "name",
          cursor(
            select
              'line' as "name",
              'sysDashDotDot' as "lineStyle",
              -- see below for possible values
              'blue' as "color",
              -- can be html/css colors, hex values.
              'square' as "symbol",
              -- can be diamond triangle
              'true' as "smooth",
              -- Can be false
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) as "lines"
        from
          dual
      ) as "linechart"
    from
      dual
  ) as "data"
from
  dual

In the above query, linechart cursor contains all the information to generate the chart. The tag to use in the document would be {$linechart}.

Available Chart Options​

note

The available options can be inserted as a new cursor named options inside the cursor for chart.

All color options are in CSS color format, e.g. 'DeepSkyBlue', '#00bfff', 'rgb(0,191,255)' ...

All style options (available from AOP 20.2.1) are objects:

"defaultStyle": {
    italic: false, // if the text should be in italics
    bold: false, // if the text should be bold
    color: undefined, // the text color, CSS format
    font: undefined // the text font name, e.g. "calibri"
}

The default options are shown below which are subjected to change as per requirements. 

select
    '5486400 / 9525' as "width", -- width of the chart
    '3200400 / 9525' as "height", -- height of the chart
    undefined as "grid", -- deprecated since AOP 20.2.1 (use majorGridlines and minorGridlines options per axis instead), if a grid should be shown
    true as "border", -- if a border should be shown
    false as "roundedCorners", -- From 20.2.1, if chart object should have rounded corners
    undefined as "backgroundColor", -- From 20.2.1, background color for the entire chart
    undefined as "backgroundOpacity", -- From 20.2.1, opacity for the entire chart (see note below)
    undefined as "title", -- the chart title
    cursor (
        select 
            0 as "italic",
            0 as "bold",
            undefined as "color",
            undefined as "font" 
        from dual
    ) as "titleStyle", -- chart title style
    cursor (
        select 
            1 as "showLegend", -- if the legend should be shown
            'r' as "position", -- 'l' for left, 'r' right, 'b' bottom, 't' top
            undefined as "style" -- style for legend text
        from dual
    ) as "legend",
    cursor (
        select 
            false as "showDataLabels", -- can be true or false, default true for pie/pie3d and doughnut
            false as "separator", -- can be either false or anything else for example \n or \t or ; or (, if false)
            false as "showSeriesName", -- include the series name in the data label, true or false
            false as "showCategoryName", -- include the series category name in the data label, true or false
            false as "showLegendKey", -- include the legend key (i.e the color of the series) in the data label, true or false
            false as "showValue", -- include the actual value in the data label
            false as "showPercentage", -- include the percentage, default true for pie/pie3d and doughnut
            'center' as "position" -- position to show the data label, can be 'center', 'left', 'right', 'above', 'below', 'insideBase', 'bestFit', 'outsideEnd', 'insideEnd'. note that not all options might be available for specific charts
        from dual
    ) as "dataLabels",
    cursor (
        select 
        cursor (
            select 
                'minMax' as "orientation", -- or "maxMin"
                undefined as "min", -- a specific number
                undefined as "max", -- a specific number
                undefined as "title", -- title for x-axis, from 19.1.1
                undefined as "titleStyle", -- axis title style
                '0' as "titleRotation", -- text rotation in degrees, clockwise from horizontal axis
                false as "majorGridlines", -- show major grid lines
                undefined as "majorUnit", -- automatic when undefined, spacing between major grid lines and axis values
                false as "minorGridlines", -- show minor grid lines
                undefined as "minorUnit" -- automatic when undefined, spacing between minor grid lines
                true as "showValues", -- options to disable showing the values in axis // From 20.2.1
                cursor (
                    select 
                        0 as "italic",
                        0 as "bold",
                        undefined as "color",
                        undefined as "font" 
                    from dual
                ) as "valuesStyle" -- axis values style
            from dual
        ) as "x",
        cursor (
            select
                'minMax' as "orientation", -- or "maxMin"
                undefined as "min", -- a specific number
                undefined as "max", -- a specific number
                undefined as "type", -- "date" : for date, "number" : for numerical data in x-axis to get linear category axis
                CURSOR (
                    SELECT
                      'unix' AS "format",
                      'mm/yy' AS "code",
                      'months' AS "unit",
                      '1' AS "step"
                    FROM dual
                ) AS "date", -- date options, only for stock charts
                undefined as "title", -- title for x-axis, from 19.1.1
                true as "showValues", -- options to disable showing the values in axis // From 20.2.1
                undefined as "valuesStyle", -- axis values style
                undefined as "titleStyle", -- axis title style
                '-90' as "titleRotation", -- text rotation in degrees, clockwise from horizontal axis
                false as "majorGridlines", -- show major grid lines
                undefined as "majorUnit", -- automatic when undefined, spacing between major grid lines and axis values
                false as "minorGridlines", -- show minor grid lines
                undefined as "minorUnit", -- automatic when undefined, spacing between minor grid lines
                'General' as "formatCode" -- format code for axis data, "General", "Number" ...
            from dual
        ) as "y"
        cursor (
            select
                'minMax' as "orientation", -- or "maxMin"
                undefined as "min", -- a specific number
                undefined as "max", -- a specific number
                undefined as "type", -- "date" : for date, "number" : for numerical data in x-axis to get linear category axis
                CURSOR (
                    SELECT
                      'unix' AS "format",
                      'mm/yy' AS "code",
                      'months' AS "unit",
                      '1' AS "step"
                    FROM dual
                ) AS "date", -- date options, only for stock charts
                undefined as "title", -- title for x-axis, from 19.1.1
                true as "showValues", -- options to disable showing the values in axis // From 20.2.1
                undefined as "valuesStyle", -- axis values style
                undefined as "titleStyle", -- axis title style
                '-90' as "titleRotation", -- text rotation in degrees, clockwise from horizontal axis
                false as "majorGridlines", -- show major grid lines
                undefined as "majorUnit", -- automatic when undefined, spacing between major grid lines and axis values
                false as "minorGridlines", -- show minor grid lines
                undefined as "minorUnit", -- automatic when undefined, spacing between minor grid lines
                'General' as "formatCode" -- format code for axis data, "General", "Number" ...
            from dual
        ) as "y2"
        from dual
    ) as "axis"
from dual
as "options"
note

backgroundOpacity is ignored if backgroundColor is not specified or if backgroundColor is specified in a color space which includes an alpha channel (e.g. rgba(0,191,255,0.5)). In the latter case, the alpha channel in backgroundColor is used.

tip

Please do not get confused with above cursor named defaultOptions, to override above default options you should create new cursor named options inside the cursor used for chart. To create a nested cursor, you can create a cursor named "dataLabels" inside the "options" cursor.

The type of chart should be determined by the "type" key. Cursor for chart should have column name type and column value as 'line', 'bar', 'barStacked', 'columnStacked', 'columnStackedPercent', 'pie', 'pie3d', 'doughnut', 'radar', 'area', 'scatter', 'bubble', 'stock', 'combining charts'.

Line​

This is a normal chart where the data points are connected with lines. Multiple lines can be generated. The chart key should contain an array of line objects. Each line object should contain the data to be connected and the name of the line.

Following structure of data is required.

Line Chart Structure: Inside chart cursor
  • type -> line ("line" for line chart)
  • name -> Name of the chart
  • lines -> Lines of chart
    • line1 -> for the first line
      • name
      • (optional options like lineStyle, color symbol)...
      • data ->data for the first line
        • x
        • y
    • line2 -> for the second line
      • data -> data for the second line
        • x
        • y
  • options
    • width
    • height
    • dataLabels
      • showDataLabels
    • axis
      • x
      • y
note

The available data labels position in line charts are: Center, Left, Right, Above and Below.

Data Source​

Data source of line chart with showDataLabels.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'line' as "type",
          'My Line Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border",
              cursor (
                select
                  'true' as "showDataLabels",
                  'center' as "position",
                  'true' as "showValue"
                from
                  dual
              ) as "dataLabels"
            from
              dual
          ) as "options",
          cursor(
            select
              'line' as "name",
              'sysDashDotDot' as "lineStyle",
              -- see below for possible values
              'blue' as "color",
              -- can be html/css colors, hex values.
              'square' as "symbol",
              -- can be diamond triangle
              'true' as "smooth",
              -- Can be false
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) as "lines"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

The supported values for "lineStyle" are:

line Style

The default "lineStyle" is "solid".

Template​

The template should contain the chart tag, which starts with $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

lineChart output

 output.docx    output.xlsx    output.pptx  

Bar​

In order to generate a bar chart, the chart cursor should contain 'bar' as "type and a cursor named bars and. This cursor must contain the objects with data for the bar and the name of the bar.

bar chart structure
  • 'bar' as "type",
  • bars as cursor
    • name - name of the bar
    • data - data for the bar
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End, Inside Base and Outside End.

Data Source​

Hereby examples of data source for bar chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'bar' as "type",
          'My Bar Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'bar' as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) as "bars"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

bar Image

 output.docx    output.xlsx    output.pptx  

Single Bar Chart​

Data Source​

Hereby examples of data source for single bar chart

select 'file1' as "filename",
    cursor(
        select cursor(
                select 'bar' as "type",
                    cursor(
                        select 'bar 1' as "name",
                            cursor (
                                select 'day 1' as "x",
                                    '4.3' as "y"
                                from dual
                                union all
                                select 'day 2' as "x",
                                    '2.5' as "y"
                                from dual
                                union all
                                select 'day 3' as "x",
                                    '3.5' as "y"
                                from dual
                            ) as "data"
                        from dual
                    ) as "bars"
                from dual
            ) as "chart"
        from dual
    ) as "data"
from dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

When the above data source (which results in a record named chart which has data for single bar) together with the given template is passed to AOP, the output will be as follows.

singleBarChart

 output.docx    output.xlsx    output.pptx  

Bar Stacked​

This is similar to a bar chart but the bars from the same category will be stacked.

Bar Stacked Chart Structure
  • type : 'barStacked' -- type of chart
  • bars : cursor with bars
    • name : name of bar
    • data : data for the bar
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End and Inside Base.

Data Source​

Hereby examples of data source for bar stacked

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'barStacked' as "type",
          'My Bar Stacked Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'bar ' || to_char(nbr) as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) * nbr as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              (
                select
                  1 as nbr
                from
                  dual
                union
                select
                  1.21 as nbr
                from
                  dual
              )
          ) as "bars"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

barStacked

 output.docx    output.xlsx    output.pptx  

Bar Stacked Percent​

This is similar to a bar stacked chart, but the x-axis will be expressed in percentage.

BarStacked Percent Chart Structure
  • type : barStackedPercent,
  • bars : cursor with bars
    • name : name of bar
    • data : data for the bar
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End and Inside Base.

Data Source​

Hereby examples of data source for barStacked chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'barStackedPercent' as "type",
          'My Bar Stacked Percent Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'bar ' || to_char(nbr) as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) * nbr as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              (
                select
                  1 as nbr
                from
                  dual
                union
                select
                  1.21 as nbr
                from
                  dual
              )
          ) as "bars"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

barStackedPercent

 output.docx    output.xlsx    output.pptx  

Column​

This will produce a normal column chart. The chart object should contain an array named columns with objects containing the data and name of the column.

column Chart Structure
  • type : column,
  • columns : cursor with columns
    • name : name of column
    • data : data for the column
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End, Inside Base, and Outside End.

Data Source​

Hereby examples of data source column chart.

select
    'file1' as "filename",
    cursor(
        select
            cursor(
                select
                    'column' as "type",
                    'My Column Chart' as "name",
                    cursor (
                        select
                            576 as "width",
                            336 as "height",
                            'Chart Title' as "title",
                            'true' as "grid",
                            'true' as "border"
                        from
                            dual
                    ) as "options",
                    cursor(
                        select
                            'column ' || to_char(nbr) as "name",
                            cursor (
                                select
                                    c.cust_first_name || ' ' || c.cust_last_name as "x",
                                    sum(o.order_total) * nbr as "y"
                                from
                                    aop_sample_customers c,
                                    aop_sample_orders o
                                where
                                    c.customer_id = o.customer_id
                                group by
                                    c.cust_first_name || ' ' || c.cust_last_name
                                order by
                                    c.cust_first_name || ' ' || c.cust_last_name
                            ) as "data"
                        from
                            (
                                select
                                    1 as nbr
                                from
                                    dual
                                union
                                select
                                    1.21 as nbr
                                from
                                    dual
                            )
                    ) as "columns"
                from
                    dual
            ) as "chart"
        from
            dual
    ) as "data"
from
    dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

column

 output.docx    output.xlsx    output.pptx  

Column Stacked​

This will produce a column stacked chart. The chart object should contain an array named columns with objects containing the data and the name of the column.

columnStacked Chart Structure
  • type : columnStacked,
  • columns : cursor with columns
    • name : name of column
    • data : data for the column
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End and Inside Base.

Data Source​

Hereby examples of data source column chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'columnStacked' as "type",
          'My Column Stacked Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'column ' || to_char(nbr) as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) * nbr as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              (
                select
                  1 as nbr
                from
                  dual
                union
                select
                  1.21 as nbr
                from
                  dual
              )
          ) as "columns"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

columnStacked

 output.docx    output.xlsx    output.pptx  

Column Stacked Percent​

This will produce a column stacked chart. The chart object should contain an array named columns with objects containing the data and the name of the column.

columnStacked Percent Chart Structure
  • type : columnStackedPercent,
  • columns : cursor with columns
    • name : name of column
    • data : data for the column
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End and Inside Base.

Data Source​

Hereby examples of data source column chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'columnStackedPercent' as "type",
          'My Column Stacked Percent Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'column ' || to_char(nbr) as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) * nbr as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              (
                select
                  1 as nbr
                from
                  dual
                union
                select
                  1.21 as nbr
                from
                  dual
              )
          ) as "columns"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

columnStackedPrecent

 output.docx    output.xlsx    output.pptx  

Pie​

Available From: v3.3

This will produce a pie chart. The chart object should contain an array named pies with one element containing the data and name of the pie chart.

pie chart Structure
  • type : pie,
  • pies : cursor with pies
    • name : name of pie
    • data : data for the pie
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End, Outside End, and Best Fit.

Data Source​

Hereby examples of data source pie chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'pie' as "type",
          'My Pie Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'pie' as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) as "pies"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Pie3d​

Available From: v3.3

This will produce a pie3d chart. The chart object should contain an array named pies with one element containing the data and name of the pie chart.

pie3d chart Format
  • type : pie3d,
  • pies : cursor with pies
    • name : name of pie
    • data : data for the pie
      • x
      • y
note

The available data labels position in line charts are: Center, Inside End, Outside End, and Best Fit.

Data Source​

Hereby examples of data source pie3d chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'pie3d' as "type",
          'My Pie3d Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'pie' as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) as "pies"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Doughnut​

Available From: v3.3

This will produce a doughnut chart, similar to the pie chart. The chart object should contain an array named "doughnuts" with one object containing the data and name of the doughnut chart.

doughnut chart Structure
  • type : doughnut,
  • doughnuts : cursor with doughnuts
    • name : name of doughnut
    • data : data for the doughnut
      • x
      • y

Data Source​

Hereby examples of data source doughnut chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'doughnut' as "type",
          'My doughnut Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'pie' as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) as "doughnuts"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Radar​

Available From: v3.3

This will produce a radar chart. The chart object should contain an array named radars with one element containing the data and name of the radar chart.

radar chart Structure
  • type : radar,
  • radars : cursor with radars
    • name : name of radar
    • data : data for the radar
      • x
      • y

Data Source​

Hereby examples of data source radar chart.

select 'file1' as "filename",
       cursor (
         select cursor (
           select 'radar'          as "type",
                  'My Radar Chart' as "name",
                  cursor (
                    select 576           as "width",
                           336           as "height",
                           'Radar Chart' as "title"
                    from dual
                  )                as "options",
                  cursor (
                    select 'Order Totals' as "name",
                           cursor (
                             select 'Order ' || order_id as "x",
                                    order_total          as "y"
                             from aop_sample_orders
                           )              as "data"
                    from dual
                  )                as "radars"
           from dual
         ) as "chart"
         from dual
       )       as "data"
from dual;

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Area​

Available From: v3.3

This will produce an area chart. The chart object should contain an array named areas with one element containing the data and name of the area chart.

area chart Structure
  • type : area,
  • areas : cursor with areas
    • name : name of area
    • data : data for the area
      • x
      • y

Data Source​

Hereby examples of data source area chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'area' as "type",
          'My Area Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border"
            from
              dual
          ) as "options",
          cursor(
            select
              'area' as "name",
              cursor (
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "x",
                  sum(o.order_total) as "y"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) as "areas"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Scatter​

Available From: v3.3

This will produce a scatter chart. The chart object should contain an array named scatters with one element containing the data and name of the scatter chart. It is similar to the previous charts, however the x-axis should only contain numbers.

scatter chart Structure
  • type : scatter,
  • scatters : cursor with scatters
    • name : name of scatter
    • data : data for the scatter
      • x : should only contain numbers
      • y
note

The available data labels position in line charts are: Center, Left, Right, Above and Below.

Data Source​

Hereby examples of data source scatter chart.

select
    'file1' as "filename",
    cursor(
        select
            cursor(
                select
                    'scatter' as "type",
                    'My Scatter Chart' as "name",
                    cursor (
                        select
                            576 as "width",
                            336 as "height",
                            'Chart Title' as "title",
                            'true' as "grid",
                            'true' as "border"
                        from
                            dual
                    ) as "options",
                    cursor(
                        select
                            'scatter ' as "name",
                            cursor (
                                select
                                    null as link,
                                    c.cust_first_name || ' ' || c.cust_last_name as "label",
                                    c.customer_id as "x",
                                    sum(o.order_total) as "y"
                                from
                                    aop_sample_customers c,
                                    aop_sample_orders o
                                where
                                    c.customer_id = o.customer_id
                                group by
                                    c.customer_id,
                                    c.cust_first_name || ' ' || c.cust_last_name
                                order by
                                    c.customer_id
                            ) as "data"
                        from
                            dual
                    ) as "scatters"
                from
                    dual
            ) as "chart"
        from
            dual
    ) as "data"
from
    dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Bubble​

Available From: v3.3

This will produce a bubble chart. The chart object should contain an array named bubbles with one element containing the data and the name of the bubble chart. It is similar to previous charts however, the data points can now contain a size value to determine the size of a bubble.

bubble chart Structure
  • type : bubble,
  • bubbles : cursor with bubbles
    • name : name of bubble
    • data : data for the bubble
      • x
      • y
      • size
note

The available data labels position in line charts are: Center, Left, Right, Above and Below.

Data Source​

Hereby examples of data source bubble chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'bubble' as "type",
          'My Bubble Chart' as "name",
          cursor (
            select
              576 as "width",
              336 as "height",
              'Chart Title' as "title",
              'true' as "grid",
              'true' as "border",
              cursor(
                select
                  cursor(
                    select
                      'title for x axis' as "title"
                    from
                      dual
                  ) as "x"
                from
                  dual
              ) as "axis"
            from
              dual
          ) as "options",
          cursor(
            select
              'bubble ' as "name",
              cursor (
                select
                  null as link,
                  customer_id as "label",
                  customer_id as "x",
                  sum(order_total) as "y",
                  10000 / sum(order_total) as "size"
                from
                  aop_sample_orders o
                where
                  rownum < 8
                group by
                  customer_id
              ) as "data"
            from
              dual
          ) as "bubbles"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Stock​

Available From: v3.3

The chart object should contain an array named stocks with one element containing the data and the name of the stock chart. Here instead of an y-value: the volume, open, close, high and low value should be given. The format of the x-axis is optional.

The x-axis contains the starting date using 1900 notation, i.e. 1 will represent January 1 1900 more info.

stock chart Structure
  • type : stock,
  • stocks : cursor with stocks
    • name : name of stock
    • data : data for the stock
      • volume
      • open
      • close
      • high
      • low
      • x
note

The available data labels position in line charts are: Center, Left, Right, Above and Below.

Data Source​

Hereby examples of data source bubble chart.

select
    'file1' as "filename",
    cursor(
        select
            cust_first_name as "cust_first_name",
            cust_last_name as "cust_last_name",
            cursor(
                select
                    'stock' as "type",
                    'Stocks Chart' as "name",
                    cursor(
                        select
                            'stock1' as "name",
                            cursor(
                                select
                                    '1' as "x",
                                    '70' as "volume",
                                    '44' as "open",
                                    '55' as "high",
                                    '11' as "low",
                                    '25' as "close"
                                from
                                    dual
                                union
                                all
                                select
                                    '2' as "x",
                                    '120' as "volume",
                                    '25' as "open",
                                    '57' as "high",
                                    '12' as "low",
                                    '38' as "close"
                                from
                                    dual
                                union
                                all
                                select
                                    '3' as "x",
                                    '150' as "volume",
                                    '38' as "open",
                                    '57' as "high",
                                    '13' as "low",
                                    '50' as "close"
                                from
                                    dual
                                union
                                all
                                select
                                    '4' as "x",
                                    '135' as "volume",
                                    '50' as "open",
                                    '58' as "high",
                                    '11' as "low",
                                    '35' as "close"
                                from
                                    dual
                                union
                                all
                                select
                                    '5' as "x",
                                    '148' as "volume",
                                    '34' as "open",
                                    '58' as "high",
                                    '25' as "low",
                                    '43' as "close"
                                from
                                    dual
                            ) as "data"
                        from
                            dual
                    ) as "stocks"
                from
                    dual
            ) as "chart"
        from
            aop_sample_customers
        where
            customer_id = 8
    ) as "data"
from
    dual

Data Source​

Hereby examples of data source for stock chart.

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Combining Charts​

Available From: v3.3

It is possible to combine multiple charts. For example, a column and a line chart. If a second value axis is wished on the right side, then the values should be given as y2 instead of y. The type should be "multiple" and the chart should contain a multiples array which contains the different charts defined previously. It is possible to combine more than 2 types of chart but there can only be two value axes. An example is given below:

multiple chart Structure
  • type : multiple,
  • multiples : cursor with multiples
    • type : type of chart
    • name : name of of chart
    • cursor respective to type of chart (lines, bars, columns, pies, doughnuts etc)
      • name
      • data
        • based on the type of chart (x, y , size etc.)

Data Source​

Hereby examples of data source for combining multiple chart. In this case column and line chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'multiple' as "type",
          'My Combo: Column with Line Chart' as "name",
          cursor(
            select
              576 as "width",
              336 as "height",
              'Title' as "title",
              'true' as "grid",
              'true' as "border",
              cursor(
                select
                  'true' as "showLegend",
                  'r' as "position"
                from
                  dual
              ) as "legend"
            from
              dual
          ) as "options",
          cursor(
            select
              tp as "type",
              cursor(
                select
                  nm as "name",
                  cursor(
                    select
                      to_char(o.order_timestamp, 'MON RRRR') "label",
                      to_char(o.order_timestamp, 'MON RRRR') "x",
                      sum (
                        decode(p.category, nm, oi.quantity * oi.unit_price, 0)
                      ) "y"
                    from
                      aop_sample_product_info p,
                      aop_sample_order_items oi,
                      aop_sample_orders o
                    where
                      oi.product_id = p.product_id
                      and o.order_id = oi.order_id
                    group by
                      to_char(o.order_timestamp, 'MON RRRR'),
                      to_char(o.order_timestamp, 'RRRR MM')
                    order by
                      to_char(o.order_timestamp, 'RRRR MM')
                  ) as "data"
                from
                  (
                    select
                      'Mens' nm
                    from
                      dual
                    union
                    all
                    select
                      'Womens' nm
                    from
                      dual
                  )
                where
                  tp = 'column'
              ) as "columns",
              cursor(
                select
                  nm as "name",
                  cursor(
                    select
                      to_char(o.order_timestamp, 'MON RRRR') "label",
                      to_char(o.order_timestamp, 'MON RRRR') "x",
                      sum (
                        decode(p.category, nm, oi.quantity * oi.unit_price, 0)
                      ) "y"
                    from
                      aop_sample_product_info p,
                      aop_sample_order_items oi,
                      aop_sample_orders o
                    where
                      oi.product_id = p.product_id
                      and o.order_id = oi.order_id
                    group by
                      to_char(o.order_timestamp, 'MON RRRR'),
                      to_char(o.order_timestamp, 'RRRR MM')
                    order by
                      to_char(o.order_timestamp, 'RRRR MM')
                  ) as "data"
                from
                  (
                    select
                      'Accessories' nm
                    from
                      dual
                  )
                where
                  tp = 'line'
              ) as "lines"
            from
              (
                select
                  'column' tp
                from
                  dual
                union
                all
                select
                  'line' tp
                from
                  dual
              )
          ) as "multiples"
        from
          dual
      ) as "chart"
    from
      dual
  ) as "data"
from
  dual

Template​

The template should contain the chart tag, which starts $ followed by cursor name inside delimiters. For example we have the template with following content:

{$chart}

 template.docx    template.xlsx    template.pptx  

Output​

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

pieChart

 output.docx    output.xlsx    output.pptx  

Chart Templating​

Available From: v20.3

Since AOP 20.3, there is another way to create charts. You can provide a chart in the template and mark it with a preceding
{aopchart chartData} where aopchart is the tag identifier and chartData is the cursor in the data which contains information about the chart. Using this method, the chart in the template can be styled through MS Office or LibreOffice as an alternative to passing the style options as a part of the input data. This allows the use of style options we do not support, but moves the chart styling from the data to the template (e.g. loops containing a chart with different style on each iteration would not be possible using this tag). The data is specified per series and all options are given in the example below.

Line Chart Structure: Inside chart cursor
  • title : title of new chart,
  • xAxis : For x axis.
    • data : data of the x- axis. Can be an simple array or record with columns
    • yAxis : for y axis
      • series : records with series.
        • name : name of the series
        • data : data for the series
Excel Chart Info

When using chart templating, the data for the chart is in another sheet and that sheet will be hidden after processing.

Multiple Chart

If there are multiple chart templating tag, AOP will choose the closest dummy chart for the chart templating tag. This implies for Word, PowerPoint and Excel.

multiple chart templating

Data source in case of Multiple Charts in Excel

For the multiple charts using AOP Chart Templating, the data source for each charts should be in seperate sheets.

Data Source in case of multiple charts

Here, the data source for first chart is in sheet1 and for second chart is in second sheet.

Example Simple Chart​

Chart templating can be used if we can not style the chart by using the options mentioned in AOP.

Lets see a example for line chart using AOP chart templating.

Data Source​

Hereby examples of data source for chart templating for line chart.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
        select
          'Simple Line Chart' as "title",
          cursor (
            select
              cursor(
                select
                  c.cust_first_name || ' ' || c.cust_last_name as "value"
                from
                  aop_sample_customers c,
                  aop_sample_orders o
                where
                  c.customer_id = o.customer_id
                group by
                  c.cust_first_name || ' ' || c.cust_last_name
                order by
                  c.cust_first_name || ' ' || c.cust_last_name
              ) as "data"
            from
              dual
          ) "xAxis",
          cursor(
            select
              cursor(
                select
                  'aop series ' || to_char(nbr) as "name",
                  cursor(
                    select
                      sum(o.order_total) * nbr as "value"
                    from
                      aop_sample_customers c,
                      aop_sample_orders o
                    where
                      c.customer_id = o.customer_id
                    group by
                      c.cust_first_name || ' ' || c.cust_last_name
                    order by
                      c.cust_first_name || ' ' || c.cust_last_name
                  ) as "data"
                from
                  (
                    select
                      1 as nbr
                    from
                      dual
                    union
                    select
                      1.5 as nbr
                    from
                      dual
                  )
              ) "series"
            from
              dual
          ) as "yAxis"
        from
          dual
      ) "simpleChartData"
    from
      dual
  ) as "data"
from
  dual
note

the x-axis gets one nameless series, directly as "data" instead of in a "series" cursor. The names of the series are those used as legend entries.

Template​

The template should contain the chart templating tag, which starts aopchart followed by cursor name inside delimiters and a respective chart with all the styling. For example we have the template with following content:

Chart Tempalting

and data for chart is:

Chart Templating Data

The data for the above chart in another sheet.

 template.docx    template.xlsx    template.pptx  

Output​

When the above data source (which results in a cursor name chart with data for chart inside it.) together with the given template is passed to AOP, the output will be as follows.

Chart Templating_output

 output.docx    output.xlsx    output.pptx  

If more series are provided in the data than are present in the template, the extra series from the data are ignored. If there are more series in the template, the extra series from the template are removed in the output file.

It is possible to use both the secondary x-axis and the secondary y axis. The secondary x and y axes can only receive a title in the data. The secondary y-axis can be utilized by assigning the corresponding series to the y-axis in the template.

Example Bar chart​

Lets see one example below to implement chart templating for bar chart.

Data Source​

Hereby examples of data source for chart templating.

select
    'file1' as "filename",
    cursor(
        select
            cursor(
                select
                    'bar chart with secondary axes' as "title",
                    cursor (
                        select
                            'aop x ax title' as "title",
                            cursor(
                                select
                                    c.cust_first_name || ' ' || c.cust_last_name as "value"
                                from
                                    aop_sample_customers c,
                                    aop_sample_orders o
                                where
                                    c.customer_id = o.customer_id
                                group by
                                    c.cust_first_name || ' ' || c.cust_last_name
                                order by
                                    c.cust_first_name || ' ' || c.cust_last_name
                            ) as "data"
                        from
                            dual
                    ) "xAxis",
                    cursor(
                        select
                            'aop y ax title' as "title",
                            cursor(
                                select
                                    'aop series ' || to_char(nbr) as "name",
                                    cursor(
                                        select
                                            sum(o.order_total) * nbr as "value"
                                        from
                                            aop_sample_customers c,
                                            aop_sample_orders o
                                        where
                                            c.customer_id = o.customer_id
                                        group by
                                            c.cust_first_name || ' ' || c.cust_last_name
                                        order by
                                            c.cust_first_name || ' ' || c.cust_last_name
                                    ) as "data"
                                from
                                    (
                                        select
                                            1 as nbr
                                        from
                                            dual
                                        union
                                        select
                                            2 as nbr
                                        from
                                            dual
                                        union
                                        select
                                            3 as nbr
                                        from
                                            dual
                                    )
                            ) "series",
                            cursor(
                                select
                                    'aop x2 ax title' as "title"
                                from
                                    dual
                            ) "x2Axis",
                            cursor(
                                select
                                    'aop y2 ax title' as "title"
                                from
                                    dual
                            ) "y2Axis"
                        from
                            dual
                    ) as "yAxis"
                from
                    dual
            ) "simpleChartDataWithSecondaryAxes"
        from
            dual
    ) as "data"
from
    dual

Template​

The template should contain the chart templating tag, which starts aopchart followed by cursor name inside delimiters and a respective chart with all the styling. For example we have the template with following content:

Chart Tempalting

and data for chart is:

Chart Templating Data

The data for the above chart in another sheet.

 template.docx    template.xlsx    template.pptx  

Chart Info

When using chart templating, the data for the chart is in another sheet and that sheet will be hidden after processing.

Output​

When the above data source (which results in a cursor name chart with data for chart inside it.) together with the given template is passed to AOP, the output will be as follows.

Chart Templating_output

 output.docx    output.xlsx    output.pptx  

Note that there is not much use in showing a secondary x-axis, since it shows the same values with a different title.

note

When replacing titles, both for the chart title and the axis titles, it is recommended to edit them to something else than the default values in the template. This is because, in MS Office, when the title is at its unchanged default value, neither the title text nor its styling is actually included in the document yet. The actual styling is thus still up for interpretation and the title will look different in MS Office and LibreOffice. If the titles are the same as their default value, we generate the same title styling as the one we use by default for the {$chart} tag.

Example Stock Chart​

If the x-axis contains dates in the template and receives an additional date object in the data, its series are treated as a series of dates. These should match an ISO 8601 format, the RFC 2822 Date time format or UNIX epoch time. This date object contains:

  • "format": the format to display the date on the chart's axis.
  • "unit": the unit to be used for spacing the axis values.
  • "step": how many of the above unit should be used for spacing the axis values (automatic if undefined). This option is not supported in LibreOffice.

The unit cannot be determined automatically. E.g. if the unit was not specified as "days" in the data below, the result would just show a single bar for May 1999, since the unit in the template is clearly "months".

Data Source​

Hereby examples of data source for chart templating.

return q'[
[
  {
    "data": [
      {
        "stockChartData": {
          "title": "stock chart example (dates on x-axis)",
          "xAxis": {
            "date": { "format": "d/m/yyyy", "unit": "days", "step": "1" },
            "data": [
              "1999-05-16",
              "1999-05-17",
              "1999-05-18",
              "1999-05-19",
              "1999-05-20"
            ]
          },
          "yAxis": {
            "series": [
              { "name": "volume", "data": [148, 135, 150, 120, 70] },
              { "name": "open", "data": [34, 50, 38, 25, 44] },
              { "name": "high", "data": [58, 58, 57, 57, 55] },
              { "name": "low", "data": [25, 11, 13, 12, 11] },
              { "name": "close", "data": [43, 35, 50, 38, 25] }
            ]
          }
        }
      }
    ]
  }
]
]';

Template​

The template should contain the chart templating tag which starts aopchart followed by cursor name inside delimiters and a respective chart with all the styling. For example we have the template with following content:

Chart Tempalting

and data for chart is:

Chart Templating Data

Note that the data for the above chart in another sheet.

 template.docx    template.xlsx    template.pptx  

Output​

When the above data source (which results in a cursor name chart with data for chart inside it.) together with the given template is passed to AOP, the output will be as follows.

Chart Templating_output

 output.docx    output.xlsx    output.pptx  

Hidden Excel Sheet

Note In Excel: the sheet with data is hidden in output.

D3 Images​

Available From: v18.1

You can insert a d3 image in your report using the d3 tag, which follows the syntax $d3 followed by the column with the javascript d3 code within delimiters (for ex {$d3 test} considering test has javascript d3 code). When AOP runs the code for the d3 image, it will replace the tag with the resulting image.

If your SQL query includes :

'javascript code for d3' as "test",

Then use the d3 code in a template as:

{$d3 test}

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. Let's take an example for the implementation of a d3 image.

Data Source​

Hereby are examples of data sources for different options.

We have used the following js code for the implementation of d3:

// Based on https://bl.ocks.org/mbostock/7f5f22524bd1d824dd53c535eda0187f
const d3 = require("d3");
const D3Node = require("d3-node")
const d3n = new D3Node()

const svg = d3n.createSVG(512, 512);
const [width, height] = [512, 512];
const margin = { top: 20, right: 30, bottom: 30, left: 40 };

var x = d3.scaleLog().domain([2e-1, 5e0])
    .rangeRound([margin.left, width - margin.right]);
var y = d3.scaleLog().domain([3e2, 2e4])
    .rangeRound([height - margin.bottom, margin.top]);
var color = d3.scaleSequential(d3.interpolateYlGnBu).domain([0, 1.8]); // Points per square pixel.

svg.append("g")
    .attr("transform", "translate(0," + (height - margin.bottom) + ")")
    .call(d3.axisBottom(x).ticks(null, ".1f"))
    .select(".tick:last-of-type text")
    .select(function () { return this.parentNode.appendChild(this.cloneNode()); })
    .attr("y", -3).attr("dy", null)
    .attr("font-weight", "bold").text("Carats");

svg.append("g")
    .attr("transform", "translate(" + margin.left + ",0)")
    .call(d3.axisLeft(y).ticks(null, ".1s"))
    .select(".tick:last-of-type text")
    .select(function () { return this.parentNode.appendChild(this.cloneNode()); })
    .attr("x", 3).attr("text-anchor", "start")
    .attr("font-weight", "bold").text("Price (USD)");

d3.csv("https://raw.githubusercontent.com/tidyverse/ggplot2/main/data-raw/diamonds.csv", d => ({ carat: +d.carat, price: +d.price }), (error, diamonds) => {
    if (error) throw error;
    if (diamonds[0] instanceof Error) throw diamonds[0];
    svg.insert("g", "g").attr("fill", "none")
        .attr("stroke", "#000").attr("stroke-width", 0.5)
        .attr("stroke-linejoin", "round").selectAll("path")
        .data(d3.contourDensity()
            .x(d => x(d.carat)).y(d => y(d.price))
            .size([width, height]).bandwidth(10)
            (diamonds))
        .enter().append("path")
        .attr("fill", d => color(d.value))
        .attr("d", d3.geoPath());
    finish(d3n);
});

Should a field "test_data": [1,2,3] be present in the JSON (at the same place as "test": "CODE"), then the code will have access to the global data which would be [1,2,3], or whatever "test_data" is. Strings, booleans, numbers, and objects are also possible.

select
  'file1' as "filename",
  cursor (
    select
      c.cust_first_name as "cust_first_name",
      c.cust_last_name as "cust_last_name",
      '// Based on https://bl.ocks.org/mbostock/7f5f22524bd1d824dd53c535eda0187f
const d3 = require("d3");
const D3Node = require("d3-node")
const d3n = new D3Node()

const svg = d3n.createSVG(512, 512);
const [width, height] = [512, 512];
const margin = { top: 20, right: 30, bottom: 30, left: 40 };

var x = d3.scaleLog().domain([2e-1, 5e0])
  .rangeRound([margin.left, width - margin.right]);
var y = d3.scaleLog().domain([3e2, 2e4])
  .rangeRound([height - margin.bottom, margin.top]);
var color = d3.scaleSequential(d3.interpolateYlGnBu).domain([0, 1.8]); // Points per square pixel.

svg.append("g")
  .attr("transform", "translate(0," + (height - margin.bottom) + ")")
  .call(d3.axisBottom(x).ticks(null, ".1f"))
  .select(".tick:last-of-type text")
  .select(function () { return this.parentNode.appendChild(this.cloneNode()); })
  .attr("y", -3).attr("dy", null)
  .attr("font-weight", "bold").text("Carats");

svg.append("g")
  .attr("transform", "translate(" + margin.left + ",0)")
  .call(d3.axisLeft(y).ticks(null, ".1s"))
  .select(".tick:last-of-type text")
  .select(function () { return this.parentNode.appendChild(this.cloneNode()); })
  .attr("x", 3).attr("text-anchor", "start")
  .attr("font-weight", "bold").text("Price (USD)");

d3.csv("https://raw.githubusercontent.com/tidyverse/ggplot2/main/data-raw/diamonds.csv", d => ({ carat: +d.carat, price: +d.price }), (error, diamonds) => {
  if (error) throw error;
  if (diamonds[0] instanceof Error) throw diamonds[0];
  svg.insert("g", "g").attr("fill", "none")
    .attr("stroke", "#000").attr("stroke-width", 0.5)
    .attr("stroke-linejoin", "round").selectAll("path")
    .data(d3.contourDensity()
      .x(d => x(d.carat)).y(d => y(d.price))
      .size([width, height]).bandwidth(10)
      (diamonds))
    .enter().append("path")
    .attr("fill", d => color(d.value))
    .attr("d", d3.geoPath());
  finish(d3n);
});' as "d3_image_code",
      500 as "d3_image_code_width",
      500 as "d3_image_code_height"
    from
      aop_sample_customers c
    where
      c.customer_id = 1
  ) as "data"
from
  dual;

Template​

The template should contain the d3 image tag which starts with $d3 followed by the column name inside delimiters. For example, we have the template with the following content:

{$d3 d3_image_code}

 template.docx    template.xlsx    template.pptx  

Output​

When the above data source (which results in several columns including a column name d3_image_code with the value of d3 javascript code) together with the given template is passed to AOP, the output will be as follows.

d3 image

 output.docx    output.xlsx    output.pptx  

Code structure​

This is how the code is supposed to be organized:

// Import D3
const d3 = require('d3');
// Import D3-Node
const D3Node = require('d3-node');
// Instantiate a new D3Node, which will provide the SVG and will be required to finish
const d3n = new D3Node();
// Create an SVG (instead of selecting one from the (here non-existing) document)
const svg = d3n.createSVG(512, 512); // Different sizes are possible
// Start working on the SVG
svg.append('g').text('Test');
//  etc
// We can access the _data field if it was given:
svg.append('g').text(data ? data.toString() : 'No data passed!');
// When the SVG is finished, call the global finish() with the used D3Node object
finish(d3n);
// We can also call the global fail() (or throw an error) should something fail:
fail(new Error('We are missing something!'));

Specifications and limitations​

  • The version of the d3 library is 4.12.0
  • d3-contour and d3-scale-chromatic (along with all default 4.12.0 submodules) are preloaded, e.g. d3.contour() is available
  • The code is run in an async environment, meaning await is available
  • The code has to either have a return statement with a D3Node object or call finish() with one
  • After the code has successfully finished, the SVG will be extracted from the D3Node, converted to PNG, and pasted in Word/Excel/...
  • ES2015, ES2016, and ES2017 are supported and run inside a Node environment
  • Only d3 libraries (including sub-libraries like d3-polygon) can be returned by require()
  • d3-request can only use http:// and https:// URLs
  • There are time limitations, e.g. the whole script will time out after the 30s, even when it's still fetching data over HTTP(S)
  • All errors are caught and will cause the whole PrintJob to fail. The stack trace will be shown

Created / Modified Date of Document​

Available From: v22.2

It is also possible to set the created and modified date of the processed output file by using the g_output_created_date and g_output_modified_date respectively. The datetime format must be in ISO format (Example: "2022-02-07T12:55:12") or in the date time format ("YYYY-MM-DD HH:mm:ss", "YYYY-MM-DD").

Example​

Data Source​

To set the created and modified date, following Init PL/SQL Code is used.

aop_api_pkg.g_output_created_date:='2021-02-21';
aop_api_pkg.g_output_modified_date:='2021-03-23';

Here are the different options for example data sources:

SELECT 'output.docx' as "filename",
    cursor (
        select 'Thank you for using AOP' as text 
        from dual
    )as "data" 
from dual

Template​

The created and modified date properties of the template is as shown below:

Also, the template is consist of following contents.

 template.docx    template.pptx  

Output​

Upon processing, AOP provides the given output file with the created and modified date as passed in the Init PL/SQL code.

 output.docx    output.pptx  

Available Template Types

Currently, PPTX and DOCX templates are supported for setting output created and modified date.