As a developer, if you think of a Classic Report, what comes to your mind is a simple tabular presentation of data. With the evolution of Oracle APEX, this component has also changed significantly and has become one of the most versatile components in Oracle APEX. Classic reports have become template-driven and Oracle APEX provides some out-of-the-box templates that make it hard to believe that there is an APEX classic report behind this. A classic report is the formatted result of a SQL query. Developers choose a table on which to build a report or provide a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement. The new classic report templates provide template control over the results of a row from a SQL query and expect specific result columns from the SQL query.
Classic Report Templates are worth exploring. Since the Oracle APEX manual does not seem to address these tools, I’ve decided to slot in this important topic in this tutorial with a practical example that demonstrates the Timeline template of Classic Report, as shown in Figure - 1. Timeline is a Classic Report template that is useful for displaying a series of events. It can be used to showcase the history of a given widget, recent updates, or new interactions within an application.
Each report template
identifies column names using their respective substitution strings #EVENT_TITLE#,
#ALERT_TITLE#, #COMMENT_TEXT#, and so on. For example, the following Timeline
template indicates the names of column aliases to be used in your SQL query to feed the template.
The following table
lists all substitution strings you can use in this template:
|
Substitution String |
Description |
|
#EVENT_MODIFIERS# |
CSS Class-based modifiers for an event item |
|
#EVENT_ATTRIBUTES# |
Custom
attributes for an event item |
|
#USER_COLOR# |
Color
applied to a user initials using u-color-XX classes or custom classes |
|
#USER_AVATAR# |
User
initials or image |
|
#USER_NAME# |
User
name |
|
#EVENT_DATE# |
Date
of event displayed below user name |
|
#EVENT_STATUS# |
Type
of event. Substitute with "is-new", "is-updated" or
"is-removed" for default styling. |
|
#EVENT_ICON# |
Icon
of event type. |
|
#EVENT_TITLE# |
Name
of event |
|
#EVENT_DESC# |
Description
of event displayed below event name |
|
#EVENT_LINK# |
Link
for the event |
|
#EVENT_TYPE# |
Type
of the event |
For this exercise, I
will be using the following two tables. You can locate the script of these
tables and some seed data for the DEMO_TASK table in the SourceCode under the ReportTemplates folder. The DEMO_USERS
table contains users’ images that will be shown in your classic report.
However, you can ignore this table and use an alternate SQL query that displays
users’ initials instead of images. The alternate query is located in the same folder of the source code.
|
DEMO_USERS Table |
DEMO_TASK Table |
|
USERID NUMBER NOT NULL ENABLE |
"ID"
NUMBER NOT NULL ENABLE |
|
USERNAME VARCHAR2(50) |
"TASK_NAME"
VARCHAR2(255) |
|
USERIMAGE BLOB |
"TASK_DESCRIPTION" VARCHAR2(4000) |
|
MIMETYPE VARCHAR2(255) |
"START_DATE" DATE |
|
FILENAME VARCHAR2(400) |
"END_DATE" DATE, |
|
IMAGE_LAST_UPDATE TIMESTAMP (6) WITH LOCAL TIME
ZONE |
"STATUS" VARCHAR2(30) |
|
- |
"USERID" NUMBER |
Here are the steps to create a Classic report using the Timeline template:
1. Create a new page and select Report on the first wizard page.
2. Click Classic Report on the second wizard screen.
3. Enter the number and name of the new page.
4. On the Report Source screen, select SQL Query for Source Type and enter the following SQL statement. If you are not using the DEMO_USERS table, then enter the alternate statement provided in the SourceCode that displays users’ initials.
SELECT t.userid, dbms_lob.getlength(u.userimage) USER_AVATAR,
t.start_date as EVENT_DATE, t.task_name as
EVENT_TITLE,
t.task_description as EVENT_DESC,
CASE t.status
when 'Open' then 'fa
fa-clock-o'
when 'Closed' then 'fa fa-check-circle-o'
when 'On-Hold' then 'fa
fa-exclamation-circle'
when 'Pending' then 'fa fa-exclamation-triangle'
END EVENT_ICON,
CASE t.status
when 'Open' then 'is-new'
when 'Closed' then
'is-removed'
when 'On-Hold' then 'is-updated'
when 'Pending' then 'is-updated'
END EVENT_STATUS,
t.status EVENT_TYPE,
u.username as USER_NAME,
null EVENT_MODIFIERS,
null EVENT_ATTRIBUTES,
null USER_COLOR
FROM demo_task
t, demo_users u
WHERE t.userid=u.userid
ORDER BY t.id desc
5. Click the report's Attributes node and select Timeline for Template attribute. If you run the report now, you won’t see users’ images. Proceed to the next step which will help you display images.
6.
In Page Designer, expand the Columns
node, and click on the USER_AVATAR
column. Set the following attributes for this column. The Display Image type displays the column as an image. The USERIMAGE BLOB column is the
column that stores the images you want to display.
|
Property |
Value |
|
Type |
Display
Image |
|
Table Name |
DEMO_USERS |
|
BLOB Column |
USERIMAGE |
|
Primary Key Column 1 |
USERID |
Save and run the page. The images should now appear in the classic report. Perform the following steps to further tweak the report.
7. Click the EVENT_DATE column, and enter Since in the Format Mask attribute. SINCE is a special format mask, which formats the DATE or TIMESTAMP value as text – the text indicates how much time has passed since that very timestamp.
8. To display a compact version of the timeline with smaller text and fewer columns, click the Attributes node, and set Style to Compact under Template Options.
Save
and run the page to see an output similar to Figure 1.
For Visual Instruction Watch This Video
