Skip to main content

Transformation Function

Available From: v24.3

AOP allows the use of transformation function for more flexible data modification using functions. The transform() function is executed internally by AOP, serving as the main function within the transformation_function and the returned value from this function becomes the new data key for AOP during document rendering. You can define new variables and functions within this context, similar to how it's done in JavaScript.

For testing of transformation_function, On-premises users can use AOP Webeditor.

Example-I

This example demonstrates the usage of transformation function for the styling of table and breaking of data based on category to create a HTML content.

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;

The data ganerated from SQL is further processed by AOP when transformation function is provided. Hearby the transformation function:

aop_api_pkg.g_transformation_function         := 'function generateProductRows(products, category) {\r\n    return products\r\n        .filter(product => product.category === category)\r\n        .map(product => {\r\n            if (category === \"Mens\") {\r\n                product.category_bold = \"true\";\r\n                product.product_name_font_color = \"blue\";\r\n            } else {\r\n                product.category_italic = \"true\";\r\n                product.product_name_font_color = \"red\";\r\n            }\r\n            const totalCost = product.unit_price * product.quantity;\r\n            return `\r\n                <tr>\r\n                    <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">${product.product_name}</td>\r\n                    <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">${product.unit_price}</td>\r\n                    <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">${product.quantity}</td>\r\n                    <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">${totalCost}</td>\r\n                </tr>\r\n            `;\r\n        })\r\n        .join('');\r\n}\r\n\r\nfunction transform() {\r\n    files.forEach(file => {\r\n        file.data.forEach(data => {\r\n            // Initialize HTML strings for mens_products and womens_products\r\n            let mensProductsHtml = '<table style=\"width: 100%; border: 2px solid blue; border-collapse: collapse;\">';\r\n            let womensProductsHtml = '<table style=\"width: 100%; border: 2px solid red; border-collapse: collapse;\">';\r\n\r\n            // Add table headers\r\n            const tableHeaders = `\r\n                <tr>\r\n                    <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Product Name</th>\r\n                    <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Unit Price</th>\r\n                    <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Quantity</th>\r\n                    <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Total Cost</th>\r\n                </tr>\r\n            `;\r\n            mensProductsHtml += tableHeaders;\r\n            womensProductsHtml += tableHeaders;\r\n\r\n            // Generate HTML rows for mens and womens products\r\n            const mensProductsRows = generateProductRows(data.product, \"Mens\");\r\n            const womensProductsRows = generateProductRows(data.product, \"Womens\");\r\n\r\n            // Calculate totals using reduce\r\n            const mensTotals = data.product\r\n                .filter(product => product.category === \"Mens\")\r\n                .reduce((totals, product) => {\r\n                    totals.quantity += product.quantity;\r\n                    totals.cost += product.unit_price * product.quantity;\r\n                    return totals;\r\n                }, { quantity: 0, cost: 0 });\r\n\r\n            const womensTotals = data.product\r\n                .filter(product => product.category === \"Womens\")\r\n                .reduce((totals, product) => {\r\n                    totals.quantity += product.quantity;\r\n                    totals.cost += product.unit_price * product.quantity;\r\n                    return totals;\r\n                }, { quantity: 0, cost: 0 });\r\n\r\n            // Close the HTML tables\r\n            mensProductsHtml += mensProductsRows + '</table>';\r\n            womensProductsHtml += womensProductsRows + '</table>';\r\n\r\n            // Add the new entries to the data object\r\n            data.mens_products = mensProductsHtml;\r\n            data.womens_products = womensProductsHtml;\r\n            data.mens_total_quantity = mensTotals.quantity;\r\n            data.mens_total_cost = mensTotals.cost;\r\n            data.womens_total_quantity = womensTotals.quantity;\r\n            data.womens_total_cost = womensTotals.cost;\r\n        });\r\n    });\r\n    return files;\r\n}\r\n';

AOP executes the transformation_function internally that gives the following modified data:

