Hooked on Classic (Reports)

Learn tips & techniques to make them sing

Jorge Rimblas

Jorge Rimblas

Senior APEX Consultant

@rimblas

rimblas.com/blog

  • Oracle Database since 1995
  • APEX since it was HTMLDB in 2004
  • Kscope16 APEX Track Content Lead
  • jrimblas in OTN Forums
  • Co-author of
    "Expert Oracle Application Express, 2nd Edition"
    with "Themes & Templates" chapter
  • North America’s largest APEX consulting firm
  • 12 years working with Oracle APEX
  • 80 employees and growing
  • Committed to APEX innovation
  • Our services: application development, coaching, consulting, EBS extensions, and Forms migration
  • Learn more at insum.ca

Agenda

  • Why?
  • Template Types
  • Generic  Column Templates
  • Named Column Templates
  • Analytic Functions
  • Limitations
  • Resources

The "Manual" Approach


htp.p('<ul>');
for t in (
  select id
       , todo
       , decode(completed_by
              , null, ''
              , 'check' || '-') checked
  from app_todos
)
loop
  htp.p('<li>'
      || t.todo
      ||  ' <i class="fa fa-'
      ||      t.checked || 'square-o">'
      ||   '</i>'
      || '</li>');  
end loop;
htp.p('</ul>');

Why?

Standard Functionality

Ease of use

Flexibility

No PL/SQL loops

Classic Report examples

Template Types

Generic Columns
&
Named Columns

Generic Columns


Named Columns

Column Template

Row Template

Generic Columns

Every column looks the same 

<td>
  #COLUMN_VALUE#
</td>

Every column looks the same 

<td class="t-Report-cell" #ALIGNMENT# headers="#COLUMN_HEADER_NAME#">
  #COLUMN_VALUE#
</td>

Column Template Conditions

  • Four Column Templates
  • Condition for each template
  • Condition types: PL/SQL Expression, even or odd rows

Using conditions

Duplicate the template

Modify the template

Assign the template

Step by step details

rimblas.com/blog/2014/05/using-conditions-with-column-templates/

Named Columns

SQL

Named Columns

select PRODUCT_ID
     , PRODUCT_NAME
     , PRODUCT_DESCRIPTION
     , CATEGORY
     , PRODUCT_AVAIL
     , LIST_PRICE
 from PRODUCTS
#PRODUCT_ID#
#PRODUCT_NAME#
#PRODUCT_DESCRIPTION#
#CATEGORY#
#PRODUCT_AVAIL#
#LIST_PRICE#

#COLUMN_VALUE#

Generic Columns

SQL

Named Columns

select PRODUCT_ID
     , PRODUCT_NAME
     , PRODUCT_DESCRIPTION
     , CATEGORY
     , PRODUCT_AVAIL
     , LIST_PRICE
 from PRODUCTS
#1#
#2#
#3#
#4#
#5#
#6#

Positional Columns

Simple List

Simple List

<ul>
  <li>Orange Juice <i class="fa fa-check-square-o"></i></li>
  <li>Milk <i class="fa fa-check-square-o"></i></li>
  <li>Eggs <i class="fa fa-square-o"></i></li>
  <li>Bread <i class="fa fa-square-o"></i></li>
  <li>Cornflakes <i class="fa fa-square-o"></i></li>
</ul>

  <li>#TODO# <i class="fa fa-#CHECKED#square-o"></i></li>

<ul>
</ul>

Before Rows

After Rows

Simple List Template

Simple List

  <li>#TODO# <i class="fa fa-#CHECKED#square-o"></i></li>

select id
     , todo
     , decode(completed_by
            , null, ''
            , 'check' || '-') checked
     , completed_by
     , completed_on
from app_todos

Demo p220

Demo

Adding a link to edit

Benefits

  • Standard Classic Report Functionality
    • Edit Links
    • Dynamic Actions
      • Dialog Close
      • Refresh

Named Columns

Mixing Layouts

Contact List

Two types of rows

<tr>
  <td colspan="2">
    <a href="#LINK#">&EDIT_BUTTON.</a>
    #PARTY_NAME# - #ROLE# <i class="fa"></i>
  </td>
</tr>
<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

<tr>
  <td colspan="2">
    <a href="#LINK#">&EDIT_BUTTON.</a>
    #PARTY_NAME# - #ROLE# <i class="fa"></i>
  </td>
</tr>
<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

select party_id
     , party_name
     , role
     , type_name
     , entry
     , active_ind
  from parties
<tr>
  <td colspan="2">
    <a href="#LINK#">&EDIT_BUTTON.</a>
    #PARTY_NAME# - #ROLE# <i class="fa"></i>
  </td>
</tr>
<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

select party_id
     , party_name
     , role
     , type_name
     , entry
     , active_ind
  from parties
 where party_id = 41

For a single row

