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

Using CodeMirror in your APEX Application

I just had a look at a blog post by Connor McDonald where he's asking for opinions how code should be presented on the AskTom website.
In my opinion I would love to have the line numbers removed from the actually code and instead let the get generated by a code highlighter.
With APEX 5 the development team uses CodeMirror for the App Builder, meaning the needed library is already included with every installation of APEX 5.

Getting your code highlighted

First of all I'm assuming you put your code into some sort of a tag, otherwise it wouldn't possible to distinguish code from other text. I did a quick check on the AskTom website and they seem to use the <pre> tag, so I'll use the same to demonstrate. Let's prepare a single page for displaying nice highlighted code.

Update page JavaScript and CSS attributes

Add following to JavaScript File URLs:

#IMAGE_PREFIX#libraries/codemirror/4.4/codemirror-custom.min.js
#IMAGE_PREFIX#libraries/codemirror/4.4/mode/sql/sql.min.js

Add this to the "Execute when Page Loads" sections:

apex.jQuery(".code").each(function() 
{ 
  var myThis = apex.jQuery(this);
  var myCode = myThis.html();
  var myMIME = myThis.attr("data-code-mode");
  myThis.empty();
  CodeMirror(
    this,
    {
      value: myCode,
      mode: myMIME,
      lineNumbers: !myThis.is('.nolinenumbers'),
      readOnly: true
    }  
  );      
});

This code searches all elements having class "code" and uses the contained html as the code to highlight. The language is derived from an attribute called "data-code-mode", if you want to use something else than what is included in the sql.min.js file you need to include additional files from  the CodeMirror mode directory.

In CSS File URLs add the CodeMirror gloabl css file:

#IMAGE_PREFIX#libraries/codemirror/4.4/codemirror-custom.min.css

Finally specify this for Inline CSS:

.CodeMirror {
    height: auto!important;   
}

Adding code blocks

Now just add a new region to the page, I have used static content for now as this is just an example.
The content of the region is the following:

This is some text first...
Now show code:
<pre class="code" data-code-mode="text/x-plsql">DECLARE
  l_data VARCHAR2(32767);
BEGIN
  NULL;
END;</pre>
More text below code block.
now using a div for code:
<div class="code" data-code-mode="text/x-plsql">
SELECT user
  FROM dual;</div>
Nice, isn't it?

Finally some code without line numbers 
<span class="code nolinenumbers" data-code-mode="text/x-plsql">SELECT * FROM emp;</span>
where we just select everything from the famous emp table.

And the rendered result looks like this:

This is all more or less a quick hack, but I hope you get the idea and can make some good use of it.

APEX jQuery Modal Dialog Oddities

Lately I have been working on creating a new version of my demo application for the APEX IR XLSX Downloader.
The package provides a set of configuration parameters which I thought would be nice to present to the user instead of being solely defined by the developer.
During that I encountered some oddities when using a jQuery Modal Dialog to render a region as a modal dialog.

Note: I'll show you the steps to reproduce and fix in a newly created application. But you can also just create a new page in an existing application.

Steps to reproduce and fix:

1. Navigate to the page where you want to put the modal region containing form elements.
2. Create a new region accepting all defaults
3. Create a button in that region and set Action to "Defined by Dynamic Action"
   
   
   
4. Create a new region accepting defaults except for "Display Attributes"
   Select "jQuery Modal Region Template" as "Region Template"

5. Create at least one item which should save session state on the modal region.
   I used a text item and made it submit on enter for this example.
   
   
6. Create the dynamic action reacting to button click.
   
   
   
   
   
   
   
   


7. Run a simple test
1. Run the page
2. Click open Dialog button
3. Enter something in the text field and hit enter.
4. Now verify session state
   
   
5. So we submitted the page with a new value in the item, but it doesn't look like it was pushed into session state.
8. Finding the reason for session state not being updated.
1. Analyze DOM before dialog open.
   
   
2. Analyze DOM after dialog open.
   
   
9. This tells us that the region gets moved outside of the <form> container, making the item invisible to APEX.
   Therefore no update in session state.
10. Steps to fix DOM
    1. Create Dynamic Action which fires "Before Page Submit"
    2. Set action to "Execute Javascript Code":
       

       apex.jQuery( this.affectedElements ).dialog( "destroy" ).appendTo( 'form#wwvFlowForm' );
       

    3. Set "Affected Elements" identical to previous Dynamic Action.
11. Test again with same steps as noted in 7.
    This should now work.
    
    
    
    
    
    
    
12. Enjoy nicely working modal dialogs.