Oracle APEX

Build beautiful and interactive API documentation for ORDS

2023-07-13T11:12:00.005+01:00

In this blog post, I will show you how to quickly build beautiful and interactive API documentation for your Oracle APEX REST data sources using swagger hub. Using APEX v23.1.

I downloaded the titanic data set and loaded them into tables in my APEX instance, created some authorized restful services and published them using swagger hub.

You can create a free account on swagger hub. Check out my titanic swagger hub here;

Press Authorize. Username REST, password Glasgow123!

I won't go through creating RESTful services and just show you the four I created that sit on top of the titanic data set;

The GET is a very simple SQL query;

select * from TITANIC_DATA_SET_NEW

A handy tip is to add comments, as there will appear on swagger hub, making your API self documenting;

Once you have created your modules, press the Generate Swagger Doc button;

This will generate an open API for you. Copy the API and paste it into swagger hub

This will generate the documentation;

As mentioned before, the comments you add in APEX will appear in the API, self documenting!

Same for any parameters you create, these are comments I created in APEX for the parameters. Male or female, giving the API user some info on what is required for this parameter;

The real power of swagger hub is it builds client libraries and documentation for your API. Press Export on the top right of the swagger hub page;

For example, choosing Java will download a zip

Giving example code to use your API.

Oracle APEX pretty checkbox item plugin

2022-09-28T11:35:00.008+01:00

Following on from the animated on off button this is now an item plugin. Pretty up your checkboxes.....

You have 16 checkbox types to choose from where you can control the size and colour of the checkbox in item settings;

demo

Create a page item with type Pretty check box

Choose type of checkbox

Set checkbox attributes.

How it works

The item plug in code uses this code to plug the rendered checkbox into APEX.

///////////////////////////////////////////////////////////////////////////////////////////
// glasgow_simple_checkbox
//
// Provides a plug-in specific implementation for a checkbox item. The item on the page
// should be a checkbox, i.e. <input type="checkbox" and name should be your item name
// i.e. name='P12_NAME'
//
// Params
//
// itemName - the item name, i.e. name='P12_NAME'
// checkboxValues - the check and unchecked values, i.e. Y/N
///////////////////////////////////////////////////////////////////////////////////////////
function glasgow_simple_checkbox(itemName, checkboxValues) {
    // get the checkbox using JQuery
    var checkBox = apex.jQuery("#" + itemName);

    // when the checkbox changes (clicked) set the item value
    apex.jQuery("#" + itemName).change(setItemValue);
    
    // set the checkbox item value depending on whether its checked or not.
    function setItemValue() {
        // the checkbox has been clicked, set the value 
        // depending on selection - checked or not checked
        checkBox.val((checkBox.is(":checked") === true) 
          ? checkboxValues.checked : checkboxValues.unchecked);
    }

    // apex.item.create 
    //
    // Provides a plug-in specific implementation for the item.
    // This is where we get APEX to set/get the item using the values on the page.
    //
    // Params
    //
    // itemName : Page item
    // functions needed to customize the Application Express item object behavior.
    apex.item.create(itemName, {
      
        setValue: function(f) {
            console.log('set value ' + checkBox.val());
            setItemValue()
        },
        getValue: function() {
            console.log('get value ' + checkBox.val());
            return checkBox.val()
        }
    }); 
    
};

We render the checkbox on the page using this code in the plugin which uses the user attributes choosen;

procedure renderPrettyCheckbox(p_css_file_name in varchar2,
                                p_outer_div_class_name in varchar2,
                                    p_icon_html in varchar2) is
    begin
         apex_css.add_file(
             p_name      => p_css_file_name,         
             p_directory => p_plugin.file_prefix,
             p_version   => null );

          sys.htp.prn ( 
            '<div style="font-size:'||l_font_size||'em" class="'||p_outer_div_class_name||'">'||
                '<input type="checkbox"  id="'||p_item.name||l_checkbox_postfix||'" '||  ' name="'||l_name||'" '||
                    'value="'||l_value||'" '||
                        case when l_value = l_checked_value then 'checked="checked" ' end||
                        case when p_is_readonly or p_is_printer_friendly then 'disabled="disabled" ' end||
                '/>' ||
                ' <div class="state '|| l_pretty_on_colour || '">
                '||p_icon_html||'
            <label></label>
        </div>' ||
            '</div>'); 
    end;

Each check-box type selected calls the above code, for pretty jelly checkbox we have this;

  renderPrettyCheckbox('pretty-checkbox',
                                'pretty p-icon p-round p-jelly',
                                    '<i class="icon fa fa-check"></i>');

Oracle APEX animate yes-no button

2022-09-07T11:42:00.001+01:00

This post will show you how to add an animated on-off yes-no button to your APEX application and link it to a page item.

Demo

Create a new page text item, in my case P8_NEW. We are going to use this to store the selected value when the user presses the button

Then create a static region with the following HTML code. When the button gets clicked we set P8_NEW using JavaScript.

<div class="mid">
  <label class="rocker">
    <input type="checkbox" id="myCheck">
    <span class="switch-left"  onclick="apex.item( 'P8_NEW' ).setValue('Yes')">Yes</span>
    <span class="switch-right" onclick="apex.item( 'P8_NEW' ).setValue('No')">No</span>
  </label>
</div>

You can change the button text here to On/Off or whatever you need.

Add the following code to page inline CSS to style the button.

html {
  box-sizing: border-box;
  font-family: 'Arial', sans-serif;
  font-size: 100%;
}
*, *:before, *:after {
  box-sizing: inherit;
  margin:0;
  padding:0;
}
.mid {
  display: flex;
  align-items: center;
  justify-content: center;
  padding-top:1em;
}


/* Switch starts here */
.rocker {
  display: inline-block;
  position: relative;
  /*
  SIZE OF SWITCH
  ==============
  All sizes are in em - therefore
  changing the font-size here
  will change the size of the switch.
  See .rocker-small below as example.
  */
  font-size: 1em;
  font-weight: bold;
  text-align: center;
  text-transform: uppercase;
  color: #888;
  width: 7em;
  height: 4em;
  overflow: hidden;
  border-bottom: 0.5em solid #eee;
}

.rocker-small {
  font-size: 0.75em; /* Sizes the switch */
  margin: 1em;
}

.rocker::before {
  content: "";
  position: absolute;
  top: 0.5em;
  left: 0;
  right: 0;
  bottom: 0;
  background-color: #999;
  border: 0.5em solid #eee;
  border-bottom: 0;
}

.rocker input {
  opacity: 0;
  width: 0;
  height: 0;
}

.switch-left,
.switch-right {
  cursor: pointer;
  position: absolute;
  display: flex;
  align-items: center;
  justify-content: center;
  height: 2.5em;
  width: 3em;
  transition: 0.2s;
}

.switch-left {
  height: 2.4em;
  width: 2.75em;
  left: 0.85em;
  bottom: 0.4em;
  background-color: #ddd;
  transform: rotate(15deg) skewX(15deg);
}

.switch-right {
  right: 0.5em;
  bottom: 0;
  background-color: #bd5757;
  color: #fff;
}

.switch-left::before,
.switch-right::before {
  content: "";
  position: absolute;
  width: 0.4em;
  height: 2.45em;
  bottom: -0.45em;
  background-color: #ccc;
  transform: skewY(-65deg);
}

.switch-left::before {
  left: -0.4em;
}

.switch-right::before {
  right: -0.375em;
  background-color: transparent;
  transform: skewY(65deg);
}

input:checked + .switch-left {
  background-color: #0084d0;
  color: #fff;
  bottom: 0px;
  left: 0.5em;
  height: 2.5em;
  width: 3em;
  transform: rotate(0deg) skewX(0deg);
}

input:checked + .switch-left::before {
  background-color: transparent;
  width: 3.0833em;
}

input:checked + .switch-left + .switch-right {
  background-color: #ddd;
  color: #888;
  bottom: 0.4em;
  right: 0.8em;
  height: 2.4em;
  width: 2.75em;
  transform: rotate(-15deg) skewX(-15deg);
}

input:checked + .switch-left + .switch-right::before {
  background-color: #ccc;
}

/* Keyboard Users */
input:focus + .switch-left {
  color: #333;
}

input:checked:focus + .switch-left {
  color: #fff;
}

input:focus + .switch-left + .switch-right {
  color: #fff;
}

input:checked:focus + .switch-left + .switch-right {
  color: #333;
}

I then added a submit button so the item gets saved so the button will default to the selected value when the page gets refreshed.

Add the following code to execute when page loads to set the selected value when the page is refreshed.

if (apex.item( 'P8_NEW' ).getValue() == 'Yes')
{      
       document.getElementById("myCheck").checked = true;
}

APEX Office Print

2022-06-29T10:38:00.007+01:00

This post is how we used APEX Office Print (AOP) in the University of Glasgow in Scotland to print APEX forms and loved it 😃

The app

The application is a Performance Development Review (PDR) APEX application for staff at the University of Glasgow. There are 7,000 staff and several different forms depending on the staff role. A professor will get a different form to a Janitor for example.

CSS Print

Our first attempt at printing was using a print style sheet. This worked to a certain extent but we had major issues printing Interactive Grids and you dont have a great deal of control over what gets printed especially as we had several different APEX forms.

Apex Office Print to the rescue

