Classic Report Feature ? Oracle APEX

 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 their 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 an 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) in 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. With these, we can have the cards display either initials or icons - we can have colored cards, control card sizes, and how many cards to display in a row. After changing some of the Template Options, the report looks like this:

As this brief overview of 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.