Free script to generate a Line Chart on HTML
Performance Metrics are easier to digest if visualized trough some Line Charts. OEM, eDB360, eAdam and other tools use them. If you already have a SQL Statement that provides the Performance Metrics you care about, and just need to generate a Line Chart for them, you can easily create a CSV file and open it with MS-Excel. But if you want to build an HTML Report out of your SQL, that is a bit harder, unless you use existing technologies. Tools like eDB360 and eAdam use Google Charts as a mechanism to easily generate such Charts. A peer asked me if we could have such functionality stand-alone, and that challenged me to create and share it.
This HTML Line Chart Report above was created with script line_chart.sql shown below. The actual chart, which includes Zoom functionality on HTML can be downloaded from this Dropbox location. Feel free to use this line_chart.sql script as a template to display your Performance Metrics. It can display several series into one Chart (example above shows only one), and by reviewing code below you will find out how easy it is to adjust to your own needs. Chart above was created using a simple query against the Oracle Sample Schema SH, but the actual use could be Performance Metrics or any other Application time series.
Script
SET TERM OFF HEA OFF LIN 32767 NEWP NONE PAGES 0 FEED OFF ECHO OFF VER OFF LONG 32000 LONGC 2000 WRA ON TRIMS ON TRIM ON TI OFF TIMI OFF ARRAY 100 NUM 20 SQLBL ON BLO . RECSEP OFF;
PRO
DEF report_title = "Line Chart Report";
DEF report_abstract_1 = "<br>This line chart is an aggregate per month.";
DEF report_abstract_2 = "<br>It can be by day or any other slice size.";
DEF report_abstract_3 = "";
DEF report_abstract_4 = "";
DEF chart_title = "Amount Sold over 4 years";
DEF xaxis_title = "Sales between 1998-2001";
--DEF vaxis_title = "Amount Sold per Hour";
--DEF vaxis_title = "Amount Sold per Day";
DEF vaxis_title = "Amount Sold per Month";
DEF vaxis_baseline = ", baseline:2200000";
DEF chart_foot_note_1 = "<br>1) Drag to Zoom, and right click to reset Chart.";
DEF chart_foot_note_2 = "<br>2) Some other note.";
DEF chart_foot_note_3 = "";
DEF chart_foot_note_4 = "";
DEF report_foot_note = "This is a sample line chart report.";
PRO
SPO line_chart.html;
PRO <html>
PRO <!-- $Header: line_chart.sql 2014-07-27 carlos.sierra $ -->
PRO <head>
PRO <title>line_chart.html</title>
PRO
PRO <style type="text/css">
PRO body {font:10pt Arial,Helvetica,Geneva,sans-serif; color:black; background:white;}
PRO h1 {font-size:16pt; font-weight:bold; color:#336699; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
PRO h2 {font-size:14pt; font-weight:bold; color:#336699; margin-top:4pt; margin-bottom:0pt;}
PRO h3 {font-size:12pt; font-weight:bold; color:#336699; margin-top:4pt; margin-bottom:0pt;}
PRO pre {font:8pt monospace;Monaco,"Courier New",Courier;}
PRO a {color:#663300;}
PRO table {font-size:8pt; border_collapse:collapse; empty-cells:show; white-space:nowrap; border:1px solid #cccc99;}
PRO li {font-size:8pt; color:black; padding-left:4px; padding-right:4px; padding-bottom:2px;}
PRO th {font-weight:bold; color:white; background:#0066CC; padding-left:4px; padding-right:4px; padding-bottom:2px;}
PRO td {color:black; background:#fcfcf0; vertical-align:top; border:1px solid #cccc99;}
PRO td.c {text-align:center;}
PRO font.n {font-size:8pt; font-style:italic; color:#336699;}
PRO font.f {font-size:8pt; color:#999999; border-top:1px solid #cccc99; margin-top:30pt;}
PRO </style>
PRO
PRO <script type="text/javascript" src="https://www.google.com/jsapi"></script>
PRO <script type="text/javascript">
PRO google.load("visualization", "1", {packages:["corechart"]})
PRO google.setOnLoadCallback(drawChart)
PRO
PRO function drawChart() {
PRO var data = google.visualization.arrayToDataTable([
/* add below more columns if needed (modify 3 places) */
PRO ['Date Column', 'Number Column 1']
/****************************************************************************************/
WITH
my_query AS (
/* query below selects one date_column and a small set of number_columns */
SELECT --TRUNC(time_id, 'HH24') date_column /* preserve the column name */
--TRUNC(time_id, 'DD') date_column /* preserve the column name */
TRUNC(time_id, 'MM') date_column /* preserve the column name */
, SUM(amount_sold) number_column_1 /* add below more columns if needed (modify 3 places) */
FROM sh.sales
GROUP BY
--TRUNC(time_id, 'HH24') /* aggregate per hour, but it could be any other */
--TRUNC(time_id, 'DD') /* aggregate per day, but it could be any other */
TRUNC(time_id, 'MM') /* aggregate per month, but it could be any other */
/* end of query */
)
/****************************************************************************************/
/* no need to modify the date column below, but you may need to add some number columns */
SELECT ', [new Date('||
TO_CHAR(q.date_column, 'YYYY')|| /* year */
','||(TO_NUMBER(TO_CHAR(q.date_column, 'MM')) - 1)|| /* month - 1 */
--','||TO_CHAR(q.date_column, 'DD')|| /* day */
--','||TO_CHAR(q.date_column, 'HH24')|| /* hour */
--','||TO_CHAR(q.date_column, 'MI')|| /* minute */
--','||TO_CHAR(q.date_column, 'SS')|| /* second */
')'||
','||q.number_column_1|| /* add below more columns if needed (modify 3 places) */
']'
FROM my_query q
ORDER BY
date_column
/
/****************************************************************************************/
PRO ]);
PRO
PRO var options = {
PRO backgroundColor: {fill: '#fcfcf0', stroke: '#336699', strokeWidth: 1},
PRO explorer: {actions: ['dragToZoom', 'rightClickToReset'], maxZoomIn: 0.1},
PRO title: '&&chart_title.',
PRO titleTextStyle: {fontSize: 16, bold: false},
PRO focusTarget: 'category',
PRO legend: {position: 'right', textStyle: {fontSize: 12}},
PRO tooltip: {textStyle: {fontSize: 10}},
PRO hAxis: {title: '&&xaxis_title.', gridlines: {count: -1}},
PRO vAxis: {title: '&&vaxis_title.' &&vaxis_baseline., gridlines: {count: -1}}
PRO }
PRO
PRO var chart = new google.visualization.LineChart(document.getElementById('chart_div'))
PRO chart.draw(data, options)
PRO }
PRO </script>
PRO </head>
PRO <body>
PRO <h1>&&report_title.</h1>
PRO &&report_abstract_1.
PRO &&report_abstract_2.
PRO &&report_abstract_3.
PRO &&report_abstract_4.
PRO <div id="chart_div" style="width: 900px; height: 500px;"></div>
PRO <font class="n">Notes:</font>
PRO <font class="n">&&chart_foot_note_1.</font>
PRO <font class="n">&&chart_foot_note_2.</font>
PRO <font class="n">&&chart_foot_note_3.</font>
PRO <font class="n">&&chart_foot_note_4.</font>
PRO <pre>
L
PRO </pre>
PRO <br>
PRO <font class="f">&&report_foot_note.</font>
PRO </body>
PRO </html>
SPO OFF;
SET HEA ON LIN 80 NEWP 1 PAGES 14 FEED ON ECHO OFF VER ON LONG 80 LONGC 80 WRA ON TRIMS OFF TRIM OFF TI OFF TIMI OFF ARRAY 15 NUM 10 NUMF "" SQLBL OFF BLO ON RECSEP WR;
Carlos Sierra's Tools and Tips 
[…] https://carlos-sierra.net/2014/07/28/free-script-to-generate-a-line-chart-on-html/ […]
unCharted Territory | The Pointy End
July 29, 2014 at 1:51 am
A clever way to marry Charts with SQL.
Christoph Ruepprich
July 29, 2014 at 11:54 am
[…] Free script to generate a Line Chart on HTML. […]
Free script to generate a Line Chart on HTML | Christoph's 2 Oracle Cents
July 29, 2014 at 11:54 am
[…] It is very neat though and when combined with the SQL graph tool Carlos Sierra posted yesterday (https://carlos-sierra.net/2014/07/28/free-script-to-generate-a-line-chart-on-html/), you can visualise a lot of good data very […]
Everyone likes pie (apparently) | The Pointy End
July 29, 2014 at 12:12 pm
[…] with 4 comments […]
script to generate a Line Chart on HTML « Oracle-dba
August 5, 2014 at 12:43 pm
[…] visualization is done by using Google charts. I was inspired by Carlos Sierra’s blog post on creating line charts using google charts […]
Tablespace Utilization Trend and Forecasting | Anjul Sahu's Oracle Blog
March 14, 2015 at 9:56 pm
Hi Carlos,
I have a newbie question.
I wonder what is the meaning of the DEF and PRO markups.
I would like to generate HTML with SQL when producing an explain plan ( through dbms_xplan.display_cursor ).
If I presently succeed, I only translate into HTML the
Id / Operation / Name part while allows me to display the different steps of the plan.
But the way I do it is too complicated.
That’s why I would like to know the meaning and possibilities of the DEF and PRO markups.
Thank you in advance
Jean-michel A., Nemours , France
jean-michel
November 10, 2015 at 5:05 am
Jean-michel, those two DEF and PRO are SQL*Plus commands. DEF is to define variable and PRO is to place comments. DBMS_PLAN.DISPLAY_CURSOR seems to give you the info that you want (on text format).
Carlos Sierra
November 10, 2015 at 2:16 pm
Thank you Carlos.
So I just have to call your script at the sqlplus prompt then ?
SQL> @line_chart
does not send me anything ( or does not open any browser )
I do not do the thing the right way certainly.
Btw, the link to the dropbox file above does not work on my laptop.
Thank you very much
Jean-michel
jean-michel
November 11, 2015 at 9:33 am
no, you edit the script so it works for you. i provided the script as a sample
Carlos Sierra
November 11, 2015 at 2:51 pm
Hi Carlos,
I have a newbie question.
I wonder what is the meaning of the DEF and PRO markups.
I would like to generate HTML with SQL when producing an explain plan ( through dbms_xplan.display_cursor ).
If I presently succeed, I only translate into HTML the
Id / Operation / Name part while allows me to display the different steps of the plan.
But the way I do it is too complicated.
That’s why I would like to know the meaning and possibilities of the DEF and PRO markups.
PS : I did not see my comment, so I leave it again.
jean-michel
November 10, 2015 at 5:55 am
Thank you Carlos.
I did not notice the sh.sales was just a sample …
It works.
Jean-michel.
jean-michel
November 12, 2015 at 12:39 pm
Hi Carlos,
Thank you for this very useful script. However, i’m trying to access to the version stored in the dropbox but it seems it’s no active anymore. Could you correct the link ?
Thanks a lot
Christophe
Ortiz christophe
August 19, 2016 at 12:04 pm
Done! Thx
Carlos Sierra
August 19, 2016 at 2:16 pm
Hi Carlos, Thanks for the nice sql.
i have one question.
Lets say i have below kind of data.
TIME – containing the sample time from ash truncated to MM/DD/YYYY-HH24:MI
W – containing the wait event, if none, then CPU.
SMPL – count of samples for that event in that “TIME” minute
TIME W SMPL
4/13/2017-19:44 CPU 1
4/13/2017-19:46 log file sync 1
4/13/2017-19:46 CPU 1
4/13/2017-19:46 CPU 1
4/13/2017-19:47 db file sequential read 1
4/13/2017-19:47 db file sequential read 1
4/13/2017-19:47 log file sync 1
4/13/2017-19:47 CPU 1
4/13/2017-19:47 CPU 1
4/13/2017-19:48 CPU 1
4/13/2017-19:49 CPU 1
4/13/2017-19:50 db file sequential read 1
4/13/2017-19:50 flashback log file sync 1
4/13/2017-19:50 log file sync 4
4/13/2017-19:50 control file sequential read 1
4/13/2017-19:50 log file parallel write 1
4/13/2017-19:50 flashback log file write 1
4/13/2017-19:51 db file sequential read 1
4/13/2017-19:52 CPU 1
4/13/2017-19:52 null event 1
i want to make a stacked area chart using google charts only.
My requirement is :
w should be a variable where the value of the event will result in a different color.
I dont want to make a different column for each wait separately.
How can i achieve that?
I am looking for a kind of graph ( unfortunately there is not image copy paste option to show exactly) which i can get if i copy the above data in an excel and make a pivot chart:
AXIS(categories)-TIME
LEGEND(series)-W
VALUES-Sum of SMPL
Regds
sachinperfdba
April 25, 2017 at 3:12 pm
Not sure how to pivot the data using Google Charts. I do what you want on 1st report under column 5 within eDB360, but I do the pivot on Oracle so when I pass the time series to Google Charts I present as many columns as time series (in addition to the date column).
Carlos Sierra
April 26, 2017 at 6:40 am
i need to create a simple chart for tablespace growth in the database.How can i edit the query please suggest me.
Thanks,
DBA
velmurugan
August 3, 2017 at 4:31 am
Hi,
I run the same script which you are giving it’s working fine. I got the good results but I remove the select query which is you are giving,Instead of your query i put my tablespace query but output showing blank page.
I need your help,
Thanks,
DBA
velmurugan
August 3, 2017 at 4:50 am
Maybe you want to send me what you have done so I can check it for you. My email is carlos.sierra.usa@gmail.com.
Carlos Sierra
August 3, 2017 at 12:46 pm
Hi,
Thank you for your reply I send what i did
please suggest me.
Thanks,
DBA
velmurugan
August 3, 2017 at 9:40 pm