We purchased AOP on-prem bronze and its paid for itself in the year we have been using it as we have went from dozens of print queries where we were constantly updating our print CSS to zero queries (yes zero!!!). The resulting word documents the users now download are also much more professional looking than a CSS print.

Installation

AOP runs on its own server in the University. You then have to install the plugins in your APEX environment. Both were easy to install and it comes with good documentation.

SQL

First thing we did was create the SQL that would include all the form data we want to print. Here is a snippet where we get individual form items and row data that appears in interactive grids on the form. In this example its research publications that staff have published;

to_char(start_date,'DD-MM-YYYY') "start_date",
(select forename||' '||surname from core_person where GUID=:P0_APPLICATION_USER) "pdr_person",
cursor(select title "title", authors "authors", year "year"
from EPRINT_PDR_PUBLICATIONS where GUID=:P0_APPLICATION_USER and SELECTED='Y'
) "publications",

A really neat feature of the AOP plugin is it allows you to send the results out as a template by choosing template type "AOP Template". This is a really good starting position as the resulting word template gives you all the tags produced by AOP from your SQL.

Forms

Using the generated word template which has all the tags, we created a word form for each PDR form and uploaded them to static applkication files.

We then created a page zero item called P0_AOP_FILENAME to hold the AOP template name depending on which from the staff member is assigned.

When the user opens the form page in APEX we know which type of form the user has been assigned.

We created a PL/SQL process in "pre rendering after header" in the form page to set P0_AOP_FILENAME. We know the type of form using another item P0_ACTIVE_PDR_FORM_ID and append this to the P0_AOP_FILENAME item;

:P0_AOP_FILENAME := 'aop/1921/AOP_TEMPLATE_PDR_APPLICATION_FORM_' || :P0_ACTIVE_PDR_FORM_ID || '.docx';

We then use P0_AOP_FILENAME in AOP dynamic action settings;

Take aways

Dont use CSS print. It eneded up causing us all sorts of unforseen issues.

AOP is a solid piece of software, the server has been running for over a year and has never had to be restarted. This is quite amazing in itself.

We handed over the word templates to the project sponsor which is the Universities Human Resources department who are not technical and now make changes to the templates (leaving the tags). They make any changes and we load them back into static application files.

AOP support is really good. Answered all our queries quickly.

7,000 staff using this application and at peak times we get around 400 print requests a day and not one issue reported.

Now getting used in several projects, all using the same templates so all project prints have the same look and feel.

Minor point's

Not that many articles on AOP on Google (thats why I posted this) so its a wee bit of a learning curve although not too bad, but its worth it as its a great piece of software and highly configurable.

Everybody loves it and now want excel prints. Excel does not come in the bronze license. You need to purchase the silver license for $8,000. Working on HR...lol

Add a chatbot to your APEX app using DialogFlow

2021-12-01T09:20:00.011+00:00

This post will show you how you can add a free chatbot to your APEX app using the always free Oracle Autonomous database and Google’s natural language processing (NLP) Dialogflow ES.

This Oracle APEX NSS web site allows users to ask the chatbot for the percentage of students who overall, were satisfied with the quality of the courses at the University of Glasgow. It allows users to query over 1,200 courses. Give it a try by clicking NSS→Russell Group NSS Course Search

Here is some info about how DialogFlow works on another project I built.

How I built it.

Loaded data into autonomous database

I loaded the National Student Survey CSV data into autonomous database using database actions data load;

Created a REST API for the data

Using the REST action above I created a REST API for the data, it takes a course and aim and retrieves it from the Database

Try it out;

https://kjwvivmv0n5reuj-apexkqor.adb.uk-london-1.oraclecloudapps.com/ords/admin/nss/q27/Mathematics/BSc

This returns the following JSON which has selected the question Q27 overall percentage satisfied with Mathematics BSC course. It also returns the course ID and overall rank for UK universities for that course

"items":[{"q27":77,"kiscourseid":"G100-2208","rank":11}]

Create the chatbot

I used DialogFlow ES to create the chatbot.

Call the REST API from DialogFlow

In webhook fulfilment, I added this function that takes a course and aim and calls the REST API. As it is calling a REST API, I used a promise to make the call and return the result to DialogFlow;

function aim(agent) {
    
    console.log('in AIM intent');    
    var course = agent.contexts[0].parameters.course;
    var aim = agent.contexts[1].parameters.aim;
        
    const promise1 =  new Promise(function (resolve, reject) {
        
    axios.get('https://kjwvivmv0n5reuj-apexkqor.adb.uk-london-1.oraclecloudapps.com/ords/admin/nss/q27/'+course+ '/'+ aim)
        .then(res => {
        resolve(res);
        })
        .catch(error => {
        console.log('Error in fetch ', error);
        });
   });
  
   promise1.then(function (result) {
      console.log("Result is " + JSON.stringify(result.data));
      if (result.data.items && result.data.items[0])
      {
       console.log('Percentage is ', result.data.items[0].q27 );
       console.log('Rank is ', result.data.items[0].rank );
        
        var rank = '';
        if (result.data.items[0].rank == 1)
        {
          rank = 'Good news, the University of Glasgow is the number 1 ranked University in the Russell Group :-).';
        }
        else if (result.data.items[0].rank)
        {
          rank = 'The University of Glasgow is ranked ' + result.data.items[0].rank + ' in the Russell Group.';
        }
       agent.add(course + ' ' + aim + ' overall satisfaction is ' + result.data.items[0].q27 + '%.' + rank + ' Say another course to start again');          
      }
      else
      {
       agent.add(course+ ' does not have an aim of ' + aim + '. Say another course to start again');
      }
    }, function (error) {
                console.log("Error in then ", error);
    });  
    return promise1;   
  }

The DialogFlow aim intent calls the webhook, which passes in the course and aim;

To get the chatbot HTML to add to your APEX app, select DialogFlow Messenger in Integrations;

this created the code to add to our APEX page;

<script src="https://www.gstatic.com/dialogflow-console/fast/messenger/bootstrap.js?v=1"></script>
<df-messenger
  intent="WELCOME"
  chat-title="UofG Overall satisfaction"
  agent-id="XXXXXX"
  language-code="en"
></df-messenger>

we now have the chatbot on our APEX app;

Add calculated tooltips to your Interactive Grid

2021-10-28T13:06:00.000+01:00

In this post, I will show you how to add a tooltip that calculates column values displayed as tooltips on your Interactive grid or report. It's a nice value add, giving your users some context to the values in the grid.

Demo

The tooltip displays the following calculated values from the selected value's column;

Create the Interactive Region with static ID IR_STATIC_ID using the following SQL source;

select 'Product 1' as PRODUCT, 100 as VALUE_1, 200 as VALUE_2, 
'121' as VALUE_3, 'text' as VALUE_4 from DUAL
UNION
select 'Product 2' as PRODUCT, 103 as VALUE_1, 140 as VALUE_2, 
'99' as VALUE_3, 'text' as VALUE_4 from DUAL
UNION
select 'Product 3' as PRODUCT, 1 as VALUE_1, 2 as VALUE_2, 
'1,200' as VALUE_3, 'text' as VALUE_4 from DUAL
UNION
select 'Product 4' as PRODUCT, 10 as VALUE_1, 7 as VALUE_2, 
'124' as VALUE_3, 'text' as VALUE_4 from DUAL
UNION
select 'Product 5' as PRODUCT, 432 as VALUE_1, 632 as VALUE_2, 
'134' as VALUE_3, 'text' as VALUE_4 from DUAL
UNION
select 'Product 6' as PRODUCT, 34 as VALUE_1, 333 as VALUE_2, 
'non number' as VALUE_3, 'text' as VALUE_4 from DUAL

Add the following code to execute when page loads;

// Interactive report static ID
const IG_NAME = '#IR_STATIC_ID';

// This function expects the first column to be a string and the rest of the columns to be numbers.
// It will ignore any columns that have text.
$(function(){
  // Get column values for all rows and place it in the cols array
  var cols = []
  var trs = $(IG_NAME + ' tr')
  var data =$.each(trs , function(index, tr){
    $.each($(tr).find("td").not(":first"), function(index, td){
      cols[index] = cols[index] || [];

      if(!isNaN($(td).text().replace(',',''))){
        cols[index].push(parseInt($(td).text().replace(',',''), 10)) 
      }
    })
  });

  cols.forEach(function(col, index)
  {
    let columnData = new ColumnData(col);

    $(IG_NAME + ' tr').find('td:eq('+(index+1)+')').each(function(i, td)
    {       
        if(!isNaN($(td).text().replace(',','')))
        {
           columnData.createTooltip($(td));
        }        
    })
  })
})

class ColumnData {
    constructor(col) {
        this.max = Math.max.apply(null, col);
        this.min = Math.min.apply(null, col);
        this.total = col.reduce((a, b) => a + b, 0);
        this.avg = (this.total/ col.length).toFixed(0);
        this.col = col;
        this.col.sort(function(a, b){return (b-a)});
    }

    valueAsNumber(cell) {
        return parseInt((cell.text()).replace(',',''), 10);
    }

    ranking(cell)
    {
        return this.col.findIndex(rank => rank === this.valueAsNumber(cell)) +1;
    }

    maxDifference(cell)
    {
        return this.max - this.valueAsNumber(cell);
    }

