Data Preparation for AOP

Data Preparation for AOP

Background

The team at Storm Petrel are enthusiastic about using AOP (APEX Office Print) for developing high-quality and professional reports directly from Oracle Application Express. Our expectation is that with AOP, the cost of developing terrific looking reports will be reduced (significantly?!) and that we will gain greater flexibility with report layout. Data preparation for AOP has a few tricks.

We have been collaborating with APEX RND during a trial period with AOP. We find the support excellent. We have learned that new techniques must be employed to step into their world. The purpose of this blog entry is to assist other (and help remind us) what these techniques are.

Using an inventive tool really does require that one not just walk in the moccasins of another, but see through their eyes as well.

Context

Writing database reports requires two processes:

  • Designing the report for printing on paper;
  • Developing the data structures for the report.

Our industry has been separating these processes for decades. Collectively we have said “let’s use the best tool for each job.” Word processors and spreadsheets have become standard tools for presenting data neatly and attractively. And SQL is our industry’s best tool for organizing data. BI Pub has been doing this for years (although BI Pub always broke my MS Word or Excel and generated more frustration than freedom).

APEX RND employs SQL for data management and word processors/spreadsheets for layout and design. Y es, they let you use power point too. The output is flexible: PDF, Word, Excel, or HTML5.

We will return to related topics in the weeks to come. Even in an old friend like Word, or Excel, there are tricks and rules.

Future Entries

  • Preparing MS Word templates
  • Preparing MS Excel templates
  • Formatting numeric values
  • Subtotals with GROUP BY ROLLUP

Prepare the Payload

You are sending all of the components of a report to an engine that generates your report. The components are the report layout instructions and the data to populate the report. AOP has you do this in one query creating a single JSON data set. Like a payload on a rocket, all required “stuff” must be present when docking at the destination.

The payload bakes advantage of the power present within the JSON data structures. Data are nested. Records within fields.

The most fundamental aspect of the AOP payload are two “fields”:

  • filename
  • data

The Oracle SQL queries shown below are written in with tools we have on our desks such as SQL Developer. The output is implicitly JSON. One does not need to convert or make an explicit declaration for JSON.

The query framework to create the payload is:

select
  'file1' as "filename",
  cursor ( select
  	cursor ( select
      -- place holder for header fields
      cursor (
				-- place  holder for detail query
        ) as "detail"
			-- place holder for header tables, joins and where
    ) as "header"
 from dual ) as "data"
from dual

If you require a report that does NOT have specific header data, the framework is simplified. The payload requirements remain the same:

  • filename
  • data

The simplified structure does not include the header data set. It looks like this:

select
	'file1' as "filename",
  cursor ( select
     cursor ( select
           -- place holder for fields
           -- place holder for tables, joins and where clause
    ) as "detail"
 from dual ) as "data"
from dual

Inside – Out

Start your query from the inside. Build out – Build up. The very last step of the process involves wrapping the query into the AOP structure. SQL Developer, APEX SQL Workshop, APEX classic and APEX interactive reports do not tolerate the nested structure required by AOP. These tools do not display data presented as JSON. Even our good friend SQL Developer adds extra characters into the JSON output that annoys on-line JSON editors.

Develop solid queries with the existing tools.

For my example, I will generate a report from the standard Oracle “Demo” data using the table DEMO_ORDER_ITEMS. With subsequent blog entries, we will explore GROUP BY ROLLUP and adding richness to the report. The first payload ought to be low risk and simple – more Sputnik, less Enterprise. I’ll come back to add subtotals and such.

Detail Query

Starting with a familiar query on a familiar data set, let’s display the order items for one customer while providing an extended total for each line.

        select
          o.customer_id             "customer_id",
          i.order_id                "order_id",
          i.order_item_id           "order_item_id",
          p.product_name            "product",
          i.quantity                "qty",
          nvl(i.quantity,0)     
            * nvl(i.unit_price, 0)  "extended"         
        from demo_order_items i
        left join demo_orders o on
          o.order_id = i.order_id 
        left join demo_customers c on
          c.customer_id = o.customer_id
        left join demo_product_info p on
          p.product_id = i.product_id
        where o.customer_id = 1
               
               ;

The results of this query should look like this:

Query Results

Header Query

Let’s go create a header query. We want the data to be related and come together when put it in the AOP payload, so use table aliases and criteria that will subsequently prepare for the marriage of header and detail queries.

    select
        c1.customer_id                 "customer_id",
        o1.order_id                    "order_id",
        c1.cust_first_name || ' ' || 
            c1.cust_last_name          "customer",
        c1.cust_street_address1        "addr",
          c1.cust_city || ' ' ||
          c1.cust_state || ' ' 
          || cust_postal_code           "city",
        trunc(o1.order_timestamp)       "ord_date"
    from demo_orders o1
    left join demo_customers c1 on
      c1.customer_id = o1.customer_id
    where o1.customer_id = 1
;
Header Query Results

The results ought to resemble, the image below:

Lower Case

JSON is case sensitive. Oracle and Oracle APEX are not particularly case sensitive. The default case for column aliases in Oracle is all capital letters. Save yourself the four hours I wasted, make sure that every column has an alias, and make sure that the alias is small letters.

