Friday, January 8, 2016

Coloring Cells in an Interactive Report

This is a quick post on something that came up on slack this week.
The task was basically to highlight certain cells in an interactive report based on the values in another column. While the highlighting feature in the interactive report is great that's one thing you can't do with it. You have the choice of highlighting the whole row or the cell in the column on which you based the condition. Another situation where the builtin highlighting isn't enough is when you need some complex conditions involving more than one column.

Took some iterations, but here's one solution to this.
I'll first outline the building blocks and then explain how they work together including some failed attempts on the way getting to this.
Interactive Report Query:
SELECT empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno,
       CASE WHEN sal <= 1500 THEN 'data-style="background-color:green"' 
            WHEN sal > 1500 AND sal <= 3000 THEN 'data-style="background-color:orange"'
            WHEN sal > 3000 THEN 'data-style="background-color:red"'
            ELSE NULL
       END css_style
  FROM emp

Then set the css_style column to hidden and set the "HTML Expression" attribute on the column you want colored to something like the following.
Note: I use the SAL column here, but you can really use any column.
HTML Expression of column SAL:
<span #CSS_STYLE#>#SAL#</span>

The last bit is some JavaScript code which you put into a dynamic action firing on event "After Refresh" for the interactive report region. Make sure to also set "Fire On Page Load" to yes, otherwise you have no coloring after page load.
JavaScript Code:
apex.jQuery("span[data-style]").each(
  function()
  { 
    apex.jQuery(this).
      parent().attr( 'style'
                   , apex.jQuery(this).attr('data-style')
                   ); 
  }
);

If you don't want to read the full story you can stop reading here and have a look at the example app to be found at https://apex.oracle.com/pls/apex/f?p=83537.

Still reading?
Let's take a look at how we got to a working solution and how it does it's magic.

In the query we are laying the foundation by adding a column which computes the needed style for us. You might wonder why there is "data-style" instead of simply "style". Let me show you how the report would look if you would just be applying a style to the span element.
Just styling the span element

This doesn't look good and the reason is that the span element is an inline element only using the space it needs and not filling all available space as block elements do. Next try would be setting the span element to behave like a block element by adding display: block to the style definition.
Styling the span element with added display:block
A bit better, but this still isn't coloring the whole cell. To keep it short there where some other tries using some CSS-Wizardry which didn't work out too good either.
Back to the "data-style" thing. You are allowed to add your own custom attributes to HTML elements if you prefix them with "data-". Browsers will ignore them but from JavaScript or in this case jQuery you can access and utilize those attributes.
Let's have a closer look at the JavaScript code and walk through the logic.
  1. Select all span elements which have an attribute called data-style
    apex.jQuery("span[data-style]")
  2. Loop through the jQuery array and call an anonymous function for each item
    .each( function() {
  3. Find the current item's parent element (that's the for the cell)
    apex.jQuery(this).parent()
  4. Set the parent's style attribute using the span's data-style attribute
    .attr('style', apex.jQuery(this).attr('data-style'))
Finally we had something which looks very much like the inbuilt highlighting:


So long folks, this post actually got longer than I expected.

Happy IR-Hacking :-)

26 comments:

  1. Mr. Moritz,

    I implemented until the dynamic action with javascript, on APEX 5, but without results. The columns did not changed.

    Regards,

    Gustavo

    ReplyDelete
  2. Mr. Moritz,

    I forgot the HTML EXPRESSION feature. Now, it's works fine!

    Thanks!

    ReplyDelete
  3. Thank you for this blog. Helped me dynamically highlight changes cells in a report.

    ReplyDelete
  4. Hi this solution is not working when I am changing pagination to see more records. Please let me know what should I do to execute it when moving previous and next

    ReplyDelete
    Replies
    1. Hi,
      I just tried this in the demo app I have on apex.oracle.com and it works with pagination.
      Maybe you could download my demo app and then compare with what you have.
      If you happen to have this also on apex.oracle.com maybe I could have a look?

      Regards,
      Moritz

      Delete
  5. Changing event from "After Refresh" to "Change" and selection type to "jQuery Selector" and jQuery Selector to "#apexir_WORKSHEET_REGION" makes dynamic action works in single row view as well.

    ReplyDelete
    Replies
    1. I tried but it is not showing up the entire row colored, Please help did I miss anything?

      Delete
  6. When using this with tabular forms i had trouble with the " quotes breaking html/css markup.

    The solution for me was to use no quotes in the SQL:

    case when length(TXT) > 150 then 'data-style=background-color:red' else null end as css_style

    ReplyDelete
    Replies
    1. Hi,
      Are you able to edit the highlighted column when used in tabular forms?
      Kindly advise.

      Thanks,

      Delete
    2. Change the Javascript to go up another parent and then select all children.
      You basically walk up to the table row and then select all children of that row, meaning all columns in that row.
      Code:
      apex.jQuery("span[data-style]").each(
      function()
      {
      apex.jQuery(this).
      parent().parent().children().attr( 'style'
      , apex.jQuery(this).attr('data-style')
      );
      }
      );

      Delete
  7. I was having issues with quoting too, but I just set the "Escape special characters" property for the "CSS_STYLE" column in the IR to "No" and all was well.

    ReplyDelete
  8. I am unable to edit the highlighted column when I use the above code in Tabular Form.
    Any solution for this issue?
    Kindly advise.
    Thanks.

    ReplyDelete
  9. How to do it if I want to apply the background color for the complete row?

    ReplyDelete
    Replies
    1. You can use following code to apply the style to all columns within the same row:
      apex.jQuery("span[data-style]").each(
      function()
      {
      apex.jQuery(this).
      parent().parent().children().attr( 'style'
      , apex.jQuery(this).attr('data-style')
      );
      }
      );

      Regards,
      Moritz

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. What do I have to change, so that it works for the interactive grid?
    Thanks

    ReplyDelete
  12. this does not work for interactive grid, any idea how that should be?

    ReplyDelete
    Replies
    1. http://apex-base.com/2017/11/25/interactive-grid-change-cell-background-color/

      Delete
  13. Hi,
    I use your code for interactive grid
    I have table that has backgroundcolor and fontcolor field so they save in db
    now I add link in my grid that open form so user can change font color and back ground color
    I have dynamic action(event:after refresh/selection type :jquery selector/jquery selector:#mystatic id) and use your js code in dynamic action(Fire on Initialization:yes)
    when I change color in form, It doesn't work.
    can you help me?
    Thanks

    ReplyDelete
  14. Excelente. gracias por compartir

    ReplyDelete
  15. Thank you, I was looking for this solution for days and it works. Perfect.

    ReplyDelete
  16. Love this feature, but it stops working if column is defined as a link, is there any way round this.

    ReplyDelete
  17. Hi Moritz,

    Thanks so much for this, it's been a great help!

    Only issue I'm having is that when I update or apply changes to the interactive report it loses the colour and only returns if you refresh the page. If you change the number of rows visible on the interactive report it also does the same thing - colours disappear and then only returns on refreshing the page.

    There was no option to "Fire on page load" to yes so I added another dynamic action to run the javascript on "Page Load" but still not working :( Please help!

    ReplyDelete