    minDifference(cell)
    {
        return this.valueAsNumber(cell) - this.min;
    }   

    percent(cell)
    {
        return ((this.valueAsNumber(cell)/this.total)*100).toFixed(0);
    }
    
    percentColour(cell)
    {
        if (this.valueAsNumber(cell) < this.avg)
        {
            return "red";
        }
        return  "green"; 
    }

    createTooltip(cell)
    {
        cell.attr('title', '');

        cell.tooltip({
        content: "<table border='1'><tr>"+
        //////////////////////////////////////////////
        // HEADER row
        //////////////////////////////////////////////
        "<th class='celldata'>Value</th>"+
        "<th class='celldata'>Rank</th>"+
        "<th class='celldata'>% of total</th>"+
        "<th class='celldata'>Avg</th>"+
        "<th>Overal total</th>"+
        "<th>Min value</th><th>> Min</th>"+
        "<th>Max value</th><th>< Max</th></tr>"+
        //////////////////////////////////////////////
        // DATA row
        //////////////////////////////////////////////
        "<tr><td style='font-size: 150%;'><b>"+this.valueAsNumber(cell)+"</b></td>"+
        "<td>"+this.ranking(cell)+"</td>"+
        "<td>"+this.percent(cell)+"%</td>"+
        "<td style='font-size: 100%;' class='"+this.percentColour(cell)+"'</td>"+this.avg+"</td>"+ 
        "<td>"+this.total+"</td>"+
        "<td>"+this.min+"</td><td>"+this.minDifference(cell)+"</td>"+
        "<td>"+this.max+"</td><td>"+this.maxDifference(cell)+"</<td>"+
        "</tr></table>"
    });  
}}

If you don't want values to appear in the tooltip, comment them out here. In this case, we don't want min and max value displayed in the tooltip;

 //////////////////////////////////////////////
        // HEADER row
        //////////////////////////////////////////////
        "<th class='celldata'>Rank</th>"+
        "<th class='celldata'>% of total</th>"+
        "<th class='celldata'>Avg</th>"+
        "<th>Overal total</th>"+
        //"<th>Min value</th><th>> Min</th>"+
        //"<th>Max value</th><th>< Max</th></tr>"+
        //////////////////////////////////////////////
        // DATA rows
        //////////////////////////////////////////////
        "<tr><td style='font-size: 150%;'><b>"+this.valueAsNumber(cell)+"</b></td>"+
        "<td>"+this.ranking(cell)+"</td>"+
        "<td>"+this.percent(cell)+"%</td>"+
        "<td style='font-size: 100%;' class='"+this.percentColour(cell)+"'</td>"+this.avg+"</td>"+ 
        "<td>"+this.total+"</td>"+
        //"<td>"+this.min+"</td><td>"+this.minDifference(cell)+"</td>"+
        //"<td>"+this.max+"</td><td>"+this.maxDifference(cell)+"</<td>"+
        "</tr></table>"

Conversely, you could add other values to the tooltip here like the column name. Style the tooltip by placing this code in inline CSS;

div.ui-tooltip {
    max-width: 600px !Important;
}

table {
  border-collapse: collapse;border: 2px solid rgba(0, 0, 0, 0.075);
}

.celldata {
  padding: 15px;
  color: white;
  background-color: rgb(198, 241, 40);
  text-align: center;
}

th {
  padding: 15px;
  color: white;
  background-color: dodgerblue;
  text-align: center;
}

td {
  padding: 15px; 
  background-color:white;
  text-align: center;
}

.red{
    color:red;
}

.green{
    color:green;
}

Any questions, post a comment and I will try and answer it.

Test & Monitor you APEX app. Part 3 : Monitor your DB with JMeter and Jenkins

2021-10-01T13:15:00.004+01:00

This is the third of a series of posts where I will show you how you can use free open source software Jenkins and JMeter to monitor your APEX database.

Part 1 - Monitor your APEX app using Jenkins

Part 2 - Monitor your APEX app using Google Analytics

In the next post, I will show you how you can run scheduled Selenium tests on your APEX app, the tests will log into your app and run Selenium tests which will be hosted on a free GitHub account using GitHub actions so no installation of any software necessary. You will have your APEX application scheduled selenium tests (getting email alerts if any issues with your APEX application) up in running in minutes by cloning a ready-made GitHub APEX selenium repo.

Prerequisite: Install JMeter on the same server as Jenkins. Like Jenkins, JMeter is simple to set up and there are lots of online guides, so I won't go into it here.

First we are going to set up the DB monitoring job on JMeter then schedule it from Jenkins.

Run JMeter in GUI mode

Add a thread group

Add a JDBC Connection configuration to connect to your APEX DB;

give it the name oracle_db, (we are going to use this in the JDBC request) and enter your database connection details;

now we add the JDBC request, in this case I want to ensure that a table has been updated in the last 24 hours and if it hasn't, I want alerted by email;

select
CASE
        WHEN max(w_update_dt) < sysdate-1
        THEN
          'WARNING Reload older then 1 day'
        ELSE
           'OK'
      END status
fromPDRL2.eprint_pdr_publications

now add the actual JDBC request;

this is where we are going to add the SQL, put in oracle_db as the connection config name;

Now we add the check to ensure the data is not stale by adding a BeanShell Assertion;

Note we are referencing dataFromDb which was set up in the JDBC Request. The code is;

log.info("Response is  " + vars.getObject("dataFromDB").get(0).get("STATUS"));

if (prev.getResponseMessage() != "OK" || 
     !vars.getObject("dataFromDB").get(0).get("STATUS").equalsIgnoreCase( "OK"))
{
      SampleResult.setSuccessful(false);
      IsSuccess=false;
      SampleResult.setStopTestNow(true);
}

The whole job looks like this

The view results tree, summary report etc are for viewing the results. Try it out by pressing the green button;

View the results in the View results tree. If everything is OK, we are ready to set up Jenkins to schedule it;

I am saving it as Oracle_ensure_publication_data_recent.jmx which we are going to call from Jenkins. I also created a directory Oracle_ensure_publication_data_recent which is where the results are stored.

In Jenkins, create a new freestyle item (same as setting up item in part 1) and configure how often it will run;

Build is where we call the JMeter job, as both Jenkins and JMeter are installed on the same server we reference the JMeter job like this;

C:\apps\apache-jmeter-5.2.1\bin\jmeter -n -t C:\apps\apache-jmeter-5.2.1\bin\oracle_db_ensure_publication_data_recent.jmx -f -l C:\apps\apache-jmeter-5.2.1\bin\oracle_db_ensure_publication_data_recent.jtl -e -o C:\apps\apache-jmeter-5.2.1\bin\oracle_db_ensure_publication_data_recent_output

Set the error threshold;

list the emails any alerts will be sent to;

Your APEX database is now monitored. Once you have set up one job its easy to set up subsequent jobs by following the same principles. I have several jobs monitoring our APEX DB, sending alerts if it identifies any problems.

Tip

When creating another JMeter schedule in Jenkins, type in your existing JMeter job and just change the JMeter script in the build.

Test & Monitor your APEX app - Part 2 Google Analytics

2021-09-26T10:54:00.004+01:00

Monitoring your APEX application with Google Analytics is a must. It takes 5 minutes to set up in your APEX application and gives you lots of benefits.

Advantages.

Before go live we load tested our APEX app using JMeter, on the go live date we used Google Analytics to give us a real time view of how many users were logging in to see if it was getting close to the number of users we load tested the application with. Luckily, it was only around 10% of the amount of users we load tested and everything went OK.

You get access to the free Google Data Studio to create embeddable reports. We embedded reports in our APEX app back office showing views per day, browser usage etc. It's a University staff APEX application, so interesting to see usage during the week compared to weekends.

View browser and operating system's usage statistics, which allows us to target testing on browsers for upcoming releases. Analytics showed us Edge was surprisingly the second most popular browser (after Chrome), beating Safari and Firefox, so we will give Edge more precedence in testing the next release.

Set-up Analytics in your APEX app

Create a Google Analytics account and place the JS code provided into your APEX app.

Open page 0 in your APEX app;

place the Google Analytics JS code in source;

<!-- Global site tag (gtag.js) - Google Analytics -->
<script async src="<YOUR URL>"></script>
<script>
  window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments);}
  gtag('js', new Date());

  gtag('config', '<YOUR TAG>');
</script>

When you log into your Google Analytics account, you will get a real time overview straight away;

For little effort you get so much........

In-depth platform to track a multitude of website analytics.
Easy to understand metrics to allow informed decisions.
Great for making reports to present to people.
Easy to collaborate with multiple users if needed.
Has a ton of integrations with other marketing products.

Part 1 - Monitor you APEX app using Jenkins

Test & Monitor you APEX app. Part 1 : Monitor your APEX app using Jenkins

2021-09-22T15:35:00.004+01:00

This is the first of a series of posts where I will show you how you can use Jenkins, Selenium, JMeter and Maven to test, monitor and create a continuous integration pipeline for your APEX application.

This is part 1, Set-up Jenkins to monitor your APEX app and get alerted when your app is unavailable.

Creating a simple Jenkins job that takes 5 minutes to set up, your APEX application will be monitored 24 hours a day, and you will get alerted as soon as it becomes unavailable. You can then build on this using free open source software to run web (Selenium) and database (JMeter) tests on your APEX app controlled by Jenkins, which will I describe in upcoming posts.