[
{
"filename": "file1",
"data": [
{
"cust_first_name": "John",
"cust_last_name": "Dulles",
"product": [
{
"product_name": "Business Shirt",
"unit_price": 50,
"quantity": 3,
"category": "Mens",
"category_bold": "true",
"product_name_font_color": "blue"
},
{
"product_name": "Trousers",
"unit_price": 80,
"quantity": 3,
"category": "Mens",
"category_bold": "true",
"product_name_font_color": "blue"
},
{
"product_name": "Jacket",
"unit_price": 150,
"quantity": 3,
"category": "Mens",
"category_bold": "true",
"product_name_font_color": "blue"
},
{
"product_name": "Blouse",
"unit_price": 60,
"quantity": 3,
"category": "Womens",
"category_italic": "true",
"product_name_font_color": "red"
},
{
"product_name": "Skirt",
"unit_price": 80,
"quantity": 3,
"category": "Womens",
"category_italic": "true",
"product_name_font_color": "red"
},
{
"product_name": "Ladies Shoes",
"unit_price": 120,
"quantity": 2,
"category": "Womens",
"category_italic": "true",
"product_name_font_color": "red"
},
{
"product_name": "Belt",
"unit_price": 30,
"quantity": 2,
"category": "Accessories"
},
{
"product_name": "Bag",
"unit_price": 125,
"quantity": 4,
"category": "Accessories"
},
{
"product_name": "Mens Shoes",
"unit_price": 110,
"quantity": 2,
"category": "Mens",
"category_bold": "true",
"product_name_font_color": "blue"
},
{
"product_name": "Wallet",
"unit_price": 50,
"quantity": 2,
"category": "Accessories"
}
],
"mens_products": "<table style=\"width: 100%; border: 2px solid blue; border-collapse: collapse;\">\n <tr>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Product Name</th>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Unit Price</th>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Quantity</th>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Total Cost</th>\n </tr>\n \n <tr>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Business Shirt</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">50</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">3</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">150</td>\n </tr>\n \n <tr>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Trousers</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">80</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">3</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">240</td>\n </tr>\n \n <tr>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Jacket</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">150</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">3</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">450</td>\n </tr>\n \n <tr>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Mens Shoes</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">110</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">2</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">220</td>\n </tr>\n </table>",
"womens_products": "<table style=\"width: 100%; border: 2px solid red; border-collapse: collapse;\">\n <tr>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Product Name</th>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Unit Price</th>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Quantity</th>\n <th style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Total Cost</th>\n </tr>\n \n <tr>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Blouse</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">60</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">3</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">180</td>\n </tr>\n \n <tr>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Skirt</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">80</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">3</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">240</td>\n </tr>\n \n <tr>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">Ladies Shoes</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">120</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">2</td>\n <td style=\"border-width: 1px; border-style: solid; border-color: black; padding: 8px;\">240</td>\n </tr>\n </table>",
"mens_total_quantity": 11,
"mens_total_cost": 1060,
"womens_total_quantity": 8,
"womens_total_cost": 660
}
]
}
]

Template

For example, we have the template with following content:

 template.docx  

Output

Upon processing the given data, along with transformation_function and the template, AOP generates the following result.
 output.pdf  

Example-II

Charts using Transformation function:

Data Source

Hereby examples of data source for different options.

select 'file1' as "filename",
cursor (
select cursor (
select 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 "pies",
cursor (
select 'Order ' || order_id as "x",
order_total as "y"
from aop_sample_orders
) as "radars"
from dual
) as "data"
from dual
) as "data"
from dual;

The data ganerated from SQL is further processed by AOP when transformation function is provided. Hearby the transformation function:

