As an APEX developer, thinking about reports, the following picture comes to mind ... the typical, tabular layout of data.
But Classic Reports can do more - these are, for sure, one of the most versatile components in Application Express. The reason is that classic reports are template-driven. Developers can create own report templates and visualize data however wanted.
But Application Express also provides a few classic report templates - out of the box. These allow data visualizations, which make it hard to believe that there is an APEX classic report behind this.
In this posting, we'll give an overview of the alternative report templates provided with Application Express. A lot of information comes from the Universal Theme Sample Application (apex.oracle.com/ut) - there you'll find even more information on the Look & Feel variants of APEX components.
Now let's get started with the first alternative classic report template. In Page Designer, navigate to your report based on the EMP table, open the Report Attributes property pane and look up the Appearance section.
Start with the Media List template; save your change and re-run the page.
Well ... that's not what we wanted. But it's obvious what happened: the report template expects specific result columns from the SQL query. We can see that even better when looking into the template definition. You'll get there by clicking the > button right to the Report Template select list. Within the HTML markup, placeholders like #ICON_CLASS# or #LIST_TITLE# are easy to find. The report SQL query can (but not has to) provide a value for each placeholder.
So, we'll adjust the SQL query as follows. Alias names will give us the column names the template expects.
select ename as list_title, job as list_text, 'fa fa-user' as icon_class, null as edit_link, sal as list_badge from emp
Save and run the page again ...
Looks better, doesn't it? But there is more - the Media List template provides a few Template Options. Access these in Page Designer by clicking the Template Options button below the Report Template select list. A dialog, similar to the following, will open.
Activate Show Badges and Apply Theme Colors, save the changes and run the page again ...
Just a tiny change to the SQL query, a few mouse clicks - and we have a completely different data visualization. You can use the same process or all other report templates provided by Application Express.
Let's move on to the Comments template. First, adjust the SQL query (the EMP table data is not well suited for that template, but is sufficient to act as example) ...
select ename as user_name, 'My comment is: ' || job as comment_text, hiredate as comment_date, null as user_icon, 'Comment' as actions, ' ' as attribute_1, sal as attribute_2, ' ' as attribute_3, ' ' as attribute_4 from emp
And have a look at the result page:
For the Alert template, not only the placeholder names are important - the value for the #ALERT_TYPE# placeholder is particularly interesting - here's the SQL query.
select ename as alert_title, job as alert_desc, sal as alert_action, case when sal < 1000 then 'info' when sal between 2000 and 3000 then 'success' when sal between 3000 and 4000 then 'warning' else 'danger' end as alert_type from emp
And here's the result. A report row just contains the three values title, desc and action. However, the value of the alert_type column controls the row color and icon - It's good to know the keywords info, success, warning and danger. These follow the semantics of the Application Express Alert region type.
The Badge List template is well suited when your SQL query returns only one row. To display multiple rows, it's not recommended. In this case, the SQL query result column names will directly display in the report output. Here's an example: First (again) the SQL query:
select empno, ename, extract(year from hiredate) as hired, sal, comm, deptno from emp where rownum = 1
The query does not use any special placeholders - column names can be arbitrary. As the result page shows, the template directly displays the column names. This is a good candidate to visualize key-value pairs - but the SQL query must return one row.
The Timeline template specializes (as the name indicates) on displaying subsequent events.
select apex_string.get_initials(ename) as user_avatar, ename as user_name, hiredate as event_date, 'fa fa-user' as event_icon, case deptno when 10 then 'is-new' when 20 then 'is-removed' when 30 then 'is-updated' when 40 then 'is-updated' end as event_status, 'Hired' as event_type, job as event_title, sal || case when comm is not null then ' - ' end || comm as event_desc from emp
Here is the result page - we can see that this template supports many placeholders and therefore many display options.
Search Results provides the typical search engine look & feel. So it's very useful in combination with search functionality. The following SQL query is a (simple) example for that. It filters rows based on user inputs on the P10_SEARCH item.
select ename search_title, job search_desc, null search_link, 'Sal' label_01, sal value_01, 'Hiredate' label_02, hiredate value_02, 'Comm' label_03, comm value_03, 'Deptno' label_04, deptno value_04 from emp where instr(ename||job, :P10_SEARCH)>0 or :P10_SEARCH is null
Of course, that query is not a blueprint for searching on huge amounts of data. The database provides other means of search and filtering in these cases - one example is Oracle Text - but that is a topic for a different blog posting.
A report using the Search Results template will typically look as follows:
Last, but not least, we'll have a look at the Cards template. On the internet, you'll frequently find that kind of data visualization. And with the cards template, it becomes more than easy to apply that look & feel to your table data. First, as always, the SQL query ...
select ename card_title, apex_string.get_initials(ename) card_initials, job card_text, hiredate card_subtext, 'f?p=...' card_link from emp
... and then the result:
The Cards template offers a few Template Options an. With these, we can have the cards display either initials or icons - we can have colored cards, control card sizes and how much cards to display in a row. After changing some of the Template Options, the report looks like this:
As this brief overview on the alternative Classic Report templates illustrates - it's so easy to visualize data in a different look & feel. HTML or CSS knowledge is not required; the only thing a developer has to do, is to adjust the SQL query and the Template Options.
Try it out - today!