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 generate the documentation; As mentioned b
Post a Comment

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

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



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 pretty checkbox item plugin

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

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 generate the documentation; As mentioned b
Read more