Use both types of rows

For a single row
Or the 1st Party Row

Use both types of rows

Special Case

Analytic Functions

Analytics Syntax

<function> (<arg1>, <arg2>, )
   OVER (
     < partition clause >
     < sorting clause >
     < windowing clause >
   )
row_number() 
   over (
     partition by party_id
     order by party_name, entry
   )

Analytics

select party_id
     , party_name
     , entry
     , row_number() over (
         partition by party_id
         order by party_name, entry) rn
  from parties

PARTY_ID  PARTY_NAME         ENTRY                             RN
--------- ------------------ -------------------------------- -------
1         Scott, Michael     800 555-1212                     1
1         Scott, Michael     952 555-1212                     2
1         Scott, Michael     michael.scott@dm.us              3
21        Schrute, Dwight    800 555-1212                     1
21        Schrute, Dwight    TheSilentNinja@SchruteFarms.com  2
21        Schrute, Dwight    dschrute@dm.us                   3
21        Schrute, Dwight    ceo@SchruteFarms.com             4
41        Halpert, James D.  800 555-1212                     1

 8 rows selected.

count(*) as analytic

select party_id
     , party_name
     , row_number() over (
         partition by party_id
         order by party_name, entry) rn
     , count(*) over (
        partition by party_id
       ) total_rows
  from parties

PARTY_ID  PARTY_NAME         RN  TOTAL_ROWS
--------- ------------------ --- -----------
1         Scott, Michael     1   3
1         Scott, Michael     2   3
1         Scott, Michael     3   3
21        Schrute, Dwight    1   4
21        Schrute, Dwight    2   4
21        Schrute, Dwight    3   4
21        Schrute, Dwight    4   4
41        Halpert, James D.  1   1

 8 rows selected.

KISS series on Analytics

by Connor McDonald

youtu.be/nk4I4ta4ZuU 

select party_id
     , party_name
     , role
     , type_name
     , entry
     , row_number() over (partition by party_id 
                              order by party_name, entry) rn
  from parties

:RN = 1

Putting it all together

Create a new template

<tr>
  <td colspan="2">
    <a href="#LINK#">&EDIT_BUTTON.</a>
    #PARTY_NAME# - #ROLE# <i class="fa"></i>
  </td>
</tr>
<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

Column Template 1

Column Template 2

<tr>
  <td colspan="2">
    <a href="#LINK#">&EDIT_BUTTON.</a>
    #PARTY_NAME# - #ROLE# <i class="fa"></i>
  </td>
</tr>
<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

Column Template 1

Adding some markup

:RN = 1
<tr>
  <td colspan="2">
    <a href="#LINK#">&EDIT_BUTTON.</a>
    #PARTY_NAME# - #ROLE# <i class="fa"></i>
  </td>
</tr>
<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

Column Template 1

<tr class="newContact rowStatus_#ACTIVE_IND#">
  <td colspan="2" class="t-Report-cell heading">
    <a href="#LINK#" class="contactEdit" data-id="#PARTY_ID#">&EDIT_BUTTON.</a> 
    <span class="name">#PARTY_NAME#</span> - <span class="roleName">#ROLE#</span> 
    <i class="fa cfa-check_#ACTIVE_IND# cfa-1_5x" title="#ACTIVE_IND#"></i>
  </td>
</tr>
<tr>
  <td align="right" class="t-Report-cell contactType">#TYPE_NAME#</td>
  <td class="t-Report-cell contactEntry">#ENTRY#</td>
</tr>

Adding some markup

:RN = 1
<tr>
  <td align="right">#TYPE_NAME#</td>
  <td>#ENTRY#</td>
</tr>

Column Template 2

Contact List

Limitations

  • Some HTML knowledge
  • Templates are not that re-usable
  • New columns require template changes
  • Only 4 template conditions
  • Potential Pagination Issues
  • Limited support for Interactive Reports

Interactive Reports

Detail View

Blogs & References

Q&A

Hooked on Classic (Reports)

@rimblas

Jorge Rimblas

apex.world 
Your one stop for
 everything APEX

Hooked on Classic Reports

By Jorge Rimblas

Hooked on Classic Reports

Visit http://rimblas.com/blog/2016/06/apex-classic-report-named-column-templates/ for companion videos to this presentation. Hooked on Classic (Reports): learn tips & techniques to make them sing. Using the lowly Classic Report template, this presentation will cover how to take advantage of the Generic Columns and Named Columns template types. We will examine a complex report layout using Named Columns template. We will also look at advanced querying techniques, such as analytic functions, to further customize the report. By inspecting underlying HTML structures, we will gain an understanding of how these templates work. The techniques and functionality covered are standard in APEX and often greatly underutilized. They have been around for several versions of APEX, yet many developers don't know how to use them or that they even exist.

  • 4,773

More from Jorge Rimblas