Wolfgang Moritz

Call me Wolf

Apex HTML Report Layout Options

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: Classic Apex report layout

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.)

Inspecting the DOM

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,
  '</td><tr><td></td><td colspan="3">'||text||'</td></tr><td>' 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:

After modifying the report

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:

Styled report

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.

Apex report options


Leave me a Comment

* required


Comments

Gokmen March 15, 2011
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
Wolfgang Moritz March 15, 2011
Boris November 24, 2011
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
Wolfgang Moritz December 25, 2011
Matthew Swaringen December 30, 2011
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
Wolfgang Moritz April 26, 2012
Dan Nguyen September 6, 2012
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