Skip to main content

Google Pie Charts in Oracle APEX


Here, I am giving you an elegant way to build google pie chart in Oracle APEX. This has been inspired by Google ChartsGoogle chart tools are powerful, simple to use, and free. We can try out Google's rich gallery of interactive charts and data tools.

Step 1: Create required objects (table, sequence, trigger) and populate sample data into a table. (As per your requirements)

Step 2: Create a new blank page.

Step 3: Create a region to the page. Change region type to PLSQL Dynamic Content.

Making a 2D Pie Chart:

Copy and paste below PLSQL code into the region's PLSQL code source.

DECLARE
l_task VARCHAR2(240) := 'List out Top 10 Infected Countries';
l_title VARCHAR2(240) := 'Breakdown of Top 10 Infected Countries - 2D';
l_chart_values VARCHAR2(4000);
-- Getting Top 10 infected countries details
CURSOR cur_top10_countries IS
SELECT country, total_cases
  FROM (SELECT COUNTRY, TOTAL_CASES,
        RANK() OVER (ORDER BY total_cases DESC) AS rank 
      FROM fxgn_global_data)
 WHERE rank <= 10;
BEGIN
htp.print('<div id="piechart" style="width: 700px; height: 500px;"></div>');
htp.print('<script type="text/javascript" 
src="https://www.gstatic.com/charts/loader.js"></script>');
htp.print('<script type="text/javascript">');

-- Load google charts
htp.print('google.charts.load(''current'', {''packages'':[''corechart'']})');
htp.print('google.charts.setOnLoadCallback(drawChart)');

-- Construct chart values
FOR i IN cur_top10_countries
LOOP
l_chart_values := l_chart_values || '['''||i.country||''','||i.total_cases||'],';
END LOOP;
l_chart_values := substr(l_chart_values,1, length(l_chart_values)-1);
--dbms_output.put_line (l_chart_values);

-- Draw the chart and set the chart values
htp.print('function drawChart() {
  var data = google.visualization.arrayToDataTable([
  [''Task'', '''||l_title||'''],
  '||l_chart_values||'
])');

-- Optional; add a title and set the width and height of the chart
htp.print('var options = {''title'':'''||l_title||'''}');

-- Display the chart inside the <div> element with id="piechart"
htp.print('var chart = new google.visualization.PieChart
(document.getElementById(''piechart''))');
htp.print('chart.draw(data, options)');
htp.print('}');
htp.print('</script>');
END;



Output: Then your output would then display like this,



Making a 3D Pie Chart:

If you set the is3D option to true, your pie chart will be drawn as though it has three dimensions.

Copy and paste below PLSQL code into the region's PLSQL code source,

DECLARE
l_task VARCHAR2(240) := 'Show of Top 10 Infected Countries';
l_title VARCHAR2(240) := 'Breakdown of Top 10 Infected Countries - 3D';
l_chart_values VARCHAR2(4000);

CURSOR cur_top10_countries IS
SELECT country, total_cases
  FROM (SELECT COUNTRY, TOTAL_CASES,
        RANK() OVER (ORDER BY total_cases DESC) AS rank 
      FROM fxgn_global_data)
 WHERE rank <= 10;
 
BEGIN
htp.print('<div id="piechart_3d" style="width: 700px; height: 500px;"></div>');
htp.print('<script type="text/javascript"
src="https://www.gstatic.com/charts/loader.js"></script>');
htp.print('<script type="text/javascript">');

-- Load google charts
htp.print('google.charts.load(''current'', {''packages'':[''corechart'']})');
htp.print('google.charts.setOnLoadCallback(drawChart)');

-- Construct chart values
FOR i IN cur_top10_countries
LOOP
l_chart_values := l_chart_values || '['''||i.country||''','||i.total_cases||'],';
END LOOP;
l_chart_values := substr(l_chart_values,1, length(l_chart_values)-1);
--dbms_output.put_line (l_chart_values);

-- Draw the chart and set the chart values
htp.print('function drawChart() {
  var data = google.visualization.arrayToDataTable([
  [''Task'', '''||l_title||'''],
  '||l_chart_values||'
])');

 -- add a title and set 3D
htp.print('var options = {''title'':'''||l_title||''', is3D: true}');

-- Display the chart inside the <div> element with id="piechart"
htp.print('var chart = new google.visualization.PieChart
(document.getElementById(''piechart_3d''))');
htp.print('chart.draw(data, options)');
htp.print('}');
htp.print('</script>');
END;

Output: Then your output would then display like this,


Making a Donut Chart:

A donut chart is a pie chart with a hole in the center. You can create donut charts with the pieHole option.

Copy and paste below PLSQL code into the region's PLSQL code source,

DECLARE
l_task VARCHAR2(240) := 'List out Top 10 Infected Countries';
l_title VARCHAR2(240) := 'Breakdown of Top 10 Infected Countries - Donut';
l_chart_values VARCHAR2(4000);

CURSOR cur_top10_countries IS
SELECT country, total_cases
  FROM (SELECT COUNTRY, TOTAL_CASES,
        RANK() OVER (ORDER BY total_cases DESC) AS rank 
      FROM fxgn_global_data)
 WHERE rank <= 10;
 
BEGIN
htp.print('<div id="donutchart" style="width: 700px; height: 500px;"></div>');
htp.print('<script type="text/javascript" 
src="https://www.gstatic.com/charts/loader.js"></script>');
htp.print('<script type="text/javascript">');

-- Load google charts
htp.print('google.charts.load(''current'', {''packages'':[''corechart'']})');
htp.print('google.charts.setOnLoadCallback(drawChart)');

-- Construct chart values
FOR i IN cur_top10_countries
LOOP
l_chart_values := l_chart_values || '['''||i.country||''','||i.total_cases||'],';
END LOOP;
l_chart_values := substr(l_chart_values,1, length(l_chart_values)-1);
--dbms_output.put_line (l_chart_values);

-- Draw the chart and set the chart values
htp.print('function drawChart() {
  var data = google.visualization.arrayToDataTable([
  [''Task'', '''||l_title||'''],
  '||l_chart_values||'
])');

 -- add a title and set pieHole value
htp.print('var options = {''title'':'''||l_title||''', pieHole: 0.4}');

-- Display the chart inside the <div> element with id="piechart"
htp.print('var chart = new google.visualization.PieChart
(document.getElementById(''donutchart''))');
htp.print('chart.draw(data, options)');
htp.print('}');
htp.print('</script>');
END;

Note: The pieHole option should be set to a number between 0 and 1, corresponding to the ratio of radii between the hole and the chart. Numbers between 0.4 and 0.6 will look best on most charts. Values equal to or greater than 1 will be ignored, and a value of 0 will completely shut your piehole.

You can't combine the pieHole and is3D options; if you do, pieHole will be ignored.

Output: Then your output would then display like this,


The demo is here.

That's it. Happy APExing!!!...

Comments

  1. Try using JSON_OBJECT and JSON_ARRAY

    ReplyDelete
    Replies
    1. Sure, let me try, but somewhat i feel this approach is little better than the approach which you proposed.

      Delete
    2. Thanks for the feedback Chuck. I have created a Plugin APEX Google Pie Chart (Using JSON_OBJECT and JSON_ARRAY). Please do try out (https://apex.world/ords/f?p=100:710:7633322251075::::P710_PLG_ID:ORCLKING.GOOGLE.PIECHART)

      Delete
  2. This would be the most straightforward way to get this done. Thanks a lot for sharing brother.

    ReplyDelete
    Replies
    1. Thanks Deepak. Please try out APEX Google Pie Chart Plugin (https://apex.world/ords/f?p=100:710:7633322251075::::P710_PLG_ID:ORCLKING.GOOGLE.PIECHART)

      Delete

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template

Generating the report with APEX_DATA_EXPORT

With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON. Step 1: Create a table and populate it with some sample records. CREATE TABLE emp   (     empno        NUMBER,     first_name   VARCHAR2(240),     last_name    VARCHAR2(240),     mgr          NUMBER,     deptno       NUMBER,     sal          NUMBER,     created_date TIMESTAMP (6),     comm         NUMBER,     hiredate     DATE,     JOB          VARCHAR2(240),     ename        VARCHAR2(240),     PRIMARY KEY (empno) USING INDEX ENABLE   ); /    INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (1, 'Larry', 'Ellison', ,                  10, 5000, LOCALTIMESTAMP);   INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (2, 'Juan', 'Juan', 1,  

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename