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

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

Of course, you can do that with APEX!

Report Templates; the definitive guide

Jorge Rimblas

Goals

Agenda

Examples

Single Line Alerts & Messages

Examples

HTML Expression

Remove Pagination

Remove Headings

Report Template: Vertical Report

HTML Expression

Classic Reports Interactive Report

Do escape special characters

No need to "Strip HTML"

Remove Headings

Probably turn off

HTML Expression

Patterns For Presenting Data

Tables

Table tags: table, tr, td

Lists

List tags: ul, li

List Structure

Classic Reports

Generic Columns Named Columns

Column Template

Row Template

Every column in the SQL is treated the same

Generic Columns

Every column looks the same

Using conditions

Column Templates

Step by step details

Named Columns

SQL

Named Columns

Generic Columns

SQL

Named Columns

Positional Columns

Simple List

Benefits

Named Columns

Mixing Layouts

Contact List

Two types of rows

For a single row

Use both types of rows

For a single row Or the 1st Party Row

Use both types of rows

Analytic Functions

Analytics

count(*) as analytic

KISS series on Analytics

Contact List

Limitations

Detail View

Detail View Structure

Interactive Reports

Interactive Grid

Interactive Grid

Recap

Q&A

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

Classic Reports
Interactive Report

Generic Columns

Named Columns

For a single row
Or the 1st Party Row

Detail
View

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