Of course, you can do that with APEX!

Report Templates; the definitive guide

Jorge Rimblas

@rimblas

Jorge Rimblas

APEX Tech Lead at DRW

@rimblas

rimblas.com/blog

  • Oracle DB since 1995
  • APEX since it was HTMLDB in 2004
  • Always involved in web technologies
  • jrimblas in OTN Forums
  • Contributor to
    "Expert Oracle Application Express, 2nd Edition"
    with "Themes & Templates" chapter

Goals

Be comfortable enhancing report layouts

  • Examples
  • Single Line Alert & Messages
  • HTML Expressions
    • Interactive Reports
    • Interactive Grids
  • Patterns
  • Classic Reports
  • Classic Reports: Custom Named Templates

Agenda

Examples

Classic Reports

Single Line Alerts & Messages

Examples

There are ~ #TIME_ENTRIES# total time 
entries, <b>#MANDATE_TIME_ENTRIES#</b> 
billable that will create transactions. §

HTML Expression

HTML Expression

Reference your columns via the #COLUMN_NAME# notation

Columns are still escaped!

Use any HTML you want

Remove Pagination

Under Report Attributes

Remove Headings

Under Report Attributes

Heading

Value

Heading

Value

Report Template: Vertical Report

Under Report Attributes

Change Template Type

Also, turn off Pagination!

Demo p500

HTML Expression

Classic Reports
Interactive Report

select b.id
     , b.book_name
     , b.author
     , b.year
     , b.summary
  from books b
order by b.book_name
<h2 class="title">#BOOK_NAME#</h2>
<div class="author">by #AUTHOR#</div>
<div class="summary">#SUMMARY#</div>
<span class="copyright">© #YEAR#</span>

Do escape special characters

No need to "Strip HTML"

Remove Headings

Probably turn off

HTML Expression

Demo 400

Patterns For Presenting Data

Tables

Table tags: table, tr, td

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

Lists

List tags: ul, li

List Structure

<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>#CONTENT#</li>

<ul>
</ul>

Before Rows

After Rows

For Each Row

Classic Reports

Generic Columns


Named Columns

Column Template

Row Template

Every column in the SQL is treated the same 

<td>
  #COLUMN_VALUE#
</td>

Generic Columns

Every column looks the same 

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

Using conditions

Column Templates

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

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

  <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

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

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

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

Detail
View

Detail View Structure

<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

For Each Row

Interactive Reports

Detail View

Interactive Grid

Detail View

Interactive Grid

Card View example

Example from John Snyders

John Snyders IG Cookbook

hardlikesoftware.com/weblog/2018/01/01/apex-ig-cookbook-for-5-1-4/

hardlikesoftware.com/weblog/2019/03/30/apex-ig-cookbook-update-for-19-1/

hardlikesoftware.com

Recap

Q&A

Of course, you can do that with APEX!
Report Templates; the definitive guide

Jorge Rimblas

github.com/rimblas/apex-report-templates-demo

@rimblas

rimblas.com/blog/presentations

Of course, you can do that with APEX! Report Templates; the definitive guide - DRW

By Jorge Rimblas

Of course, you can do that with APEX! Report Templates; the definitive guide - DRW

  • 1,393

More from Jorge Rimblas