aop_api_pkg.g_transformation_function         := 'function createRadarChart(radars) {\r\n    return {\r\n        type: \"radar\",\r\n        name: \"Radar Chart\",\r\n        options: {\r\n            width: 576,\r\n            height: 336,\r\n            title: \"Radar Chart\",\r\n            border: true,\r\n            dataLabels: {\r\n                showDataLabels: true,\r\n                position: \"center\",\r\n                showValue: true\r\n            },\r\n            axis: {\r\n                x: {\r\n                    showValues: true\r\n                },\r\n                y: {\r\n                    showValues: true\r\n                }\r\n            }\r\n        },\r\n        radars: [\r\n            {\r\n                name: \"Orders\",\r\n                data: radars\r\n            }\r\n        ]\r\n    };\r\n}\r\n\r\nfunction createPieChart(pies) {\r\n    return {\r\n        type: \"pie\",\r\n        name: \"Pie Chart\",\r\n        options: {\r\n            width: 576,\r\n            height: 336,\r\n            title: \"Pie Chart\",\r\n            border: true,\r\n            dataLabels: {\r\n                showDataLabels: true,\r\n                showPercentage: true\r\n            }\r\n        },\r\n        pies: [\r\n            {\r\n                name: \"Customers\",\r\n                data: pies\r\n            }\r\n        ]\r\n    };\r\n}\r\n\r\nfunction transform() {\r\n    files.forEach(file => {\r\n        const data = file.data;\r\n        if (data.radars && data.radars.length > 0) {\r\n            data.radar_chart = createRadarChart(data.radars);\r\n        }\r\n        if (data.pies && data.pies.length > 0) {\r\n            data.pie_chart = createPieChart(data.pies);\r\n        }\r\n    });\r\n    return files;\r\n}\r\n';

AOP executes the transformation_function internally that gives the following modified data:

[
{
"filename": "file1",
"data": {
"pies": [
{
"x": "Albert Lambert",
"y": 950
},
{
"x": "Edward Logan",
"y": 2420
},
{
"x": "Eugene Bradley",
"y": 2760
},
{
"x": "Fiorello LaGuardia",
"y": 1090
},
{
"x": "Frank OHare",
"y": 1060
},
{
"x": "John Dulles",
"y": 2380
},
{
"x": "William Hartsfield",
"y": 2370
}
],
"radars": [
{
"x": "Order 1",
"y": 1890
},
{
"x": "Order 2",
"y": 2380
},
{
"x": "Order 3",
"y": 1640
},
{
"x": "Order 4",
"y": 1090
},
{
"x": "Order 5",
"y": 950
},
{
"x": "Order 6",
"y": 1515
},
{
"x": "Order 7",
"y": 905
},
{
"x": "Order 8",
"y": 1060
},
{
"x": "Order 9",
"y": 730
},
{
"x": "Order 10",
"y": 870
}
],
"radar_chart": {
"type": "radar",
"name": "Radar Chart",
"options": {
"width": 576,
"height": 336,
"title": "Radar Chart",
"border": true,
"dataLabels": {
"showDataLabels": true,
"position": "center",
"showValue": true
},
"axis": {
"x": {
"showValues": true
},
"y": {
"showValues": true
}
}
},
"radars": [
{
"name": "Orders",
"data": [
{
"x": "Order 1",
"y": 1890
},
{
"x": "Order 2",
"y": 2380
},
{
"x": "Order 3",
"y": 1640
},
{
"x": "Order 4",
"y": 1090
},
{
"x": "Order 5",
"y": 950
},
{
"x": "Order 6",
"y": 1515
},
{
"x": "Order 7",
"y": 905
},
{
"x": "Order 8",
"y": 1060
},
{
"x": "Order 9",
"y": 730
},
{
"x": "Order 10",
"y": 870
}
]
}
]
},
"pie_chart": {
"type": "pie",
"name": "Pie Chart",
"options": {
"width": 576,
"height": 336,
"title": "Pie Chart",
"border": true,
"dataLabels": {
"showDataLabels": true,
"showPercentage": true
}
},
"pies": [
{
"name": "Customers",
"data": [
{
"x": "Albert Lambert",
"y": 950
},
{
"x": "Edward Logan",
"y": 2420
},
{
"x": "Eugene Bradley",
"y": 2760
},
{
"x": "Fiorello LaGuardia",
"y": 1090
},
{
"x": "Frank OHare",
"y": 1060
},
{
"x": "John Dulles",
"y": 2380
},
{
"x": "William Hartsfield",
"y": 2370
}
]
}
]
}
}
}
]

Template

For example, we have the template with following content:

 template.docx  

Output

Upon processing the given data, along with transformation_function and the template, AOP generates the following result.

 output.pdf