In upcoming posts, I will show you how to set up Jenkins to;

Run web (Selenium) tests on your APEX application. Schedule web tests logging into your APEX app running tests using Selenium and Maven and get notified of any failures.

Schedule deployment of your APEX dev env nightly to a test env. Deploy your APEX app from your development environment to a test environment nightly, then run a suite of Selenium web tests and get notified of any failures.

Monitor your APEX DB. Run scheduled database checks on your APEX database using JMeter and get notified of any failures.

Load test your APEX app. Stress test your APEX application, logging in with thousands of users using JMeter distributed testing.

In this post I will also create a job to monitor our Apex Office Print server and get alerted if it's unavailable. These are the 2 Jenkins monitoring jobs I am going to set-up;

Prerequisite: Install Jenkins and the site monitor plugin. Jenkins is simple to set up and there are lots of online guides, so I won't go into it here.

Let's set up the monitor APEX application job. Log into Jenkins and press New Item (top left);

name it "Monitor APEX application", select Freestyle project and press OK;

press configure;

set-up how often we want to monitor our APEX app, which will be every 5 minutes;

H/5 * * * *

(press the blue ? beside to the right of schedule to get help on setting up the schedule cron)

Now tell it the APEX URL you want to monitor by pressing Add post-build action;

and choose Monitor Site;

the URL is your APEX app URL;

You can leave Success Response Codes empty, where it will check for 2XX codes by default.

Lastly, set up the email recipients who will get emailed if the app is unavailable;

Exactly the same steps for the Monitor AOP job, replacing the APEX URL with our AOP Server address.

It's free and took about 5 minutes to set the jobs up, my team now get alerted as soon as our APEX app or AOP server is unavailable. I have found this invaluable, as we now get alerted if there is an issue with our application, so we can try and fix it before any users report it.

How to send an email from your ORACLE APEX app

2021-09-17T16:23:00.012+01:00

Sending emails from your APEX application using APEX Email Templates is super simple.

The project specification was to add buttons to a form to allow users to press a button to email students whether they have been successful or unsuccessful in their scholarship application.

This is how we did it. The application form is a standard APEX form where we will add a button to the form that when pressed will send an email;

In this example I will walk you through the creation of the rejection email. First, we created a new email template for rejection emails with the name REJECTION;

with the following identification;

To send images in the email ensure they are hosted on a publicly available site. In this case, I hosted the University of Glasgow logo on my Google Drive and put it in the header of the email using the HTML IMG tag;

The body is standard HTML using substitution strings (Special substitution strings available within a template are denoted by the number symbol (#). For example: #ABC#)

Student Name: #FIRST_NAME# #LAST_NAME#<br>
    <br>
    16 September 2021
    <br><br>
    Dear #FIRST

In the Advanced section press 'Load Default HTML' which loads the default HTML that styles the email, you can change this;

Now we create the button on the page with an 'Execute Server-side Code' Dynamic Action;

This is where the code reads the items on the page to send to the template which uses them as special substitution strings. We are sending 5 items to the template where they get referenced in the HTML body, for example to reference FIRST_NAME in the body HTML we use #FIRST_NAME#.

p_template_static_id is the name of the email template, REJECTION.

begin
    apex_mail.send (
        p_to                 => apex_json.stringify( :P901_EMAIL_ADDRESS) ,
        p_template_static_id => 'REJECTION',
        p_placeholders       => '{' ||
        '    "FIRST_NAME":' || apex_json.stringify( :P901_FIRST_NAME ) ||
        ',   "LAST_NAME":' || apex_json.stringify( :P901_LAST_NAME ) ||
        ',   "STUDENT_ID":' || apex_json.stringify( :P901_STUDENT_ID ) ||
        ',   "APPLICATION_NUMBER":' || apex_json.stringify( :P901_APPLICATION_NUMBER ) ||
         ',  "TEXT_SCHOLARSHIP_NAME":' || apex_json.stringify( :P901_SCHOLARSHIP_NAME) ||
        '}' );
        apex_mail.push_queue;
end;

Note. You must set Maintain Session State to 'Per Session (Disk)' for any form values you are sending in the PLSQL;

To finish it off, add another Dynamic Action to the button to alert the user an email has been sent.

That's it, super easy and because the HTML is in the email template it's a nice separation of model/view so easy to maintain going forward.

Conditional HTML

If you have conditional requirements, for example you do not want an ordered list element to appear if there is no value, you can do this in your HTML body;

#RESIDENCY_COMMENT_BEGIN!RAW#
<li>#RESIDENCY#</li>
#RESIDENCY_COMMENT_END!RAW#

In the PLSQL, declare 2 variables;

  l_residency_comment_begin VARCHAR2(100);
  l_residency_comment_end   VARCHAR2(100);

If we do not have a value for the agent's email address, give them the following values;

IF :P901_AGENT_EMAIL_ADDRESS is null THEN
      l_residency_comment_begin := '<!--';
      l_residency_comment_end := '-->';
END IF;

send them to the email template;

',"RESIDENCY_COMMENT_BEGIN":' || apex_json.stringify(l_residency_comment_begin) ||
',"RESIDENCY_COMMENT_END":'   || apex_json.stringify(l_residency_comment_end) ||    

The full PLSQL is;

DECLARE
    l_residency_comment_begin VARCHAR2(100);
    l_residency_comment_end   VARCHAR2(100);
begin

IF :P901_AGENT_EMAIL_ADDRESS is null THEN
      l_residency_comment_begin := '<!--';
      l_residency_comment_end := '-->';
END IF;

apex_debug.enable( apex_debug.c_log_level_engine_trace);
    apex_mail.send (
        p_to                 => 'david.lang@glasgow.ac.uk',
        p_template_static_id => 'GENERICACCEPTANCE',
        p_placeholders       => '{' ||
        ' "FIRST_NAME":' || apex_json.stringify( :P901_FIRST_NAME ) ||
        ',"LAST_NAME":' || apex_json.stringify( :P901_LAST_NAME ) ||
        ',"STUDENT_ID":' || apex_json.stringify( :P901_STUDENT_ID ) ||
        ',"APPLICATION_NUMBER":' || apex_json.stringify( :P901_APPLICATION_NUMBER ) ||
        ',"TEXT_SCHOLARSHIP_NAME":' || apex_json.stringify( :P901_SCHOLARSHIP_NAME) ||
        ',"RESIDENCY":' || apex_json.stringify( :P901_AGENT_EMAIL_ADDRESS) ||  
        ',"RESIDENCY_COMMENT_BEGIN":' || apex_json.stringify(l_residency_comment_begin) ||
        ',"RESIDENCY_COMMENT_END":'   || apex_json.stringify(l_residency_comment_end) ||     
        '}' );
        apex_mail.push_queue;
end;

I did think the easy way of doing this would be to add this CSS, but this does not work. If anybody does know how to do it with CSS only, a comment would be great.

li:empty {
  display: none;
}  

Add a time-series chart to your APEX app - Part 3 of the HighChart Series

2021-09-16T13:26:00.004+01:00

Add a time-series chart to your APEX app using HighCharts and JQuery. This is the third in the series of blog posts showing you how to add HighCharts to your APEX application.

This is the most useful of the HighCharts in the series so far, easily turn your date data into an animated time series chart with a couple of lines of JavaScript

Demo

The chart reads the data points from an Interactive Report on the page. Create the Interactive Region with static ID IR_STATIC_ID using the following SQL source;

select TO_DATE('2003/07/09', 'yyyy/mm/dd') as VALUE_1, 10 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/07/11', 'yyyy/mm/dd') as VALUE_1, 20 as VALUE_2, 40 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/07/13', 'yyyy/mm/dd') as VALUE_1, 30 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/07/10', 'yyyy/mm/dd') as VALUE_1, 99 as VALUE_2, 80 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/01', 'yyyy/mm/dd') as VALUE_1, 5 as VALUE_2, 80 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/02', 'yyyy/mm/dd') as VALUE_1, 10 as VALUE_2, 30 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/03', 'yyyy/mm/dd') as VALUE_1, 15 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/04', 'yyyy/mm/dd') as VALUE_1, 4 as VALUE_2, 10 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/05', 'yyyy/mm/dd') as VALUE_1, 17 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/06', 'yyyy/mm/dd') as VALUE_1, 20 as VALUE_2, 40 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/07', 'yyyy/mm/dd') as VALUE_1, 3 as VALUE_2, 2 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/08', 'yyyy/mm/dd') as VALUE_1, 20 as VALUE_2, 30 as VALUE_3 from DUAL
UNION
select TO_DATE('2003/08/10', 'yyyy/mm/dd') as VALUE_1, 18 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/07/09', 'yyyy/mm/dd') as VALUE_1, 10 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/07/11', 'yyyy/mm/dd') as VALUE_1, 20 as VALUE_2, 40 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/07/13', 'yyyy/mm/dd') as VALUE_1, 30 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/07/10', 'yyyy/mm/dd') as VALUE_1, 99 as VALUE_2, 70 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/01', 'yyyy/mm/dd') as VALUE_1, 5 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/02', 'yyyy/mm/dd') as VALUE_1, 10 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/03', 'yyyy/mm/dd') as VALUE_1, 15 as VALUE_2, 40 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/04', 'yyyy/mm/dd') as VALUE_1, 4 as VALUE_2, 2 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/05', 'yyyy/mm/dd') as VALUE_1, 17 as VALUE_2, 20 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/06', 'yyyy/mm/dd') as VALUE_1, 20 as VALUE_2, 30 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/07', 'yyyy/mm/dd') as VALUE_1, 3 as VALUE_2, 2 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/08', 'yyyy/mm/dd') as VALUE_1, 20 as VALUE_2, 10 as VALUE_3 from DUAL
UNION
select TO_DATE('2004/08/10', 'yyyy/mm/dd') as VALUE_1, 18 as VALUE_2, 2 as VALUE_3 from DUAL
order by VALUE_1 asc

Create a static region which will display the timeline diagram chart in the Header and Footer section header text

<div id="container" style="height: 400px; min-width: 600px"></div>

<script src="https://code.highcharts.com/stock/highstock.js"></script>
<script src="https://code.highcharts.com/stock/modules/exporting.js"></script>

(reading directly from HighCharts, if it works for you download them to static application files)

Add the following code to execute when page loads. This is the code that contains the configuration of the chart and loads the data from the Interactive Report;

let period = 1000; //1sec

Highcharts.stockChart('container', {

    legend: {
            enabled: true,
            labelFormatter: function() {
        return this.name +' (click to hide)';
      }
        },

    chart: {
        borderColor: 'black',
        borderWidth: 0.2,
        
        borderRadius: 10
    },
       tooltip: {
        xDateFormat: '%d-%m-%Y',
        shared: true
    },


    series: [{
        animation: {
        defer: period*0,
        duration : 4000
      },
        name: 'Product sales 2017',
        data: getDataFromInteractiveReport('IR_STATIC_ID',1)
    },{
        animation: {
        defer: period*1,
        duration : 4000
      },
        name: 'Product sales 2018',
        data: getDataFromInteractiveReport('IR_STATIC_ID',2)
    }]
});

(You can have a play with configuration options in HighCharts Fiddle and then change the options in the code)

the chart reads the data from the Interactive Report to display on the chart;

function getDataFromInteractiveReport(staticID, column){
    var result = []; // array of [date, value] to pass to HighCharts
    
    // For each row in the IR
    var data =$.each($('#'+staticID+' tr'), function(index, tr){  
        var data = []; // holds the date-value
        var dateColumnValue =  Date.parse($(this).find("td:eq(0)").text());
        var value =  Number($(this).find("td:eq("+column+")").text());
  
        if (value){
            data.push(dateColumnValue, value);
            result.push(data);
        }
  });
  return result;
}

Highcharts needs the Interactive Report to be present on the page to get the values to plot on the graph. In this case I don't need the Interactive report displayed on the page, so I will hide the interactive report using the following CSS;

#IR_STATIC_ID {
    display: none;
}

If you want to add another series for 2019 add a new column to the SQL then add the series to the JavaScript code. This time column 3;

series: [{
    animation: {
    defer: period*0,
    duration : 4000
  },
    name: 'Product sales 2017',
    data: getDataFromInteractiveReport('IR_STATIC_ID',1) // column number 1
},{
    animation: {
    defer: period*1,
    duration : 4000
  },
    name: 'Product sales 2018',
    data: getDataFromInteractiveReport('IR_STATIC_ID',2)// column number 2
},{
    animation: {
    defer: period*2,
    duration : 4000
  },
    name: 'Product sales 2019',
    data: getDataFromInteractiveReport('IR_STATIC_ID',3)// column number 3
}]

The example shown is using dummy SQL. To create an Interactive Region from your table that contains dates you can use something like this, where I am getting completion date counts from my table which will work with the JS code shown above when we have 1 series.

select TRUNC(UPDATED_DATE), 
           COUNT(*)  from pdr WHERE  
                status_comm='COMPLETE' GROUP BY TRUNC(UPDATED_DATE) 
                     ORDER BY TRUNC(UPDATED_DATE)

To work with the source above, for one series you would have this;

series: [{
    animation: {
    defer: period*0,
    duration : 4000
  },
    name: 'PDR Completion Count',
    data: getDataFromInteractiveReport('IR_STATIC_ID',1) // column number 1
}]

Highcharts is free to use for personal projects, school websites, and non-profit organizations. For commercial applications you must purchase a license.

Part 1 - Dumbell Chart

Part 2 - Timeline Diagram Chart

Add a timeline diagram chart to your APEX app - Part 2 of the HighChart Series

2021-09-14T08:58:00.018+01:00

How to add a timeline diagram chart to your APEX app using HighCharts and JQuery. This is the second in the series of blog posts showing you how to add HighCharts to your APEX application.

Demo

I have been using HighCharts in my web apps for a number of years and in my opinion its the best charting JavaScript library out there, its free to use for non-commercial use, robust, well documented, mobile friendly and a great addition to your APEX app.

I work for the University of Glasgow and the next project I am starting is an APEX project where staff members apply for academic promotion. There are many stages to the application process, manager approval, board of review, college review, etc. A timeline diagram displaying the process to the users will be ideal. This is how I will do it.

As an aside, for American readers this is a fascinating story I have recently learned about the University. James McCune Smith born a slave and the first African American to receive a medical degree, when he applied for entry to several American universities he was refused admission on account of his race. Founded in 1451, the University of Glasgow the fourth oldest university in the English-speaking world stepped in and offered him a place, which he took up in 1832. The University has recently opened a building in his name which I am immensely proud of.

Anyway back to the blog.........

The chart reads the data points from an Interactive Report on the page. Create the Interactive Region with static ID IR_STATIC_ID using the following SQL source;

select 'First dogs' as VALUE_1,
 '1951: First dogs in space' as VALUE_2, 
'22 July 1951 First dogs in space (Dezik and Tsygan) ' as VALUE_3 from DUAL
Union
select 'Apollo–Soyuz Test Project' as VALUE_1, 
'1975: First multinational manned mission' as VALUE_2, 
'The mission included both joint and separate scientific experiments, and provided 
useful engineering experience for future joint US–Russian space flights, 
such as the Shuttle–Mir Program and the International Space Station. ' as VALUE_3 
from DUAL
Union
select 'First space station' as VALUE_1, 
'1971: First space station' as VALUE_2, 
'Salyut 1 was the first space station of any kind, launched into low Earth 
orbit by the Soviet Union on April 19, 1971.' as VALUE_3 from DUAL
Union
select 'First human on the Moons' as VALUE_1, 
'1969: First human on the Moon' as VALUE_2, 
'First human on the Moon, and first space launch from a celestial body
 other than the Earth. First sample return from the Moon' as VALUE_3 from DUAL
Union
select 'First human spaceflight' as VALUE_1,
 '1961: First human spaceflight (Yuri Gagarin)' as VALUE_2, 
'First human spaceflight (Yuri Gagarin), and the first human-crewed orbital 
flight ' as VALUE_3 from DUAL
Union
select 'Sputnik 1' as VALUE_1, '1957: First artificial satellite' as VALUE_2, 
'4 October 1957 First artificial satellite. First signals from space. ' as VALUE_3 
from DUAL

Create a static region which will display the timeline diagram chart in the Header and Footer section header text;

<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/timeline.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>
<script src="https://code.highcharts.com/modules/accessibility.js"></script>

<figure class="highcharts-figure">
    <div id="container"></div>
</figure>

(reading directly from HighCharts, if it works for you download them to static application files)

Add the following code to execute when page loads. This is the code that contains the configuration of the chart and loads the data from the Interactive Report;

Highcharts.chart('container', {
    chart: {
        type: 'timeline'
    },
    accessibility: {
        screenReaderSection: {
            beforeChartFormat: '<h5>{chartTitle}</h5>' +
                '<div>{typeDescription}</div>' +
                '<div>{chartSubtitle}</div>' +
                '<div>{chartLongdesc}</div>' +
                '<div>{viewTableButton}</div>'
        },
        point: {
            valueDescriptionFormat: '{index}. {point.label}. {point.description}.'
        }
    },
    xAxis: {
        visible: false
    },
    yAxis: {
        visible: false
    },
    title: {
        text: 'Timeline of Space Exploration'
    },
    subtitle: {
        text: 'Info source: <a href="https://en.wikipedia.org/wiki/Timeline_of_space_exploration">www.wikipedia.org</a>'
    },
    colors: [
        '#4185F3',
        '#427CDD',
        '#406AB2',
        '#3E5A8E',
        '#3B4A68',
        '#363C46'
    ],
 series: [{
        data: getDataFromInteractiveReport('IR_STATIC_ID')
    }]
});

(You can have a play with configuration options in HighCharts Fiddle and then change the options in the code)

the chart reads the data from the Interactive Report to display on the chart;

function getDataFromInteractiveReport(staticID){
    var result = []; // array of [name, label, description] to pass to HighCharts
    
    // For each row in the IR
    var data =$.each($('#'+staticID+' tr'), function(index, tr){  
        if ($(this).find("td:eq(0)").text())
        {
            result.push({
                name: $(this).find("td:eq(0)").text(),
                  label: $(this).find("td:eq(1)").text(),
                    description: $(this).find("td:eq(2)").text()});
        }
    });
    var result = result.reverse();
    return result;
}

#IR_STATIC_ID {
    display: none;
}

Highcharts is free to use for personal projects, school websites, and non-profit organizations. For commercial applications you must purchase a license.

Part 1 - Dumbell Chart

Add a DumbBell chart to your APEX app - Part 1 of the HighChart Series

2021-09-13T09:33:00.013+01:00

How to add a DumbBell chart to your APEX app using HighCharts and JQuery. This is the first in the series of blog posts showing you how to add HighCharts to your APEX application.

Demo

I have been using HighCharts in my web apps for a number of years and in my opinion its the best charting JavaScript library out there, its free to use for non-commercial use, robust, well documented, mobile friendly and a great addition to your APEX app.

The chart reads the data points from an Interactive Report.

Create an Interactive Region with static ID IR_STATIC_ID using the following SQL source;

select 'Australie' as VALUE_1, 60 as VALUE_2, 70 as VALUE_3 from DUAL
UNION
select 'New Zealand' as VALUE_1, 70 as VALUE_2, 80 as VALUE_3 from DUAL
UNION
select 'Scotland' as VALUE_1, 80 as VALUE_2, 90 as VALUE_3 from DUAL
UNION
select 'England' as VALUE_1, 50 as VALUE_2, 70 as VALUE_3 from DUAL
UNION
select 'France' as VALUE_1, 55 as VALUE_2, 90 as VALUE_3 from DUAL
UNION
select 'Spain' as VALUE_1, 67 as VALUE_2,98 as VALUE_3 from DUAL
UNION
select 'United States' as VALUE_1, 82 as VALUE_2, 89 as VALUE_3 from DUAL
UNION
select 'Peru' as VALUE_1, 70 as VALUE_2, 79 as VALUE_3 from DUAL
UNION
select 'Portugal' as VALUE_1, 55 as VALUE_2, 95 as VALUE_3 from DUAL
UNION
select 'India' as VALUE_1, 65 as VALUE_2, 85 as VALUE_3 from DUAL
UNION
select 'Senegal' as VALUE_1, 45 as VALUE_2, 65 as VALUE_3 from DUAL

Create a static region which will display the BarBell chart in the Header and Footer section header text;

<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/highcharts-more.js"></script>
<script src="https://code.highcharts.com/modules/dumbbell.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>
<script src="https://code.highcharts.com/modules/export-data.js"></script>
<script src="https://code.highcharts.com/modules/accessibility.js"></script>

<figure class="highcharts-figure">
    <div id="container"></div>
    <p class="highcharts-description">

    </p>
</figure>

(reading directly from HighCharts, if it works for you download them to static application files)

Add the following code to execute when page loads. This is the code that contains the configuration of the chart and loads the data from the Interactive Report;

Highcharts.chart('container', {

    chart: {
        type: 'dumbbell',
        inverted: true
    },

    legend: {
        enabled: false
    },

    subtitle: {
        text: '1960 vs 2018'
    },

    title: {
        text: 'Change in Life Expectancy'
    },

    tooltip: {
        shared: true
    },

    xAxis: {
        type: 'category'
    },

    yAxis: {
        title: {
            text: 'Life Expectancy (years)'
        }
    },

    series: [{
        name: 'Life expectancy change',
        data: getDataFromInteractiveReport('IR_STATIC_ID')
    }]

});

(You can have a play with configuration options in HighCharts Fiddle and then change the options in the code)

this code reads the data from the Interactive Report to display on the chart;

function getDataFromInteractiveReport(staticID){
    var result = []; // array of [name, low, high] to pass to HighCharts
    
    // For each row in the IR
    var data =$.each($('#'+staticID+' tr'), function(index, tr){  
        if ($(this).find("td:eq(0)").text())
      result.push( {name: $(this).find("td:eq(0)").text(), 
                        low:Number($(this).find("td:eq(1)").text()), 
                            high:Number($(this).find("td:eq(2)").text())});
    });
    return result;
}

Highcharts needs the Interactive Grid to be present on the page to get the values to plot on the graph. If you dont need the Interactive report displayed on the page you can hide the interactive report using the following CSS;

#IR_STATIC_ID {
    display: none;
}

Highcharts is free to use for personal projects, school websites, and non-profit organizations. For commercial applications you must purchase a license.

Part 2 - Add a timeline diagram chart to your APEX app

Part 3 - Add a timeline series chart to your APEX app

Highlight min and max column values in an Interactive Report

2021-09-09T09:33:00.002+01:00

Highlight maximum and minimum column values in an Interactive Report (or grid) with a couple of lines of JavaScript and CSS.

Demo

Create an Interactive Report region (static ID IR_STATIC_ID) with the following source SQL;

select 'Product 1' as PRODUCT, 100 as VALUE_1, 200 as VALUE_2, 121 as VALUE_3 from DUAL
UNION
select 'Product 2' as PRODUCT, 103 as VALUE_1, 140 as VALUE_2, 99 as VALUE_3 from DUAL
UNION
select 'Product 3' as PRODUCT, 1 as VALUE_1, 2 as VALUE_2, 120 as VALUE_3 from DUAL
UNION
select 'Product 4' as PRODUCT, 10 as VALUE_1, 7 as VALUE_2, 124 as VALUE_3 from DUAL
UNION
select 'Product 5' as PRODUCT, 432 as VALUE_1, 632 as VALUE_2, 134 as VALUE_3 from DUAL
UNION
select 'Product 6' as PRODUCT, 34 as VALUE_1, 333 as VALUE_2, 166 as VALUE_3 from DUAL

Add this to execute when page loads;

$(function(){
    var cols = []
    var trs = $('#IR_STATIC_ID tr')
    var data =$.each(trs , function(index, tr){
      $.each($(tr).find("td").not(":first"), function(index, td){
        cols[index] = cols[index] || [];
        cols[index].push($(td).text())
      })
    });
    cols.forEach(function(col, index){
      var max = Math.max.apply(null, col);
      var min = Math.min.apply(null, col)
      $('#IR_STATIC_ID tr').find('td:eq('+(index+1)+')').each(function(i, td){
        $(td).toggleClass('u-color-1-text', +$(td).text() === max)
        $(td).toggleClass('u-color-2-text', +$(td).text() === min)  
      })
    })
  })

Inline CSS, change this to suit.

.u-color-1-text{
    font-weight: 700;
    font-size: larger;
}
    
.u-color-2-text{
    color:red !Important;
    font-weight: 500;
    font-size: larger;
}

Your grid min and max values will now be highlighted.

Add a Sparkline to an Interactive Report

2021-09-06T09:54:00.040+01:00

This post will show you how to add a sparkline with interactive mouse-over to an Interactive Report with a couple of lines of JavaScript.

Demo

It's highly customisable where you can change the type of sparkline in the sparkline config. This is a box plot;

We are using JQuery sparklines, You can change the sparkline to any of the ones listed below. Use the 'try it out' section on JQuery sparklines to get the sparkline type and custom values you require;

First upload jquery.sparkline.js to static application files and include it on your page;

Create an Interactive Report region giving it a static id of IR_STATIC_ID, use the following SQL source;

select 'Product 1' as PRODUCT, '90' as VALUE_0,100.3 as VALUE_1, 
200 as VALUE_2, '121' as VALUE_3, '' as Sparkline from DUAL
UNION
select 'Product 2' as PRODUCT, '80' as VALUE_0, 103 as VALUE_1, 
140 as VALUE_2, '99' as VALUE_3, '' as Sparkline from DUAL
UNION
select 'Product 3' as PRODUCT, '1,345' as VALUE_0, 1000 as VALUE_1, 
900 as VALUE_2, '1,200' as VALUE_3, '' as Sparkline from DUAL
UNION
select 'Product 4' as PRODUCT, '7' as VALUE_0, 10 as VALUE_1, 
7 as VALUE_2, '124' as VALUE_3, '' as Sparkline from DUAL
UNION
select 'Product 5' as PRODUCT, '756' as VALUE_0, 432.99 as VALUE_1, 
632 as VALUE_2, '134' as VALUE_3, '' as Sparkline from DUAL
UNION
select 'Product 6' as PRODUCT, '66' as VALUE_0, 34 as VALUE_1, 
333 as VALUE_2, 'non number' as VALUE_3, '' as Sparkline from DUAL

add this code into execute when page loads. The code should work on your existing IR as long as you add a new column at the end to display the sparkline. In my dummy SQL above the column is called Sparkline.

// Uses JQuery sparkline to add sparklines to an APEX Interactive Report.
// https://omnipotent.net/jquery.sparkline/#s-about
//
// Comments welcome, how about building this into an Apex Plugin?
// davielang@gmail.com
$(function(){  
// Get column values for each row and place them in the cols array 
var data =$.each($('#IR_STATIC_ID tr'), function(index, tr){
    var cols = []; // place the row column data in here
    // for each column in the row
    $.each($(tr).find("td").not(":first"), function(columnIndex, td){                       
        // add column value to cols array only if its a number and not empty
        if(!isNaN($(td).text().replace(',','')) && $(td).text().length > 0)
            cols.push($(td).text().replace(',',''));
      
        // if we are at the last column then add the sparkline.
        if ((columnIndex+2)==$('#IR_STATIC_ID').columnCount()){
            var dataSpan = $("<span>").appendTo($(td));       
            dataSpan.sparkline(cols, {
                // For more options, see https://omnipotent.net/jquery.sparkline/#common
                type: 'box',
                barWidth : '10px',
                width : '100px'
            });
        }    
    })
  });
})

$.fn.columnCount = function() {
    return $('th', $(this).find('tbody')).length;
};

(Note the code expects the first column to be text, if you do not have this change the code accordingly.)

I was having difficulty showing the tooltip as I assume the APEX CSS was causing the sparkline tooltip some issues. Add this to page inline CSS;

.jqstooltip { box-sizing: content-box;}

The above example is a box plot. To get a bar sparkline change type: 'box' to type: 'bar' and refresh the page;

Type can be one of 'line', 'bar', 'tristate', 'discrete', 'bullet', 'pie' or 'box'. Try them all out!

Sentiment Analysis for Oracle APEX using client side JavaScript

2021-08-23T09:07:00.007+01:00

If you have an interactive grid with any sort of review text a nice value add for the reviews is the addition of sentiment analysis. This post will show you how to add a sentiment column of positive, negative or neutral and a score to your interactive grid using a couple of lines of code.

Demo app

This post is a quick front end value add of sentiment analysis to your app using client side JavaScript. If this works for you I suggest using Oracle Text or some other sentiment analysis tool to do the sentiment analysis on the database rather than the front end as shown here.

We are using a client side JavaScript library compendium to do the sentiment analysis. First download compendium.js from here and upload it to static application files in your APEX app. Get the reference to the JS file, in my case #APP_IMAGES#compendium.js

Create a new page and include the JavaScript library.

Create a new IG region with the static ID ANSWERS_IG. Sample SQL for your interactive grid with 2 placeholder columns for the sentiment analysis, SENTIMENT to hold the text, positive, negative or neutral and SENTIMENT_VALUE to hold the score.

select 'Product 1' as product,
'Good, great, brilliant, superb' as product_review,
       'N/A' as SENTIMENT,
       'N/A' as SENTIMENT_VALUE from dual

union

select 'Product 2' as product,
'Bad, terrible, worst, hated it' as product_review,
       'N/A' as SENTIMENT,
       'N/A' as SENTIMENT_VALUE from dual

union

select 'Product 3' as product,
'OK, standard, not too bad' as product_review,
       'N/A' as SENTIMENT,
       'N/A' as SENTIMENT_VALUE from dual

union

select 'Product 4' as product,
'Worst product ever' as product_review,
       'N/A' as SENTIMENT,
       'N/A' as SENTIMENT_VALUE from dual

We will use JQuery to populate the sentiment columns, so give the columns PRODUCT_REVIEW, SENTIMENT and SENTIMENT_VALUE the same class names so JQuery can access the column text. An example is the PRODUCT_REVIEW column.

Add the following JS to execute when page loads.

$.each($('#ANSWERS_IG tbody tr'), function(i, item) {
     var responseCell = $(item).find('.PRODUCT_REVIEW');

     if (responseCell.text())
     {
        var sentiment = compendium.analyse(responseCell.text())[0];
        if (sentiment.profile.label == 'negative')
        {
           $(item).find('.SENTIMENT').html('<p style="color:red;font-size: 125%">'+sentiment.profile.label+'</p>');
        }
        else if (sentiment.profile.label == 'positive'){
           $(item).find('.SENTIMENT').html('<p style="color:green;font-size: 125%"">'+sentiment.profile.label+'</p>');
        }
        else
        {
           $(item).find('.SENTIMENT').html('<span>'+sentiment.profile.label+'</span>'); 
        } 
        $(item).find('.SENTIMENT_VALUE').text(sentiment.profile.sentiment.toFixed(3));
    }
});

The JQuery code loops round all the rows in your IG. The analysis happens on this line where the text is passed to the JS code for analysis

var sentiment = compendium.analyse(responseCell.text())[0];

We are interested in the returned sentiment profile which you can view here,

Lastly I have added some styling to the sentiment which you can change to suit.

For example I want positive sentiment to show green with a larger font size.

$(item).find('.SENTIMENT').html('<p style="color:green;font-size: 125%"">'+sentiment.profile.label+'</p>');

Oracle APEX style an Interactive Grid cell conditionally based on the value of another cell using JavaScript and CSS

2021-03-05T14:44:00.005+00:00

This blog post describes how you can style an Interactive Grid cell conditionally based on the value of another cell using JQuery and CSS. Using APEX Version 19.2.

The requirement is to highlight the cell that has the highest value in the row;

This can be achieved by setting the IG column CSS class and using JQuery to loop round the table rows to conditionally set CSS style based on cell values

First give all your columns a CSS Class (note you can give columns multiple classes separated with space) in this case all 4 columns are set with CSS class SCORE_C_1, SCORE_C_2, SCORE_C3 and SCORE_C_4 in the column Appearance;

We now add the JavaScript in Execute when page loads (preferably put this is a JS file).

We are using the JQuery $.each() method to fetch every table row;

$.each($('tbody tr'), function(i, item) {}

and the JQuery text() method to get the column value from the row using the column CSS class names;

var scoreC1 = $(item).find('.SCORE_C_1').text();
var scoreC2 = $(item).find('.SCORE_C_2').text();
var scoreC3 = $(item).find('.SCORE_C_3').text();
var scoreC4 = $(item).find('.SCORE_C_4').text();

We then use the values to conditionally set the cell CSS using the addClass method;

if (scoreC1 > scoreC2 && scoreC1 > scoreC3 && scoreC1 > scoreC4)
{
$(item).find('.SCORE_C_1').addClass("highlightTopScore");
}

Where the CSS is;

   .highlightTopScore{
        font-weight: 800;
        background-color : #f4faeb;   
   }

The full code is;

$.each($('tbody tr'), function(i, item) {
var scoreC1 = $(item).find('.SCORE_C_1').text();
var scoreC2 = $(item).find('.SCORE_C_2').text();
var scoreC3 = $(item).find('.SCORE_C_3').text();
var scoreC4 = $(item).find('.SCORE_C_4').text();
if (scoreC1 > scoreC2 && scoreC1 > scoreC3 && scoreC1 > scoreC4)
{
$(item).find('.SCORE_C_1').addClass("highlightTopScore");
}
if (scoreC2 > scoreC1 && scoreC2 > scoreC3 && scoreC2 > scoreC4)
{
$(item).find('.SCORE_C_2').addClass("highlightTopScore");
}
if (scoreC3 > scoreC1 && scoreC3 > scoreC2 && scoreC3 > scoreC4)
{
$(item).find('.SCORE_C_3').addClass("highlightTopScore");
}
if (scoreC4 > scoreC1 && scoreC4 > scoreC2 && scoreC4 > scoreC3)
{
$(item).find('.SCORE_C_4').addClass("highlightTopScore");
}
});

My page has several IGs that I want to style so the above code will style all IGs that have the CSS classes set. If you only want to style a specific IG then use this;

$.each($('#<STATIC_ID_OF_IG> tbody tr'), function(i, item) {

instead of this which will style all IGs

$.each($('tbody tr'), function(i, item) {

Oracle APEX Interactive Grid colour cells based on a condition using JavaScript and CSS

2021-02-25T10:20:00.002+00:00

In this post I will describe how to colour an Interactive Grid based on a column condition using CSS and JavaScript. Using APEX Version 19.2.

I want to colour every column red in my IG when the value is 'No'.

I have several IGs on my page I want to colour, so I set all my IGs that I want to colour with a CSS class of ATTENDEE_SCORE_IG in the Appearance section of the IG;

Every column I want to colour in the IG I set the CSS Class to COLOUR_YES_NO (in this example I set this in 5 columns);

I then created a Dynamic Action (DA) that contains the JavaScript that has the condition check that will colour the cells. Because I have several IGs that are details of the master/detail relationship and these are refreshed by the user clicking on the master detail IG I have made my DA a Page Change [Interactive Grid]. If you don't need this you can set the DA on page load.

The JS which will colour every column is this;

////////////////////////////////////////////////////////////////////////////////////////////////
// This function colours IG columns that are Yes/No to red if its no and green if its yes.
//
// Currently using green (u-success) and red (u-danger) but these can be changed to
// any colour defined here;
//
// https://apex.oracle.com/pls/apex/apex_pm/r/ut/color-and-status-modifiers
//
// Example.
//
// The param igClassName is the IG class name with Yes\No columns you want to colour.
//
// Set the IG class name. Appearance->CSS classes, in this example ATTENDEE_SCORES_IG.
// For all Yes/No columns you want to colour set the CSS Class of the column to COLOUR_YES_NO.
//
// colourYesNoScores('ATTENDEE_SCORES_IG');
//
////////////////////////////////////////////////////////////////////////////////////////////////
function colourYesNoScores(igClassName)
{
$('.' + igClassName + ' ' + 'td.COLOUR_YES_NO').each(function(){
cellData = $(this).text();
if (cellData == 'No') {
$(this).addClass('u-danger');
}
else {
$(this).addClass('u-success');
}
});
};
// colour every IG on the page that has a class of ATTENDEE_SCORES_IG
colourYesNoScores("ATTENDEE_SCORES_IG");

This will colour every IG cell with a CSS class ATTENDEE_SCORES_IG and columns CSS class of COLOUR_YES_NO if the cell value meets the condition.

Currently, using green (u-success) and red (u-danger), these can be changed to any colour defined here;

https://apex.oracle.com/pls/apex/apex_pm/r/ut/color-and-status-modifiers

The advantage of using CSS is that any further IGs I add to the page all I need to do is ensure that the CSS classes are set, and it will automatically colour the cells.

The JS code will be placed in my utility JS file (utlility.js) which I import into all my projects which will automatically colour all YES/NO IGs that have the CSS set.

This gives me a standard way of colouring Yes/No columns in every project I use without having to write any code.

Oracle APEX Focus on detail interactive grid from master interactive grid on click

2021-02-22T09:16:00.060+00:00

When you have two interactive grids using master-detail you can focus the page on the detail interactive grid when the user clicks on a cell in the master interactive grid.

This is useful when your master interactive grid has lots of rows that takes up most of the screen, when the user clicks on a row on the master IG the page will focus the page on the detail interactive grid.

I am using APEX Version 19.2.

My master IG has a details column which when the user clicks on it scrolls the page to the details IG.

I give this column a CSS class name of linkToApplicantDetails.

I added an Execute JavaScript code Dynamic Action which uses the class name linkToApplicantDetails as the JQuery Selector for a click;

(Ensure your IQuery Selector has a dot (.) in front of it, i.e. .linkToApplicationDetails)

Because my detail IG is the last table on the page my JavaScript scrolls to the bottom of the page when the user clicks on the column;

    $("html, body").animate({ scrollTop: $(document).height()-$(window).height()});

You could use apex.region namespace focus function to focus the page on the detail IG;

apex.region( "<< IG STATIC ID>>" ).focus();

The page will focus on the top of the detail IG so I added another line of JS to scroll the page an extra number of pixels. Play around with this size (130) for your page;

window.scrollBy(0, 130);

See. https://developer.mozilla.org/en-US/docs/Web/API/Window/scrollBy.

Now when the user clicks on a row on the master detail IG the page will scroll to the detail IG.

Oracle APEX Remove the aggregate totals column in an Interactive Grid

2021-02-12T12:36:00.005+00:00

This post explains how to remove the Overall Average column on an IG when aggregates are added to an IG. I am using APEX version 19.2 but this should work on all versions. I added aggregates to my IG which gives me a row of averages at the bottom of the grid;

It also gives me a total averages column at the start of the grid;

There are 2 reasons I don't want this column. Firstly, in my experience with this column its very buggy, as you can see in the picture below it causes the rows of the grid to get out of sync when sorting.

Secondly I have a lot of columns and I want the grid to appear at 100% zoom on the browser with no scrolling and this column is taking up valuable retail space!

So.....how do we remove it?

In execute when page loads, add this JavaScript where rgBenchmarkingIG is the static ID of your Interactive Grid.

apex.region("rgBenchmarkingIG").widget().interactiveGrid("getCurrentView").view$.grid("option", "rowHeader", "none");

OK, now gone. But how does the user know that the totals at the bottom are averages?

We can use CSS to add the text Avg to the first column. Usually first columns are names, links (like mine) etc, so we can put the text 'Avg' here ({content: 'Avg';}). In Inline CSS add this;

#rgBenchmarkingIG_ig_grid_vc > div.a-GV-bdy > div.a-GV-w-scroll > table > tbody > tr.a-GV-row.is-aggregate.a-GV-aggregate--AVG.is-grandTotal > td:nth-child(1):after {content: 'Avg';}

Note this is for averages (aggregate--AVG), you will need to change the CSS for sum etc.

This now tells the user the totals row is averages and gives us more space for the grid to fit on the page without scrolling;

Oracle APEX fit an interactive grid onto the page when printing

2021-02-11T17:43:00.003+00:00

If your Interactive grid does not fit all the columns onto the page when printing this blog post will show you how you can add a small bit of code to your print CSS to fit all the columns onto the page when printing.

There are lots of blogs that will show you how to set up printing in APEX, so I won't go into much detail on print set up here. This blog post will show you how to fit an interactive grid with lots of columns into a page when you print it.

I have a standard print CSS set-up in Inline CSS that tries to maximize the space available for printing by setting padding and margins;

@media print{

/*----------------------------------------------------------

-- Print the page as a portrait A4 page with no margins

---------------------------------------------------------- */

@page

{

size: A4 portrait;

margin-left: 1px !Important;

margin-right: 1px !Important;

margin-top: 0px !Important;

margin-bottom: 0px !Important;

padding: 0px 0px !Important;

}

and a print button which prints the page using JavaScript;

window.print();  

The issue I have is when printing the page the IG does not fit onto the page cutting off several columns;

It's a simple hack to get the IG to fit on the page. Just add this code to your print CSS;

#ig_outputs{zoom:60%;}

(play around with the zoom level to fit your IG into the page.)

Where ig_outputs is the static ID of the IG

Now when I print the page it fits nicely onto the page.

One last thing to add is browser background graphics, by default (on Chrome) this is not checked, this caused me issues when printing the page when it was not checked.

Add this to your print CSS to ensure that the page prints OK.

force browsers to print background images.

body {-webkit-print-color-adjust: exact;}

.t-Body-nav {

display:none

}

Oracle APEX Change Textarea maximum length (and placeholder) dynamically

2021-02-11T09:01:00.001+00:00

The requirement is to change the maximum size of a text area dynamically depending on the type of user that has logged in.

Its a standard Textarea;

With a placeholder stating the input should be no more than 1750 characters;

Which looks like this

If the user is of type 'A' which is stored in a P0 item P0_TY_TYPE (calculated when the user logs in) we need to change the maximum characters for this Textarea to 1500. This can be accomplished with some JavaScript in Execute when page loads;

if ($v( "P0_TY_TYPE" ) == 'A')
{
$('#P8_ESTEEM').attr('placeholder','This statement should be no more than 200 words (approx 1500 characters)');
$("#P8_ESTEEM").attr('maxlength','1500');
$("#P8_ESTEEM_CHAR_COUNT > span.apex-item-textarea-counter--size").text("1500");
}

Oracle APEX rename Region Display Selector tabs at runtime

2021-02-10T16:51:00.000+00:00

This post describes how you can change an RDS tab name at runtime using a P0 value. I am using APEX 19.2.

I have a form with a standard RDS with the following tabs. The requirement is the Scholarship, Knowledge, Exchange & Impact tab name should change depending on the value of a P0 value.

This Scholarship, Knowledge, Exchange & Impact region has this static ID set;

and is named Scholarship, Knowledge, Exchange & Impact which is what displays on the tab;

Using JavaScript in Execute when page loads we check the PO value, if it is the target form we change the tab name to Scholarship when the page loads.

if ($v( "P0_FORM_ID" ) == '6001')
{
$('#SKEI_REGION_tab > a > span').text('Scholarship');
$('#SKEI_REGION > div.t-Region-header > div.t-Region-headerItems.t-Region-headerItems--title').text('Scholarship');
}

Oracle APEX Interactive Grid rename aggregate row label

2021-02-10T16:31:00.000+00:00

This post describes how to rename an Interactive Grid aggregate totals row name. I am using APEX 19.2.

The default name is "Overall Sum" for the totals row, this was knocking out the other rows slightly so I needed to make it "Total" instead.

The solution was to add the following JavaScript into the function and Global Variable Declaration.

The JS is as follows;

/******************************************************************************************

* The spreadsheet (IG) totals default name is "Overall Sum". Rename it "Total".

* Param spreadsheetID : The Interactive grid ID

*******************************************************************************************/

function renameOverallSumToTotal(spreadsheetID)

{

var g = apex.region(spreadsheetID).widget().interactiveGrid('getViews', 'grid');

var labels = g.view$.grid('option', 'aggregateLabels');

labels.SUM.label = 'Subtotal';

labels.SUM.overallLabel = 'Total';

g.view$.grid('option', 'aggregateLabels', labels);

g.view$.grid('refresh');

}

// Rename the totals rows on both spreadsheets

$('#pgrCalculation').on('interactivegridviewchange', function(event , ui)

{

renameOverallSumToTotal('pgrCalculation');

});

where pgrCalculation is the static ID of the Interactive Grid

Oracle APEX Color Interactive Grid (IG) group header using CSS

2021-02-10T14:36:00.000+00:00

This post describes how to colour a group header on an Interactive Grid. Using Version 19.2 but this should work on all versions.

Groups are when you create a group and place columns into the group like this;

I want to colour the Research & Teaching Group header to be this;

Open the page containing the IG and open Developer tools in Chrome then click on the selector tool (arrow top left).

Click the region you header you want to style, in my case Research & Teaching UoFGS. In developer tools right click on the selected element and copy the selector.

This gets copied;

#rgBenchmarkingIG_ig_grid_vc > div.a-GV-hdr.js-stickyWidget-toggle > div.a-GV-w-hdr > table > thead > tr:nth-child(1) > th:nth-child(2)

I dont only want to colour the selected column I want to colour the whole row, so I remove;

> th:nth-child(2)

and place the following CSS into inline CSS or preferably a CSS file;

#rgBenchmarkingIG_ig_grid_vc > div.a-GV-hdr.js-stickyWidget-toggle > div.a-GV-w-hdr > table > thead > tr:nth-child(1){

background: #87CEEB!important;

}

The IG;

Now becomes this;