Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Conditional Column Formatting in APEX

Posted by Tyler Muth on December 1, 2007

I wanted to share a little trick I’ve used in APEX for a while now to conditionally format report columns based on their values. I’m sure there are plenty of alternatives to this trick, including the 4 built-in conditional alternatives for a row when using a named column template.

At a high level, this technique uses the following components:

  1. A hidden column in the query that returns the formatting attributes for a column. I’m going to return the color or padding-left in my examples later in this post.
  2. Edit the Report Attributes > Column Attributes > Column Formatting > HTML Expression of a visible column and use the hidden column to change it’s formatting. This is the same section you apply a date or number format.

Example 1 – Color Code Salary


select empno,ename,sal,
	   case when sal < 1000 then 'red'
		    when sal between 1000 and 2000 then 'purple'
		    when sal > 2000 then 'green'
	   end the_color
  from emp

Report Attributes > Column Attributes for “SAL” > Column Formatting > HTML Expression

<span style="color:#THE_COLOR#;font-weight:bold;">#SAL#</span>


Example 2 – Employee Hierarchy


select (level*20)-20 the_level,
   from emp
connect by prior empno = mgr
  start with mgr is null

Report Attributes > Column Attributes for “ENAME” > Column Formatting > HTML Expression

<span style="padding-left:#THE_LEVEL#px;">#ENAME#</span>



