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

Query

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>

Result
salary_color

Example 2 – Employee Hierarchy

Query

select (level*20)-20 the_level,
        empno,ename,mgr
   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>

Result
employee_hierarchy

About these ads

43 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”.
    Congratulations.

  2. Tyler,

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

    Anthony.

  3. Carsten said

    Hello Tyler,

    thanks for this post. Very interesting.

    best regards
    carsten

  4. I like the hierarchy example!

    Patrick

  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.

    Thanks.

  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

    Prakash,

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

    Tyler

  8. Vish said

    Hi,

    Can we do something similar for interactive reports ?

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

    Regards

  9. Tarek said

    Hi,

    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.

    Ismael

  11. I just like it!

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

    Tnx
    Usn

  12. Basu said

    Pretty good stuff.. i liked the hierarchy more…

    regards
    Basu

  13. Rajinikanth said

    Hi,

    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?

    Regards,
    Rajinikanth

  14. Rajinikanth said

    Hi,

    I have following report sample.

    ASSET_INFO MONTH_YEAR SUM(INCIDENT_TOTAL)
    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?

    Regards,
    Rajinikanth

  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 :”

    Regards.

  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:
    #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 ?
    #SAL#

    Regards
    Susanna

  20. naushad naleer said

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

  21. Viji said

    Tyler,
    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
    Thanks,
    Viji

  22. gowthami said

    hi

    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?

    Thanks,
    Gowthami

  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,

    Claudiu

  27. rads said

    Hi

    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 !!

    Thanks

  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.

    Cheers,
    Alex

  30. L Lemahieu said

    interactive reports

    I did like this.

    select empno,ename
    , to_char(‘<span style="color:')
    ||
    case when sal 2000 then ‘green’
    end
    ||
    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’
    end
    ||
    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

    Tyler,

    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

    Tyler,

    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,
    sum(OEHR_EMPLOYEES.SALARY) as SALARY,
    from OEHR_EMPLOYEES OEHR_EMPLOYEES, OEHR_JOBS OEHR_JOBS
    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!

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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 90 other followers

%d bloggers like this: