Of course, you can do that with APEX!
Report Templates; the definitive guide
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/1424642/oracle_ace_logo.png)
Jorge Rimblas
Jorge Rimblas
Senior APEX Consultant
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/1424642/oracle_ace_logo.png)
- Oracle DB since 1995
- APEX since it was HTMLDB in 2004
- Always involved in web technologies
- jrimblas in OTN Forums
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/1503244/apeks_ace_avatar_x46.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/1689396/9781484204856-sm.jpg)
- Contributor to
"Expert Oracle Application Express, 2nd Edition"
with "Themes & Templates" chapter
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2218866/insum_logo_sm.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/5273138/Twitter_Social_Icon_Rounded_Square_Color.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4982934/Logo_oracle_gold_partner.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2218866/insum_logo_sm.png)
Custom Development, Cloud, Forms Modernization,
EBS Extensions
Consulting, Coaching, and Team Augmentation
Maintenance and Enhancement Service for apps built with APEX
Committed to APEX Innovation & Best Practices
Internationally recognized staff that includes Oracle ACEs
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4982909/Fast500-Winner.png)
Goals
Be comfortable enhancing report layouts
- Patterns
- Classic Reports
- Classic Reports: Custom Named Templates
- Interactive Reports
- Interactive Grids
- Single Line Alert & Messages
Agenda
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>
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2342142/identicall_td_elements.png)
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
Examples
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2319737/case_phases.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369183/maintain-client.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2342251/final_generic_template_w_condition.png)
Classic Reports
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2322859/tennis_shoes_table.png)
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>
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2342142/identicall_td_elements.png)
Using conditions
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2342251/final_generic_template_w_condition.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2370158/final_generic_template.png)
Column Templates
- Four Column Templates
- Condition for each template
- Condition types: even or odd rows, PL/SQL Expression
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2340800/column_template_conditions.gif)
Step by step details
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2342251/final_generic_template_w_condition.png)
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>
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2348355/simple-todo-v1.png)
select id
, todo
, decode(completed_by
, null, ''
, 'check' || '-') checked
, completed_by
, completed_on
from app_todos
Benefits
- Standard Classic Report Functionality
- Edit Links
- Dynamic Actions
- Dialog Close
- Refresh
Named Columns
Mixing Layouts
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369183/maintain-client.png)
Contact List
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369183/maintain-client.png)
Two types of rows
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369202/maintain-client-two_rows.png)
<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>
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369239/select_parties.png)
<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
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369275/select_parties_1.png)
<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
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369239/select_parties.png)
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
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2372157/select_parties_analytics.png)
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
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2369183/maintain-client.png)
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
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>
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4936879/html-expression.png)
Do escape special characters
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/6137863/escape-chars.png)
No need to "Strip HTML"
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4936893/htmlexpression-settings.png)
Remove Headings
Probably turn off
HTML Expression
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4936885/html-expression---books.png)
Demo 400
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
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/2377906/IR_template.gif)
Interactive Grid
Detail View
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4912256/ig_detail_view.gif)
Interactive Grid
Card View example
Example from John Snyders
Single Line Alerts & Messages
Examples
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979571/billing-generate-transactions.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979581/wrong-roles.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979571/billing-generate-transactions.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979616/generate-transactions-builder.png)
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
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979730/html-expression.png)
Columns are still escaped!
Use any HTML you want
Remove Pagination
Under Report Attributes
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979774/no-pagination.png)
Remove Headings
Under Report Attributes
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979768/remove-headings.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979581/wrong-roles.png)
Heading
Value
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979581/wrong-roles.png)
Heading
Value
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979806/heading-value.png)
Report Template: Vertical Report
Under Report Attributes
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/4979811/vertical-report.png)
Change Template Type
Also, turn off Pagination!
Demo p500
Recap
Q&A
This is a good time to fill out the evaluations
Of course, you can do that with APEX!
Report Templates; the definitive guide
Jorge Rimblas
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/1424642/oracle_ace_logo.png)
@rimblas
![](https://s3.amazonaws.com/media-p.slid.es/uploads/32280/images/5273138/Twitter_Social_Icon_Rounded_Square_Color.png)
Of course, you can do that with APEX! Report Templates; the definitive guide
By Jorge Rimblas
Of course, you can do that with APEX! Report Templates; the definitive guide
- 3,643