Skip to Main Content

Monday, January 25, 2021

Using AOP to Create a Sales Overview

APEX Office Print (AOP)

After my previous post, in which APEX Office Print (AOP) was used to generate a monthly timesheet, I was asked if I could provide a similar example using the Sample Database Application. Well, here we go!

Of course, there are lots of different use cases, but for this example I want to generate an overview with product sales for each category, grouped by period with subtotals. I’m choosing the Sales by Month report as a starting point for our AOP export.  
 

 
I started by creating an Export button and an on-click dynamic action with  UC - APEX Office Print (AOP) - DA [Plug-In] as the event. These are the settings: 
 
The following query returns the data I need. By using the pivot operator, the results for each category (Mens, Womens, Accessories) are available. Keep in mind that the final result should be inside a “data” cursor. 
 
select cursor ( 
  select cursor ( 
    select product_name, 
           period, 
           mens,
           womens, 
           accessories 
      from (select pi.product_name,
                   to_char(o.order_timestamp, 'MON RRRR') as period, 
                   (oi.quantity * oi.unit_price) as amount, 
                   pi.category 
              from demo_order_items oi 
              join demo_orders o 
                on o.order_id = oi.order_id 
              join demo_product_info pi 
                on pi.product_id = oi.product_id 
             order by o.order_timestamp) 
     pivot (sum(amount) for (category) 
              in ('Mens' as mens, 
                  'Womens' as womens, 
                  'Accessories' as accessories))) as sales 
    from dual) as "data" 
  from dual   
 
For the template, I used an Excel template called sales_template.xlsx which was uploaded to the Static Application Files in my APEX application. The ‘{#SALES|break:"PERIOD"}’ tag (AOP documentation) is used to group the results for each period. 
 
 
After clicking the Export button, our report is generated.:
 
 
 If you want to try out more examples, make sure you install the AOP Sample Application or try out our online version.  And, if you would like more details on the techniques used in this example, please read my previous blog post.
 
Kevin.

Author: Kevin Thyssen

Pagination

Comments

  • Ahmet 19 Feb 2021

    Thank you very much!

  • Bhavin Adhvaryu 1 Feb 2021

    Hello Kevin,
    Many thanks for providing the example using the Sample Database Application.
    Nice to understand and get to know AOP in this way...! Fantastic..!