Hooked on Classic (Reports)
Learn tips & techniques to make them sing
Jorge Rimblas
Jorge Rimblas
Senior APEX Consultant
- 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
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_todosDemo
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 = 41For 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
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 = 1Putting 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
- Demo App apex.oracle.com/pls/apex/f?p=89361 [ Download]
- Demo Video
rimblas.com/blog/2016/06/apex-classic-report-named-column-templates - Using Conditions with Column Templates
rimblas.com/blog/2014/05/using-conditions-with-column-templates/ - KISS Analytics playlist
https://www.youtube.com/playlist?list=PLJMaoEWvHwFJDyhMLCkNSSUQWw9waFkIj - APEX Reports: Color Row Based On Column Value
ww.eberapp.com/pls/apex/f?p=BLOG:READ:0::::ARTICLE:5983100346022749 - APEX Reports: Custom Layout with Named Column (Row Template)
ww.eberapp.com/pls/apex/f?p=BLOG:READ:0::::ARTICLE:5976400346831048 - Reinventing APEX Reporting
morneau.me/reinventing-apex-reporting/ - Oracle APEX 5 Classic Report Templates
storm-petrel.com/orablog/2015/12/20/oracle-apex-5-classic-report-templates/
Q&A
Hooked on Classic (Reports)
Jorge Rimblas
apex.world Your one stop for everything APEX
1.1
Hooked on Classic (Reports)
Learn tips & techniques to make them sing
Jorge Rimblas