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!

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. 
 

Author: Kevin Thyssen