The best way to be productive with APEX Office Print (AOP)

We often get the question of "how to be the most productive creating a template for APEX Office Print (AOP)".

Here are two examples of people asking in different ways:

My short answer: I recommend to use the Fast Template Switcher in our AOP Sample App or when you use the on-premises version of AOP, connect to the server and use the AOP Web Editor.

In this blog post, I will do a step-by-step guide on how I believe you will be most performant building your templates and using them in AOP.

If you didn't install APEX Office Print (AOP) yet, please read my blog post Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text

Now, we will add AOP to a button on a page in an APEX app.

Dynamic Action Plug-in

When you have a button on the page, right-click on it and choose "Create Dynamic Action".
Give it a name and as Action, specify APEX Office Print (AOP) - DA [Plug-in]

Data

The most important part of any report is the data it contains. So, the first step is to define where AOP can find all the data you want to use in the report. AOP gives you the ability to get all your data through a hierarchical SQL statement by using the cursor() or JSON syntax. For example, if we want to query all the orders and order lines (details) of a given customer we can specify in the Data Source:

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",
              40 as "image_max_width"
              from demo_order_items i, demo_product_info p
             where o.order_id = i.order_id
               and i.product_id = p.product_id
          ) "product"
         from demo_orders o
        where c.customer_id = o.customer_id
       ) "orders"
    from demo_customers c
   where customer_id = :P1_CUSTOMER_ID
  ) as "data"
from dual

In case your data is more complex than you can define in a hierarchical SQL statement, you can also specify a PL/SQL Function or a JSON data source.

AOP Template

Now you want this data in a specific look and feel. AOP allows you to specify your own template written in Word, Excel, Powerpoint, HTML, and Text. A good way to start your template is by using the AOP Template (which is selected by default). This means you don't specify your own template yet, but let AOP generate a starting template for you, based on the data you provided. AOP can generate a starter template in Word, Excel, and HTML.

Based on your data, it will generate a {tag} for every column you have. When AOP sees there are multiple records (a table), it will generate a loop statement {#tag}{/tag} as well.

Now you can iterate over your template to make it exactly as you want it to look like. Just rearrange the {tags} to the specific positions where you want your data to be. Apart from that, you are in Word (or Excel, Powerpoint, ...) and have access to all of the native Word features to create a gorgeous look and feel. If you already have a document, you can also copy and paste the tags from the AOP Template into your existing document.

What I see, is that people adjust the template, upload the template, adjust, upload, adjust, upload over and over. There's actually a much faster way to iterate through template development.

Local Debug

Behind the scenes, the AOP Plug-in generates a JSON file that is being sent to the AOP Server.
To get this JSON, you can go into Shared Components > Component Settings > APEX Office Print and set Debug to Local. This will enable AOP Debug for the entire application. If you just want to put AOP Debug on for the current button, add the following to the Init PL/SQL Code of the AOP Dynamic Action:

aop_api_pkg.g_debug := aop_api_pkg.c_debug_local;

This is what you should see in the Dynamic Action:

When you click the button, you will get the JSON file.

Quickly changing Templates

With AOP we ship an AOP Sample App. This is an APEX application which showcases many features of APEX Office Print:

In the Debugging section, you find a Quick Template Changer link:

The Quick Template Changer allows you to quickly try new templates based on an AOP JSON file.

You drag the JSON file in the first box under Exported JSON.
The JSON will be parsed and the content of the JSON is shown in the JSON Data field.
Next, you drag your template in the Template section and finally, you select the Output you want and hit the Process button.

You can now remove the template and drag-and-drop a new version of the template and hit the Process button again. This way you can quickly see the result while making changes to the template.

Web Editor in AOP On-Premises version

If you download the AOP On-Premises version and run the AOP Server (which is one executable) locally, or you navigate to the server URL where your AOP Server is running, you will see the AOP Web Editor.

This editor is really powerful and has more features than the Quick Template Changer you find in the AOP Sample App. Just as before you drag-and-drop the JSON file in the Exported JSON section.

The AOP Web Editor will parse the JSON and will show a link to the template to Download (unnamed.docx). If you want to change the Template, you just drag-and-drop another template in the Template section. On this screen, you can even prepend and append files and add sub-templates. If you don't like to always select a file from the file system, you can select all files or even a directory and drag-and-drop it entirely in the File cache section, so you can swap files even quicker!

Select the Output and hit the Process button, and presto, you see the output with the (new) template!

But that is not all, you can also change the Data on the fly by going into the Data tab:

Or see the new resulting JSON file after changing the data and template:

In case you want some examples, hit the Load Sample button and select a sample.

I really believe the AOP Web Editor will help you a lot in your development of AOP Reports.

This AOP Web Editor app is actually a nice showcase that AOP can be used with any technology. The Web Editor is written with React.js.

Hope this helps you to be even more productive with AOP!