Task:
Add updated Chart.JS 2.x line and stacked bar charts to an app.
The data will by loaded dynamically from the view directly rather than via @DbLookup or having an agent generate the JavaScript data arrays.
In this example, we have an app that has both estimations and actual data. The chart report will have three charts. The first contains actual revenue per job/project. The second contains estimated vs actual job costs. The third contains labor estimated vs actual costs.
Steps:
1. Create Look-up View
Create the look-up view for the charting. The view was named:
(lup Rpt Jobs Qtr Comp. R CE CA LE LA) | lupRptJComp
The view should have each data point as a column. In our app, we had 4 sections to the view columns, column 1 was the Job Number/ID. The next set of columns were the income/revenue for each job phase, the next two sets of columns were for the estimated and actual costs, the last two sections were for the estimated and actual labor hours for the staff. Our view had the following columns:
ID -|- R1 -|- R2 -|- R3 -|- R4 -|- R5 -||- CE1 -|- CE2 -|- CE3 -|- CE4 -|- CE5 -||- CA1 -|- CA2 -|- CA3 -|- CA4 -|- CA5 -||- LE1 -|- LE2 -|- LE3 -|- LE4 -|- LE5 -||- LA1 -|- LA2 -|- LA3 -|- LA4 -|- LA5
The JSON will reference each data value by the column name/code. In our case, we did all our filtering in the fields, so we had no formulas in the view. ( In other words, no columns with names like $10.) Therfore, our column names were the same as our field names:
J_ID, J_R1 - J_R5, J_CE1 - J_CE2, J_CA1 - J_CA5, J_LE1 - J_LE5, J_LA1 - J_LA5
2. Import JS Libraries
Into the app's Resources --> Files, import the current version of JQuery, and ChartJS files:
- Chart2-8JS.bundle.min.js | Chart.bundle.min.js
- JQuery-3.3.1.min.js | JQuery.min.js
- JQuery-3.3.1.min.map | JQuery.min.map
We give the libraries an alias so that when new versions are replaced the files are the same name to the code. Since the Chart2JS expanded zip names are already generic, we add the version to the name before importing.
3. Create Form
Technically, a page works better than a form when there is nothing on the page but the chart, the HTML, and some CSS. However, in the app we were updating, it already used forms with pass-through HTML, along with a few "profile data" fields. Since our charts were "inside" the same HTML layout, we copied an existing form to be our Report form. We could have done the same thing by replacing the "profile" fields with <computed text> elements on a page, but that would have increased the job/project cost.
Most of the heavy lifting (code) is done in the JS Head.
Notes:
- This code is from our Mindwatering Document/Web Content Management app. There will be a few extra lines which are not related directly with charting; these will be unbolded in the code sections below.
- When the chart is instantiated, it must already have the structure. We can add data dynamically, but the columns and legend must already be defined - they have to be static.
- With Chart.JS version 2, you can clear the data without destroying the chart (structure). Therefore, this chart just destroys/clears the data and leaves the chart "empty" again.
- Make sure you have a SaveOptions field, that computes to "0". That will keep the form from saving if someone hacks a submit from the web.
- The chart loads 24 columns of job/project data. There are pagination buttons. Keep the button type as "button" so that they do not submit the page when clicked.
- Chart 2 and Chart 3 have technically the same structure and could have re-used the base "empty" creation function. However, we kept them separate since it is common for our reporting to change over time w/o the same data being used between charts.
Form:
RptJobChart1Web
Form Properties Box:
UnChecked - Render pass through HTML in Notes (Leave unchecked - Enable if you want to preview form in Notes. Not all JS features work in Notes client.)
On Web Access
- Content Type: HTML
- Character Set: Unicode (UTF-8)
Form Objects (List):
Window Title:
tmp:="Job Report - Revenue , Costs, and Hours Comparisons";
tmpsite:=@If(SiteName=""; ""; " - " + SiteName);
@ReplaceSubstring(@ReplaceSubstring(tmp; "("; ""); ")"; "") + tmpsite
HTML Head:
tmpreport1:= "<script src=\"/" + @WebDbName + "/Chart.bundle.min.js\"></script>";
tmpreport2:= "<script src=\"/" + @WebDbName + "/jquery.min.js\"></script>" + @NewLine +
"<script src=\"/" + @WebDbName + "/jquery.min.map\" type=\"application/json\"></script>";
REM {To not load the app CSS and the global app JQuery (which ends up the same, you could use the line below. };
notused:= tmpreport1 + @NewLine + tmpreport2;
tmp1a:= @Implode(@DbColumn("":"Nocache"; "":""; "lupHH";1); @NewLine);
(tmpreport1 + @NewLine) + @If(@IsError(tmp1a); ""; tmp1a)
JS Head:
var pageNumber = 0;
var pageSize = 24;
var rptChart1 = {};
var rptChart2 = {};
var nowDt = new Date();
window.chartColors = {
red: 'rgb(204, 51, 53)',
darkred: 'rgb(169, 8, 1)',
orange: 'rgb(235, 104, 65)',
gold: 'rgb(237, 201, 81)',
yellow: 'rgb(255, 223, 107)',
mustard: 'rgb(219, 182, 48)',
forest: 'rgb(1, 125, 88)',
green: 'rgb(48, 179, 139)',
lime: 'rgb(198, 217, 163)',
navy: 'rgb(24, 72, 144)',
blue: 'rgb(48, 134, 191)',
cyan: 'rgb(14, 174, 180)',
sky: 'rgb(185, 216, 237)',
purple: 'rgb(112, 42, 110)',
magenta: 'rgb(184, 0, 147)',
pink: 'rgb(237, 102, 210)',
darkbrown: 'rgb(65, 45, 10)',
brown: 'rgb(116, 73, 0)',
lightbrown: 'rgb(204, 150, 56)',
sand: 'rgb(238, 189, 103)',
grey: 'rgb(201, 203, 207)'
};
// create function called in jquery doc ready
function CreateRpt1Chart() {
// build empty data, populate after load
var barChartData = Rpt1CreateEmptyCanvas();
console.log("... empty data created.");
// create chart
console.log("Creating canvas1 ...");
var ctx = document.getElementById('canvas1').getContext('2d');
window.rptChart1 = new Chart(ctx, {
type: 'bar',
data: barChartData,
options: {
title: {
display: true,
text: 'Job Revenue Comparison - ' + nowDt.toDateString()
},
tooltips: {
mode: 'index',
intersect: false
},
responsive: true,
scales: {
xAxes: [{
stacked: true,
}],
yAxes: [{
stacked: true
}]
}
}
});
// end chart, call load/update
updateRpt1ChartData(pageSize,pageNumber);
}
// update chart1 function called on reload, +/i
var updateRpt1ChartData = function(ps, pn) {
nowDt = new Date();
$.ajaxSetup({
async:true,
type: "GET",
});
rptChart1.options.title = {
display: true,
text: 'Job Revenue Comparison - ' + nowDt.toDateString()
};
$.getJSON("/" + dbpath + "/api/data/collections/name/lupRptJComp?systemcolumns=0&ps="+ps+"&page="+pn, function(data) {
console.log(" Loading page " + pn + " with pagesize " + ps + " returned " + data.length + " entries");
for (var i=0; i < data.length; i++) {
// console.log( " index: " + i + " Job: " + data[i].J_ID );
var col0ID = data[i].J_ID;
var col1R1 = data[i].J_R1;
var col2R2 = data[i].J_R2;
var col3R3 = data[i].J_R3;
var col4R4 = data[i].J_R4;
var col5R5 = data[i].J_R5;
var col6R6 = data[i].J_R6;
var col7R7 = data[i].J_R7;
var col8R8 = data[i].J_R8;
// populate bottom label and datasets
rptChart1.data.labels.unshift( col0ID);
rptChart1.data.datasets[0].data.unshift( col1R1 );
rptChart1.data.datasets[1].data.unshift( col2R2 );
rptChart1.data.datasets[2].data.unshift( col3R3 );
rptChart1.data.datasets[3].data.unshift( col4R4 );
rptChart1.data.datasets[4].data.unshift( col5R5 );
rptChart1.data.datasets[5].data.unshift( col6R6 );
rptChart1.data.datasets[6].data.unshift( col7R7 );
rptChart1.data.datasets[7].data.unshift( col8R8 );
}
rptChart1.update();
});
}
function Rpt1UpdateChartPrev() {
if (pageNumber <1 ) {
alert( "Already on page 1.");
} else {
pageNumber = pageNumber - 1;
rptChart1.config.data = Rpt1CreateEmptyCanvas();
updateRpt1ChartData(pageSize, pageNumber);
rptChart2.config.data = Rpt2CreateEmptyCanvas();
updateRpt2ChartData(pageSize, pageNumber);
rptChart3.config.data = Rpt3CreateEmptyCanvas();
updateRpt3ChartData(pageSize, pageNumber);
}
}
function Rpt1UpdateChartNext() {
pageNumber = pageNumber + 1;
rptChart1.config.data = Rpt1CreateEmptyCanvas();
updateRpt1ChartData(pageSize, pageNumber);
rptChart2.config.data = Rpt2CreateEmptyCanvas();
updateRpt2ChartData(pageSize, pageNumber);
rptChart3.config.data = Rpt3CreateEmptyCanvas();
updateRpt3ChartData(pageSize, pageNumber);
}
function Rpt1CreateEmptyCanvas() {
var barChartData = {
labels: [ ],
datasets: [{
label: 'Rev. Ph. 1',
backgroundColor: window.chartColors.red,
data: []
}, {
label: 'Rev. Ph. 2',
backgroundColor: window.chartColors.orange,
data: []
}, {
label: 'Rev. Ph. 3',
backgroundColor: window.chartColors.mustard,
data: []
}, {
label: 'Rev. Ph. 4',
backgroundColor: window.chartColors.green,
data: []
}, {
label: 'Rev. Ph. 5',
backgroundColor: window.chartColors.blue,
data: []
}, {
label: 'Rev. Ph. 6',
backgroundColor: window.chartColors.purple,
data: []
}, {
label: 'Rev. Ph. 7',
backgroundColor: window.chartColors.magenta,
data: []
}, {
label: 'Rev. Ph. 8',
backgroundColor: window.chartColors.brown,
data: []
} ]
}
return barChartData;
}
// ---------------------------- CHART 2 -------------------------------
// update chart2 function called on reload, +/i
var updateRpt2ChartData = function(ps, pn) {
nowDt = new Date();
$.ajaxSetup({
async:true,
type: "GET",
});
rptChart2.options.title = {
display: true,
text: 'Job Actual and Budgeted Expenses Comparison - ' + nowDt.toDateString()
};
$.getJSON("/" + dbpath + "/api/data/collections/name/lupRptJComp?systemcolumns=0&ps="+ps+"&page="+pn, function(data) {
console.log(" Loading page " + pn + " with pagesize " + ps + " returned " + data.length + " entries");
for (var i=0; i < data.length; i++) {
console.log( " index: " + i + " Job: " + data[i].J_ID );
// costs/exp actual
var col0ID= data[i].J_ID;
var col1CA = data[i].J_CA1;
var col2CA = data[i].J_CA2;
var col3CA = data[i].J_CA3;
var col4CA = data[i].J_CA4;
var col5CA = data[i].J_CA5;
var col6CA = data[i].J_CA6;
var col7CA = data[i].J_CA7;
var col8CA = data[i].J_CA8;
// costs/exp budgeted
var col1CE = data[i].J_CE1;
var col2CE = data[i].J_CE2;
var col3CE = data[i].J_CE3;
var col4CE = data[i].J_CE4;
var col5CE = data[i].J_CE5;
var col6CE = data[i].J_CE6;
var col7CE = data[i].J_CE7;
var col8CE = data[i].J_CE8;
// two side-by-side stacks in report
// cost/exp actual
rptChart2.data.labels.unshift( col0ID);
rptChart2.data.datasets[0].data.unshift( col1CA );
rptChart2.data.datasets[1].data.unshift( col2CA );
rptChart2.data.datasets[2].data.unshift( col3CA );
rptChart2.data.datasets[3].data.unshift( col4CA );
rptChart2.data.datasets[4].data.unshift( col5CA );
rptChart2.data.datasets[5].data.unshift( col6CA );
rptChart2.data.datasets[6].data.unshift( col7CA );
rptChart2.data.datasets[7].data.unshift( col8CA );
// costs/exp budget 2nd stack (1)
rptChart2.data.datasets[8].data.unshift( col1CE );
rptChart2.data.datasets[9].data.unshift( col2CE );
rptChart2.data.datasets[10].data.unshift( col3CE );
rptChart2.data.datasets[11].data.unshift( col4CE );
rptChart2.data.datasets[12].data.unshift( col5CE );
rptChart2.data.datasets[13].data.unshift( col6CE );
rptChart2.data.datasets[14].data.unshift( col7CE );
rptChart2.data.datasets[15].data.unshift( col8CE );
}
rptChart2.update();
});
}
// create function called in jquery doc ready
function CreateRpt2Chart() {
// build empty data, populate after load
var barChartData = Rpt2CreateEmptyCanvas();
console.log("... empty data created.");
// create chart
console.log("Creating canvas2 ...");
var ctx = document.getElementById('canvas2').getContext('2d');
window.rptChart2 = new Chart(ctx, {
type: 'bar',
data: barChartData,
options: {
title: {
display: true,
text: 'Job Actual and Budgeted Expense Comparison - ' + nowDt.toDateString()
},
tooltips: {
mode: 'index',
intersect: false
},
responsive: true,
scales: {
xAxes: [{
stacked: true,
}],
yAxes: [{
stacked: true
}]
}
}
});
// end chart, call load/update
updateRpt2ChartData(pageSize,pageNumber);
}
function Rpt2CreateEmptyCanvas() {
var barChartData = {
labels: [ ],
datasets: [{
label: 'Act 1',
backgroundColor: window.chartColors.red,
stack: 'Stack 0',
data: []
}, {
label: 'Act 2',
backgroundColor: window.chartColors.orange,
stack: 'Stack 0',
data: []
}, {
label: 'Act 3',
backgroundColor: window.chartColors.mustard,
stack: 'Stack 0',
data: []
}, {
label: 'Act 4',
backgroundColor: window.chartColors.forest,
stack: 'Stack 0',
data: []
}, {
label: 'Act 5',
backgroundColor: window.chartColors.navy,
stack: 'Stack 0',
data: []
}, {
label: 'Act 6',
backgroundColor: window.chartColors.purple,
stack: 'Stack 0',
data: []
}, {
label: 'Act 7',
backgroundColor: window.chartColors.darkbrown,
stack: 'Stack 0',
data: []
}, {
label: 'Act 8',
backgroundColor: window.chartColors.lightbrown,
stack: 'Stack 0',
data: []
}, {
label: 'Bud 1',
backgroundColor: window.chartColors.darkred,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 2',
backgroundColor: window.chartColors.gold,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 3',
backgroundColor: window.chartColors.yellow,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 4',
backgroundColor: window.chartColors.green,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 5',
backgroundColor: window.chartColors.blue,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 6',
backgroundColor: window.chartColors.magenta,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 7',
backgroundColor: window.chartColors.brown,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 8',
backgroundColor: window.chartColors.sand,
stack: 'Stack 1',
data: []
} ]
}
return barChartData;
}
// ----------------------- CHART 3 ------------------------------------
// update chart3 function called on reload, +/i
var updateRpt3ChartData = function(ps, pn) {
nowDt = new Date();
$.ajaxSetup({
async:true,
type: "GET",
});
rptChart3.options.title = {
display: true,
text: 'Job Actual and Budgeted Hours Comparison - ' + nowDt.toDateString()
};
$.getJSON("/" + dbpath + "/api/data/collections/name/lupRptJComp?systemcolumns=0&ps="+ps+"&page="+pn, function(data) {
console.log(" Loading page " + pn + " with pagesize " + ps + " returned " + data.length + " entries");
for (var i=0; i < data.length; i++) {
console.log( " index: " + i + " Job: " + data[i].J_ID );
// hours actual
var col0ID= data[i].J_ID;
var col1LA = data[i].J_LA1;
var col2LA = data[i].J_LA2;
var col3LA = data[i].J_LA3;
var col4LA = data[i].J_LA4;
var col5LA = data[i].J_LA5;
var col6LA = data[i].J_LA6;
var col7LA = data[i].J_LA7;
var col8LA = data[i].J_LA8;
// hours budgeted
var col1CE = data[i].J_CE1;
var col2CE = data[i].J_CE2;
var col3CE = data[i].J_CE3;
var col4CE = data[i].J_CE4;
var col5CE = data[i].J_CE5;
var col6CE = data[i].J_CE6;
var col7CE = data[i].J_CE7;
var col8CE = data[i].J_CE8;
// two side-by-side stacks in report
// hours actual
rptChart3.data.labels.unshift( col0ID);
rptChart3.data.datasets[0].data.unshift( col1LA );
rptChart3.data.datasets[1].data.unshift( col2LA );
rptChart3.data.datasets[2].data.unshift( col3LA );
rptChart3.data.datasets[3].data.unshift( col4LA );
rptChart3.data.datasets[4].data.unshift( col5LA );
rptChart3.data.datasets[5].data.unshift( col6LA );
rptChart3.data.datasets[6].data.unshift( col7LA );
rptChart3.data.datasets[7].data.unshift( col8LA );
// hours budget 2nd stack (1)
rptChart3.data.datasets[8].data.unshift( col1CE );
rptChart3.data.datasets[9].data.unshift( col2CE );
rptChart3.data.datasets[10].data.unshift( col3CE );
rptChart3.data.datasets[11].data.unshift( col4CE );
rptChart3.data.datasets[12].data.unshift( col5CE );
rptChart3.data.datasets[13].data.unshift( col6CE );
rptChart3.data.datasets[14].data.unshift( col7CE );
rptChart3.data.datasets[15].data.unshift( col8CE );
}
rptChart3.update();
});
}
// create function called in jquery doc ready
function CreateRpt3Chart() {
// build empty data, populate after load
var barChartData = Rpt3CreateEmptyCanvas();
console.log("... empty data created.");
// create chart
console.log("Creating canvas3 ...");
var ctx = document.getElementById('canvas3').getContext('2d');
window.rptChart3 = new Chart(ctx, {
type: 'bar',
data: barChartData,
options: {
title: {
display: true,
text: 'Job Actual and Budgeted Hours Comparison - ' + nowDt.toDateString()
},
tooltips: {
mode: 'index',
intersect: false
},
responsive: true,
scales: {
xAxes: [{
stacked: true,
}],
yAxes: [{
stacked: true
}]
}
}
});
// end chart, call load/update
updateRpt3ChartData(pageSize,pageNumber);
}
// empty data to build structure of chart
function Rpt3CreateEmptyCanvas() {
var barChartData = {
labels: [ ],
datasets: [{
label: 'Act 1',
backgroundColor: window.chartColors.red,
stack: 'Stack 0',
data: []
}, {
label: 'Act 2',
backgroundColor: window.chartColors.orange,
stack: 'Stack 0',
data: []
}, {
label: 'Act 3',
backgroundColor: window.chartColors.mustard,
stack: 'Stack 0',
data: []
}, {
label: 'Act 4',
backgroundColor: window.chartColors.forest,
stack: 'Stack 0',
data: []
}, {
label: 'Act 5',
backgroundColor: window.chartColors.navy,
stack: 'Stack 0',
data: []
}, {
label: 'Act 6',
backgroundColor: window.chartColors.purple,
stack: 'Stack 0',
data: []
}, {
label: 'Act 7',
backgroundColor: window.chartColors.darkbrown,
stack: 'Stack 0',
data: []
}, {
label: 'Act 8',
backgroundColor: window.chartColors.lightbrown,
stack: 'Stack 0',
data: []
}, {
label: 'Bud 1',
backgroundColor: window.chartColors.darkred,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 2',
backgroundColor: window.chartColors.gold,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 3',
backgroundColor: window.chartColors.yellow,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 4',
backgroundColor: window.chartColors.green,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 5',
backgroundColor: window.chartColors.blue,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 6',
backgroundColor: window.chartColors.magenta,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 7',
backgroundColor: window.chartColors.brown,
stack: 'Stack 1',
data: []
}, {
label: 'Bud 8',
backgroundColor: window.chartColors.sand,
stack: 'Stack 1',
data: []
} ]
}
return barChartData;
}
HTML on Form:
______________________________________________________________
Hidden:
End Hidden
Hidden:
Server environment and cgi variables for reference of fields on form. Fields are CFD.
The server's domain name (e.g. www.mindwatering.com) and the path are also written to Javascript variables for client-side reference.
HTTP_User_Agent Query_String Query_String_Decoded Server_Name Path_Info HTTP_Referer DbPath SiteName LayoutFlags
End Hidden
<script language="JavaScript" type="text/javascript">
var dbpath="<Computed Value>";
var servername="<Computed Value>";
</script>
<!-- Begin Layout -->
<div id="pglayout" class="pglayout">
<Computed Subform>
<Computed Subform>
<!-- Center & Left and Right Columns -->
<div id="columngrp" class="columngrp">
<div id="columncenter" class="columncenter">
<div id="vwcontent" class="vwcontent">
<div id="wpvbody" class="wpvbody">
<div id="wptitle" class = "wptitle"><h1><Computed Value></h1></div>
<div id="rptContain1" class="rptContain1">
<div id="rptHdr1" class="rptHdr">
<button id="rptHdr1Prev" type="button" onclick="Rpt1UpdateChartPrev();">Previous</button> <button id="rptHdr1Next" type="button" onclick="Rpt1UpdateChartNext();">Next</button>
</div>
<canvas id="canvas1"></canvas>
</div>
<div id="rptContain2" class="rptContain2">
<div id="rptHdr2" class="rptHdr">
<button id="rptHdr2Prev" type="button" onclick="Rpt1UpdateChartPrev();">Previous</button> <button id="rptHdr2Next" type="button" onclick="Rpt1UpdateChartNext();">Next</button>
</div>
<canvas id="canvas2"></canvas>
</div>
<div id="rptContain3" class="rptContain3">
<div id="rptHdr3" class="rptHdr">
<button id="rptHdr3Prev" type="button" onclick="Rpt1UpdateChartPrev();">Previous</button> <button id="rptHdr3Next" type="button" onclick="Rpt1UpdateChartNext();">Next</button>
</div>
<canvas id="canvas3"></canvas>
</div>
<script language="JavaScript" type="text/javascript">
// prepare chart with an empty array for data within the datasets
$(document).ready(function() {
CreateRpt1Chart();
CreateRpt2Chart();
CreateRpt3Chart();
});
</script>
<div id="vwnavprevnext" class="vwnavprevnext"><br><< previous page </div>
</div>
</div>
</div>
</div>
<!-- Footer -->
<div id="clearcolumns" class="clearcolumns"><Computed Value> </div>
<Computed Subform>
<Computed Subform>
</div>
<!-- End Layout -->
Hidden:
SaveOptions CWC - "0" Used to keep form from saving
End Hidden
______________________________________________________________
previous page
|