AOP Text Formatting

Introduction

While I love Oracle Application Express, I have experienced frustration with generating stunning printed material. APEX Office Print (AOP) has simultaneously expanded our team’s capabilities while reducing our effort. That’s a tool! Rather, that is the definition of a great tool. Our ancestors used antlers to dig in the dirt, then someone came along with the steel spade. I feel the same way about AOP. In a previous entry, we touched on the structure of the SQL needed to prepare the data for AOP. Three months later, I still refer back to this blog for quick answers and structures. In this blog, we will explore AOP Text Formatting.

In the recent months, our team has:

  1. Created data-rich reports with grouping, sub-totals, running totals all formatted in MS Excel and handed back as PDF
  2. Developed text-rich reports that generate glorious MS Word documents — Easy and affordable database driven Word documents
  3. Added both conditional formatting and conditional data to reports

Context

Edit AOP Report

For one of our commercial applications (Tempest-Bid), we create proposals. Customers use our software to respond to government requests for proposals. These are highly structured documents with hundreds (thousands) of building blocks, often involve multiple technical writers, dozens of resumes, and an output that must be perfect. For the purpose of this example and blog, we are simplifying the proposal down. We use the US Constitution as a sample of the process. And for this blog, we’ll show it being assembled in Oracle APEX and AOP.

Objectives

We are tool-builders. We pride ourselves on great support, but… we really don’t want to have to bill a customer because they want to change a footer, a font, or some other formatting within a report. Let’s give that power back to the user. AOP now let’s us share this with our customers. Want Arial 12? Go for it! Want narrower margins? Help yourself!

Not answers that have been routinely available within APEX.

In this entry, we will focus on

  • Sharing Template management with the customer
  • Linking AOP query with Templates

Setup

Our report menus are data driven (dynamic list). The basics are the source page (menu page) and the destination page (APEX page hosting the report). Incorporating AOP to this process means adding the fields needed to support the AOP plugin. Recently, we added a separate child table for report templates. We can load templates from our menu, identifying templates as “Global” or customers can add their own templates, which are then identified as “Private”.

AOP is also data driven. The data for the AOP report derives from a query. But so does the identification of the template. This means that you can provide a select list of available templates to your users. To get there, we needed a means of managing templates.

AOP Report Template

Open the collapsible region to review, edit, or add new templates to a report.

AOP Report Template List

Workflow

When writing an AOP query, we adopt the same thinking we use on Interactive Reports. What are ALL of the options that the user may want? What fields should we prepare for them? What formatting is required? The challenge for us is to anticipate needs down the road.

And…

And we need to prepare a template with full instructions on it for the user. We call this (in the data and in the image above) “Example”. We put all of the fields with definitions. We put instructions and coaching.

Preparing CLOBs

There is a trick when managing text in CLOBs. The data exchange between Oracle and AOP is done in JSON. The hand off strips the carriage return/line feed that maintains the integrity of paragraphs and lists. The guys at AOP look for a \n in the data, then substitute the CR/LF. It is for us to prepare the data with the \n.

  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"

This replace statement pulls the CR/LF — chr(10) chr(13) — from the data and lays in the \n as required by AOP.

Example

The Output

The following image is a report generated from data within Oracle, managed by APEX and “printed” by AOP into MS Word.

US Constitution

Discussion

While the example shown uses three fields, there are more available for the user on this report. Because we are curious geeks, here is the underlying query:

select
	'file1' as "filename",
  cursor ( select
     cursor (
select
  ,c.client_name                "client"
  ,p.proposal_name              "proposal"
  ,r.name                       "require_name"
  ,r.description                "require_description"
  ,r.wbs_sequence               "wbs_sequence"
  ,r.sequence_order             "require_sequence"
  ,r.proposal_section           "proposal_section"
  ,r.proposal_section_title     "section_title"
  ,sn.SUBMIT_ASSET_FK
  ,(select first_name || ' ' || last_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_first_last"
  ,(select last_name || ', ' || first_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_last_first"
  ,sn.TITLE                       "snippet_title"
  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"
  ,sn.APPROVED                    "approved"
  ,to_char(sn.CREATED_ON,'MM/DD/YYYY')  "created_on"
  ,sn.CREATED_BY                        "created_by"
  ,to_char(sn.UPDATED_ON,'MM/DD/YYYY')  "updated_on"
  ,sn.UPDATED_BY                        "updated_by"
  ,r.outline_sequence                   "outl_seq"
from bid_require_snippet sn
left join bid_client c on
  c.client_pk = sn.client_fk
left join bid_proposal p on
  p.proposal_pk = sn.proposal_fk
left join bid_require r on
  r.require_pk = sn.require_fk
where nvl(sn.archived,'N') <> 'Y'
  and nvl(sn.approved,'N') = 'Y'
  and sn.proposal_fk = :G_PROPOSAL_PK
order by r.outline_sequence, r.proposal_section 
    ) as "d"
 from dual ) as "data"
from dual

Example Template

With users being able to download templates, modify them, upload modifications or new ones, we wanted to provide instructions. The image below illustrates one of these “examples”.

US Constitution

-/qed

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