The Classic and Interactive report regions in Oracle’s Application Express have plenty of options to format the data to suit your taste – if you stick with a basic tabular layout. In most cases, that’s perfect, after all SQL is meant to dish up structured data. But, sometimes the tabular layout option does not do justice to certain columns. For example, records with larger text columns used to capture user notes along with a bit of meta about that record (status, date, who etc.) is not displayed quite so elegantly in Apex, in particular if you have a lot of columns.

Consider this table:

CREATE TABLE NOTES (
	ID INTEGER PRIMARY KEY,
	STATUS VARCHAR2(10),
	USERNAME VARCHAR2(20),
	CREATED_DATE DATE,
	TEXT VARCHAR2(4000)
	);

After inserting some rows, and creating a basic Apex Classic report, the report may look something like this:

HTML Classic Report

Not bad, but if you have a lot of meta columns the most important column Text will get scrunched up and take up a lot of vertical room. Instead, why not display the meta in a row along the top of each entry, with the text as a sub row spanning several columns?

Inspecting the page source of the last column using Firebug you can see that Apex simply wraps each column in a pair of table data tagsĀ <td></td> (If you don’t use the Mozilla Firefox add-on Firebug as a development tool, I say stop and go get it right now!)

HTML Report Table Structure

If we edit the report SQL source from this:

select id,
	status,
	username,
	created_date,
	text
from notes

And inject a little extra HTML concatenated to our last column:

select id,
	status,
	username,
	created_date,
	'&lt;/td&gt;&lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td colspan="3"&gt;'||text||'&lt;/td&gt;&lt;/tr&gt;&lt;td&gt;' text
from notes

We can force the text column to it’s own sub-row.

Here’s what’s happening:

  1. The first </td> tag closes the Apex opening table data tag.
  2. The next <tr> tag opens a new row.
  3. Then the next pair of table data tags <td></td> are used to fill in the first column under the Edit link (just for a clean look).
  4. Then we open a new table data tag <td colspan="3"> for our text column with a colspan="3" so it lays nicely across the entire report row.
  5. Then we close our column table data tag and this extra row </td></tr>.
  6. Finally, we open an extra table data tag <td>. Why? Because Apex will try to automatically close the original Text column into which we just inserted our custom HTML. This open tag will match the closing tag and keeps the HTML valid.

One last detail, we are effectively removing the Text column as a column, so change the column title to null to hide it. Now let’s see how it looks:

Apex HTML Report Modified Layout

Hey, that’s way more readable. We can take this one step further, and conditionally set the background color for each Text row based on the status. By using the DECODE() function and adding a custom class to each row:

select id,
 status,
 username,
 created_date,
 '</td><tr><td></td><td class="'||decode(status,'Open','opennote','Closed','closednote')||'" colspan="3">'||text||'</td></tr><td>' text
 from notes

And add some simple CSS to the page header:

<style type="text/css">
.opennote {background-color: #FFF4B5;}
.closednote {background-color: #A6D5F3;}
</style>

Now check out the report:

Improved Apex HTML Report Layout

I expect this technique will not work with an Interactive Report, but sometimes a plain old Classic region is all you need.

Edit: You may need to adjust the Report Attributes > Layout and Pagination options to suit your theme or particular needs. This technique should work with most Report Templates, but the most reliable template is the default HTML option; the other templates may affect the look and feel. Also, you may need to set the option to Strip HTML to No. For the above example I had this set to Yes and it still worked, but you might have better success setting this to No. Finally, this example was created using Apex 3.2.0.00.27, I have not tried this in 4.0.

Report Settings

7 thoughts on “Apex HTML Report Layout Options

  1. Hi , i tried your method , but when run the report it shows ‘<td also, is there any other plug in installation required to combine html and sql ?
    Thanks

    Reply
    • Hi, there is no plugin required beyond the basic Apex install; just plain SQL and HTML. Check your Report Attributes and try changing the option to Strip HTML from Yes (the default) to No. Also, try experimenting with changing your selected report template, I used the default HTML template for this example.

      Another possibility is an extra quote which is escaping this HTML string. Can you post part of your SQL query, the selected column with the concatenated HTML?

      Reply
  2. Hi,
    I tried the same as you specified. But it just displays the tag onto the screen.

    My sample code is ,
    select
    ” ||
    EMPLOYEE_ID || ” E_id,
    “FIRST_NAME”,

    ”||FIRST_NAME||” F_name,
    “LAST_NAME”,
    “HIRE_DATE”,
    “SALARY”,
    “DEPARTMENT_ID”
    from “#OWNER#”.”OEHR_EMPLOYEES”

    Regards.

    Reply
  3. This is old thread I can tell but I’m having the same difficulties as others. I’m using the strip HTML option but the HTML tags are still getting converted.

    I’ve also tried making a custom format in the report templates by putting my column strings #COLUMNNAME# but it looks like this isn’t allowed anymore, though it must have been at one time http://apex.oracle.com/pls/otn/f?p=11933:108 is an example.

    I’m wondering if maybe you are using an older version of APEX.

    Reply
    • Hi, I just tested this solution in Apex 4.1 and it still works fine (my original post was done in Apex 3.1). The key is to check the Report Attributes > Column Attributes and for “Display As” set to Standard Report Column.

      Reply
  4. 1. I use HTML code in my SQl as well. For example:
    ”||s.statusdesc||’: ‘||f.folderdescription

    2. In “Column Attributes”, I changed from “Display as TEXT (Escape…” to “Standard Report Column”.

    3. This works well for my applications in BOTH 3.1 and 4.0

    Reply

Leave a reply

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>