To do this, aliases must be presented in double-quotes and typed in lower case. Your quick validation is the column headings displayed in SQL Developer. If all in lower case, you will be a happier and more joyful person.

Use Lower Case

Short Aliases

Use short column aliases! You will be keying these into MS Word or MS Excel. If your alias is longer than your data, then you’ll be setting yourself up for frustration when laying out your report. For example, a standard date written in the US style is “MM/DD/RR”, 8 characters long. If our alias is longer than 8 characters, e.g. “activity_start_date”, then MS Word will force your column to be long enough to hold the phrase {activity_start_date}. Arguing with Word is a waste of time. Plan ahead. Use short aliases.

Data Preparation for AOP

Use copy/paste to wrap the query into the needed structures. Don’t over think it. When doing data preparation for AOP, see the elements and make small steps.

Replace the single “select” at the top with this:

select
	'file1' as "filename",
  cursor ( select
     cursor ( select

And stitch the parts together. You will need to separate the header query. Tease the columns away from the FROM and WHERE clauses.

select
  'file1' as "filename",
  cursor ( select
  	cursor ( select    

        c1.customer_id                 "customer_id",
        o1.order_id                    "order_id",
        c1.cust_first_name || ' ' || 
            c1.cust_last_name          "customer",
        c1.cust_street_address1        "addr",
          c1.cust_city || ' ' ||
          c1.cust_state || ' ' 
          || cust_postal_code           "city",
        trunc(o1.order_timestamp)       "ord_date",
  
   cursor ( 
   
      select
          o.customer_id             "customer_id",
          i.order_id                "order_id",
          i.order_item_id           "order_item_id",
          p.product_name            "product",
          i.quantity                "qty",
          nvl(i.quantity,0)     
            * nvl(i.unit_price, 0)  "extended"         
        from demo_order_items i
        left join demo_orders o on
          o.order_id = i.order_id 
        left join demo_customers c on
          c.customer_id = o.customer_id
        left join demo_product_info p on
          p.product_id = i.product_id
        where o.customer_id = 1
        
        ) as "detail"
        
    from demo_orders o1
    left join demo_customers c1 on
      c1.customer_id = o1.customer_id
    where o1.customer_id = 1
    
    ) as "header"
    
 from dual ) as "data"
from dual   

;

It will look like this when done. I leave the extra spaces. It lets me see through the required structures for AOP.

Simple Query

The preparation for a simple query without header data will look like this:

select
  'file1' as "filename",
  cursor ( select
    cursor (
      select
          o.customer_id             "customer_id",
          i.order_id                "order_id",
          i.order_item_id           "order_item_id",
          p.product_name            "product",
          i.quantity                "qty",
          nvl(i.quantity,0)     
            * nvl(i.unit_price, 0)  "extended"         
        from demo_order_items i
        left join demo_orders o on
          o.order_id = i.order_id 
        left join demo_customers c on
          c.customer_id = o.customer_id
        left join demo_product_info p on
          p.product_id = i.product_id
        where o.customer_id = 1
        
        ) as "detail"
  
 from dual ) as "data"
from dual   

;

Output

Here is where faith steps in. The data you get is now darn close to JSON data. I use SQL Developer.
There seem to be some errant greater-than (GT) and less-than (LT) characters throughout the data. I cannot take this data to a JSON formatter and look at it.

In the next installment, I’ll show you a trick given to us by AOP to capture the data midstream while in AOP to display it.

JSON Output

Dumping the output into a text file it reads like this:

{<customer_id=1,order_id=2,customer=John Dulles,addr=45020 Aviation Drive,city=Sterling VA 20166,ord_date=01/01/2014,detail={<customer_id=1,order_id=2,order_item_id=103,product=Business Shirt,qty=3,extended=150>,<customer_id=1,order_id=2,order_item_id=104,product=Trousers,qty=3,extended=240>,<customer_id=1,order_id=2,order_item_id=105,product=Jacket,qty=3,extended=450>,<customer_id=1,order_id=2,order_item_id=106,product=Blouse,qty=3,extended=180>,<customer_id=1,order_id=2,order_item_id=107,product=Skirt,qty=3,extended=240>,<customer_id=1,order_id=2,order_item_id=111,product=Mens Shoes,qty=2,extended=220>,<customer_id=1,order_id=2,order_item_id=112,product=Wallet,qty=2,extended=100>,<customer_id=1,order_id=2,order_item_id=108,product=Ladies Shoes,qty=2,extended=240>,<customer_id=1,order_id=2,order_item_id=109,product=Belt,qty=2,extended=60>,<customer_id=1,order_id=2,order_item_id=110,product=Bag,qty=4,extended=500>,}>,}

You are probably asking: where’s my report? Wo ist mein Bericht? Où est mon rapport? Dónde está mi informe?

Stay tuned…

-/qed

By |2018-10-09T11:35:47+00:00January 31st, 2016|Apex Office Print, Oracle APEX 5|Comments Off on Data Preparation for AOP

About the Author: