Build beautiful and interactive API documentation for ORDS

Image
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 generat...
Post a Comment

Add calculated tooltips to your Interactive Grid

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.


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.


Comments

Post a Comment

Popular posts from this blog

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

Image
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; ////////////////////////////////////////////////////...
Read more

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

Image
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('.SCOR...
Read more

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

Image
  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...
Read more