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