58 Responses to “Conditional Column Formatting in APEX”

  1. Paulo Vale said

    Hi Tyler,
    Very good post. Usefull to.
    Sometimes, the solution is right in front of your eyes, and all you need is that little “click”.

  2. Tyler,

    Nice trick, simple but very effective! Will definitely be useful.


  3. Carsten said

    Hello Tyler,

    thanks for this post. Very interesting.

    best regards

  4. I like the hierarchy example!


  5. Marcelo Burgos said

    Great examples, Tyler. I like to see when somebody takes the simpler way to the solution.

    In fact, I’m using exactly your “Color Code” solution to manage the look of my app, which has improved dramatically since its use.

    But I yet have one unsolved problem, that I’ve posted (and even replied) in the Apex forum without a solution. Maybe you could answer a simple question:

    I don’t know / don’t found how to do / nobody could explain how to, appliyng the same column color format to the column summary at the end of the report.

    All suggestions tested weren’t conditionally applicable.


  6. Prakash said

    I have developed an Apex application for my senior management,but my database through accessible thru VPN from my desktop.Now senior management do not want to go to vpn to access this application.We have three tier environment and database is in tier 3 and there is web server in tier 1 and Application Server in tier 2.Is there any way to view my Apex application accessible on Web server in tier1,I do not know anything about network architecture.Please helpas otherwise my labour to develop this application will go invain.

  7. Tyler Muth said


    I suggest you post this question on the APEX forum. I’d like to keep the comments related to the post itself.


  8. Vish said


    Can we do something similar for interactive reports ?

    Is there a column formatting HTML Expression tag for Interactive Reports ?


  9. Tarek said


    Thank you for this interesting and very useful techniques.

    However, I have been trying your color method with an interactive report, but I can not make it work.

    Is the color technique compatible with interactive reports? I am trying to color a link column in my interactive report based on the value in another hidden column. I have been trying for days and it is simply not working!

    Thanks for the help.

  10. Ismael said

    Hello Tyler,

    Thanks for this post. Very, very good.


  11. I just like it!

    Is it possible to cahnge the background color of the cell as well?


  12. Basu said

    Pretty good stuff.. i liked the hierarchy more…


  13. Rajinikanth said


    I have following report sample.

    arbuer1 – Router – 15
    Aug-2009 9
    Sep-2009 3
    Jul-2009 3
    ASSET_INFO Total: 30
    ausyap0007 – Windows Virtual – 16
    Aug-2009 7
    Jul-2009 5
    Sep-2009 4
    ASSET_INFO Total: 32

    I would like to like color only for the rows ASSET_INFO Total: 30, ASSET_INFO Total: 32….

    Used GROUP BY ROLLUP(asset_info,month_year) in the Oracle query.

    Can you please advise on this?


  14. Rajinikanth said


    I have following report sample.

    arbuer1 – Router – 15
    Aug-2009 9
    Sep-2009 3
    Jul-2009 3
    ASSET_INFO Total: 30
    ausyap0007 – Windows Virtual – 16
    Aug-2009 7
    Jul-2009 5
    Sep-2009 4
    ASSET_INFO Total: 32

    I would like to like color only for the rows ASSET_INFO Total: 30, ASSET_INFO Total: 32….

    Used GROUP BY ROLLUP(asset_info,month_year) in the Oracle query.

    Can you please advise on this?


  15. Rajinikanth said

    In Oracle Apex, I try using Break formatting to achieve sum by group wise and month wise totals. Here i would like to give by own name for monthwise total and group total. Can you please advise how to achieve this?

    When i followed this step
    Break Formatting -> Display this text on report breaks using #SUM_COLUMN_HEADER# substitutions -> Total :

    I can see Label “Total :” for month and group total, Instead i would like to have as “Month Total :” and “Grand Total :”


  16. Edward said

    Hi Tyler,

    thanks for the trick.
    Unfortunately this html formating doesn’t work for me on APEX 3.1.
    Strange enough… but I even don’t see my input in the source html code of the page. The code seems to be totally ignored by APEX.
    I tryed the following code for report column MODIFIED:

  17. Edward said

    well, again: to apply font-weight:bold and white-space:nowrap styles in span tag for column MODIFIED

  18. Susanna said

    Hi Tyler,

    I found your example1 and liked it very much but unable to implement it in APEX 4.0.

    I cannot find the link you mensioned : “Report Attributes > Column Attributes > Column Formatting > HTML Expression”. There is no “COlumn Formatting” in APEX 4.0.

    Could you please tell me
    1.where should I put the SQL ( with the new hidden column called The_color created)
    2.where should I put the “#SAL#” ?

    Thanks a lot!

  19. Susanna said

    Hi Tyler,

    sorry my 2nd question was:

    where should I find the HTML expression for the column SAL and put the following code there ?


  20. naushad naleer said

    fantastic! I like the first example & I was looking for this everywhere! Thank a lot.

  21. Viji said

    I have a tabular form and am using your example to format a column.
    But when I do that, changes to the column’s values don’t seem to get updated…
    I’m using Apex 3.2

  22. gowthami said


    I found ur example1, i liked it. i used in my application too. but when i am trying to generate a report/view joinin two tables the same values or rows will be shown so many times increasing the row count. y s it so? what can we do to avoid this?


  23. Manash Deb said

    Thank You very much. It was a great post. I was searching for it for last 5-6 hours.

    Once Again, Thanks

  24. Brenda said

    This works great! Thanks for sharing!

  25. shivaji said

    Could you help me how to do it in steps.I have learned apex by own and have build a issue tracking application but now the requirement is to make refelect the rows in colours based on the date.

    I did not get the option for Sir Tyler blog

    In blog its written as

    Edit the Report Attributes > Column Attributes > Column Formatting > HTML Expression of a visible column and use the hidden column to change it’s formatting.

    In my application i came till Home>Application Builder>Application 255>Page 2>Interactive Report Attributes …then whats next…iam lost..

  26. Claudiu said

    On APEX 4.2 it seams is not working though it is a very nice formatting example example,


  27. rads said


    I hope you can help me in this instead of displaying the data in different colors , can I get a small full circle in red, green and yellow colors to display the status of the record ???
    Would appreciate if you can let me know how this can be done !!


  28. S said

    This is a great example. I’m not able to get it to run in Apex 4.1 I’ve added the case statement to the query and added the span tags to the date/number format under the column attribute but I still don’t get the text color change. I’m using an interactive report.

  29. Alex said

    Hi Tyler,

    Don’t want to be too off topic here, but i was wondering if you ever faced the issue of making conditional format for a specific column when the APEX report is generated using dynamic SQL ?

    Im using the PIVOT function to generate a percentage report, and i have this kind of structure :
    Activity1, Activity2, Activity3 , Section1Total, Activity1, Activity2, Section2Total etc.

    The activities columns vary according to the page filters. So you could have any number of sections/activities per section

    For apex to accept this i use “Use Generic Column Names (parse query at runtime only)” and have a PLSQL block to generate the columns names colon separate (im making sure the columns are in the same sort order)

    And now finally the question is : how do i just format the “Section” columns. I cant just put them on “number 3” column 3 from the report because that is not always the case.

    Sorry again for the long and slightly off topic comment here.


  30. L Lemahieu said

    interactive reports

    I did like this.

    select empno,ename
    , to_char(‘<span style="color:')
    case when sal 2000 then ‘green’
    to_char(‘;font-weight:bold;”>’)|| sal || to_char(”)sal
    from emp

    Gives me the same as in a classic report. Just define the column sal as standaard report (not display as text)

  31. L Lemahieu said

    Interactive Report

    select empno,ename
    , to_char(‘<span style="color:')
    case when sal 2000 then ‘green’
    to_char(‘;font-weight:bold;”>’)|| sal || to_char(”) as sal
    from emp

  32. Samip said

    This is totally very interesting post here. I am delighted.

  33. Riya Singhal said

    Works great..Simple and more things can be done in this way..Thanks for sharing

  34. Ghislain said


    Your solutions work for me, I tried other solutions out there, they didn’t work.. I love simple way of doing things that don’t make code clamzy.

    Thank you.

  35. Ludo said

    i m using it since a while , but i had this issue.

    if you decide to hide your COLOR colum. it’s no more possible to change the color because this COLUMN has no output.

    Maybe i din’t understood how “not to diplay” the column but return it.

    so i’m using jQuery to hide (display:non) the color column ….

    is there another solution to hide the colum ?

  36. Ghislain said


    I was asked to create a report in APEX that will generate a report in PDF. The report will have the first column that will print Institution names, the subtotal should print the institution sector (eg: research university or state University etc..) in the institution name, it also should be in italic and bold and indented about 5 point and the total should print “Total” in the Institution column indented, bold and italic. All the subtotals and total values must be bold and italic. At least bold if not italic.and the second column will be values for headcount. To demo this, I used Oracle OEHR data where I can print Employee name and will print for subtotal the employee job ittle and the total emploees.

    I want to have the result without rows and columns borders as well as no background color. I want to print the subtotal and total in bold and italic. Can you advise how to solve this problem?

    select NVL(OEHR_EMPLOYEES.FIRST_NAME, NVL( OEHR_JOBS.job_title, ‘Employee Total’)) as Employees,
    where OEHR_EMPLOYEES.job_id = OEHR_JOBS.job_id
    group by rollup(OEHR_JOBS.job_title, OEHR_EMPLOYEES.FIRST_NAME)
    order by OEHR_JOBS.job_title

  37. What a material of un-ambiguity and preserveness of precious know-how on the topic of unpredicted feelings.

  38. writing said

    Hi would you mind sharing which blog platform you’re working with?
    I’m planning to start my own blog soon but I’m having a hard time selecting between BlogEngine/Wordpress/B2evolution and Drupal.

    The reason I ask is because your design and style seems
    different then most blogs and I’m looking for something
    unique. P.S My apologies for being off-topic but I had to ask!

  39. To the totality of facts can determine what is the case, and also whichever is not the case.

  40. I think the admin of this web site is in fact working hard in favor of his site,
    because here every stuff is quality based stuff.

  41. Excellent post however I was wondering if you could write a litte more on this subject?
    I’d be very grateful if you could elaborate a little bit more.

    Appreciate it!

  42. Giuseppe said

    Thanks for finally writing about >Conditional Column Formatting in APEX Tyler Muths Blog <Liked it!

  43. Lee Anne said

    This was a perfect post and helped me give my customer what they needed! Thanks!!


    Super Trick..Man

  45. Daniel said

    Very useful, I just used for APEX 5 and still works. In 4.2 I was passing the html format with the conditional case for the record but I was not able to replicate the process in 5.



  46. GSA said

    Great post. I was looking for the same solution. God bless you

  47. Mia said

    Quality posts is the main to interest the visitors to pay a quick visit the web site, that’s what this site is providing.

  48. Rahul Kumar said

    I have Interactive report. I do not see options like column formatting anywhere.

    • Steve Bovin said

      I know this is 2 years old byt … same here using ApEx 4 and 5.
      Can someone explain how to get to the column formatting options

  49. I was looking for a solution like this for hours !!!

    You’re a life saver.

    Thanks a lot for this trick.

  50. Jit said


    Is there any way please to highlight entire row based on particular column value. I would like to change the background color of that row record in which column value is less with defined threshold.

  51. John Foster said

    Great – this was very helpful! Question – is there a way to get the formatting done this way to show up in the Print out when you use the Print feature of a report? I have used your example and underlined a value based on some condition. Works great but the underline doesn’t carry over into the Print. Thanks

  52. easyoradba said

    Reblogged this on ..:::: EasyOraDBA | Shadab Mohammad ::::...

  53. simple but very effective & useful !!!.

  54. rhea said

    super boss, thanks so much. it worked well to me

  55. m.ogun said

    Reblogged this on simple..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: