TEXT Templates

Introduction

This section will go through all the tags that are available for text template, together with the appropriate data selection query. In this documentation, curly braces are used as delimiters for the tags. Please check general template for how to change the delimiters delimiters. The files should be in .txt format.

Tag Overview

The following tables shows the available tags in cvs template. The three dot in the format column shows what is variable, they should be either replaced by cursor name or column name.

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)

Tag Name	Format	Tag	Short Description
Normal Substitution	{...}	{normal}	Normal Substitution, the data from the given column will be replaced.
Object Scoping	{#{.. . : ...\}} {/}	{#{key:value\}} … {/}	Tag that support object scoping.
Loop Tags	{#...} {/...}	{#data_loop} … {/data_loop}	Tag that loops over the given cursor name and repeats everything in between the tags for each record by creating new row(s).

tip

Special Tags: Other special tags can be used. Please refer to the general template section: special-tags

Normal Substitution

Available From: v1.0

These kind of tags are the simplest tag to use. These tags are enclosed in curly braces and can include variables that will be replaced with actual data when the output is generated.

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.

Date Source

Hereby example data source for different options.

SQL

select 'file1' as "filename",
       cursor (
           select c.cust_first_name as "cust_first_name",
                  c.cust_last_name  as "cust_last_name"
           from aop_sample_customers c
           where c.customer_id = 1
       )       as "data"
from dual;

PL/SQL returning SQL

declare l_return clob;

begin l_return := q '[
        select
            ' file1 ' as "filename",
            cursor (
                select
                    c.cust_first_name as "cust_first_name",
                    c.cust_last_name as "cust_last_name"
                    from aop_sample_customers c
                    where c.customer_id = 1
            ) as "data"
        from dual
        ]';

return l_return;

end;

PL/SQL returning JSON

declare l_cursor sys_refcursor;
l_return clob;
begin apex_json.initialize_clob_output(dbms_lob.call, true, 2);
open l_cursor for
select
  'file1' as "filename",
  cursor (
    select
      c.cust_first_name as "cust_first_name",
      c.cust_last_name as "cust_last_name"
    from
      aop_sample_customers c
    where
      c.customer_id = 1
  ) as "data"
from
  dual;
apex_json.write(l_cursor);
l_return := apex_json.get_clob_output;

/*
 l_return can also be a varchar2:
 l_return := q'[
  [
    {
    "filename": "file1",
    "data": [
          {
          "cust_first_name": "John",
          "cust_last_name": "Dulles"
          }
      ]
    }
  ]
 ]';
 */
return l_return;

end;

JSON

[
  {
    "filename": "file1",
    "data": [
      {
        "cust_first_name": "John",
        "cust_last_name": "Dulles"
      }
    ]
  }
]

Template

The template should contain the name of the columns that were provided in the query above. For example we have the template with following content:

Hello {cust_first_name} {cust_last_name},

This is the basic example of using normal tags.
Thank you for choosing AOP.

Best Regards,
AOP Team

Output

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

Hello John Dulles,

This is the basic example of using normal tags.
Thank you for choosing AOP.

Best Regards,
AOP Team

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.

AOP(APEX Office Print) gives support to different operations such as Mathematical operations, String operations, Number/BigNumber operations, Conditional and Comparison operations, Array operations, Number formatting and Date formatting.

REFERENCE

For more details see Simple Angular Expression

Object Scoping

AOP allows you to create object and scope through object in text template. The object can be created by using opening tag {#{key:value\}} and closing tag {/}, the nested object can be created in similar way. The object value can only be accessed within the scope of opening and closing tag. For reference you can follow example mentioned below:

caution

If object is accessed outside the scope of tag then empty string is returned.

Example

Template

The template should contain the declaration of an object and the same object's value can be accessed by object scoping. For example we have the template with following content:

Simple Object Scoping : {#{x:1\}} 
    X:{x} {/}

We should be able to scope objects:{#{a:1,b:2,c:{d:123\}\}}
    A: {a} B: {b} C: {c} C.D: {c.d}
    See if we can scope further: {#c}{d}{/c} {/}

Output

When the given template is passed to AOP, the output will be as follows.

Simple Object Scoping :  
    X:1 

We should be able to scope objects:
    A: 1 B: 2 C:  C.D: 123
    See if we can scope further: 123 

Loop Tags

Available From v1.0

AOP allows you to loop through a record by using the loop tag, which has two parts: the start of the loop and the end of the loop. The start of the loop is denoted by a # symbol followed by the record name enclosed in delimiters, such as {#record1}. The end of the loop is denoted by a / symbol followed by the same record name enclosed in delimiters, such as {/record1}.

Ex:

info

{#record}start

Contents of loop and other

end{/record}

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.

SQL

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;

PL/SQL returning SQL

declare
    l_return clob;
begin
    l_return := q'[
        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
        ]';
    return l_return;
end;

PL/SQL returning JSON

declare
  l_cursor sys_refcursor;
  l_return clob;
begin
  apex_json.initialize_clob_output(dbms_lob.call, true, 2) ;
  open l_cursor for
  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
  apex_json.write(l_cursor) ;
  l_return := apex_json.get_clob_output;
  return l_return;
end;

JSON

[
  {
    "filename": "file1",
    "data": [
      {
        "cust_first_name": "John",
        "cust_last_name": "Dulles",
        "cust_city": "Sterling",
        "orders": [
          {
            "order_total": 2380,
            "order_name": "Order 1",
            "product": [
              {
                "product_name": "Business Shirt",
                "quantity": 3,
                "unit_price": 50
              },
              {
                "product_name": "Trousers",
                "quantity": 3,
                "unit_price": 80
              },
              {
                "product_name": "Jacket",
                "quantity": 3,
                "unit_price": 150
              },
              {
                "product_name": "Blouse",
                "quantity": 3,
                "unit_price": 60
              },
              {
                "product_name": "Skirt",
                "quantity": 3,
                "unit_price": 80
              },
              {
                "product_name": "Ladies Shoes",
                "quantity": 2,
                "unit_price": 120
              },
              {
                "product_name": "Belt",
                "quantity": 2,
                "unit_price": 30
              },
              {
                "product_name": "Bag",
                "quantity": 4,
                "unit_price": 125
              },
              {
                "product_name": "Mens Shoes",
                "quantity": 2,
                "unit_price": 110
              },
              {
                "product_name": "Wallet",
                "quantity": 2,
                "unit_price": 50
              }
            ]
          }
        ]
      }
    ]
  }
]

Template

The template should contain the name of the columns that were provided in the query above. For example we have the template with following content:

This is the basic example of loop tag.

Hello {cust_first_name} {cust_last_name}

{#orders}   
   {order_name} for total of {order_total}

   Content:
     {#product}* {product_name}: {quantity}: {unit_price}
     {/product}
{/orders}

Best Regards,
AOP Team

Output

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

This is the basic example of loop tag.

Hello John Dulles
    
   Order 1 for total of 2380

   Content:
     * Business Shirt: 3: 50
     * Trousers: 3: 80
     * Jacket: 3: 150
     * Blouse: 3: 60
     * Skirt: 3: 80
     * Ladies Shoes: 2: 120
     * Belt: 2: 30
     * Bag: 4: 125
     * Mens Shoes: 2: 110
     * Wallet: 2: 50
     
Best Regards,
AOP Team

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}

Reference

For more details on grouping records please visit Grouping Rows of Records

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 product then, the syntax for looping 3 times is as follows:

{#3|loop}
    {#product}
        Product name is {product_name}
    {/product}
{/3|loop}

Reference

For more details on looping multiple times please visit Looping n Times

Filtering

Available From: v3.0

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 product with various column including unit_price, the implementation of filtering by unit_price is:

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

Reference

For more details on filtering out the rows from a record please visit Filtering

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 product with column unit_price.

{#product|sort:"unit_price":"asc"}
{#sorted}
{product_name} {quantity} {unit_price}
{/sorted}
{/product|sort:"unit_price":"asc"}

Reference

For more details on sorting out the rows from a record please visit Sorting

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 product with unit_price column, we can get distinct unit_price as:

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

Reference

For more details on distinct of column values please visit Distinct

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}  {unit_price} {quantity}
{/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.

Reference

For more details on breaking a record into groups please visit Breaking into Group