About This Exercise:

View the dashboard.

There are times that you will need to create a dynamic project dashboard to meet a particular stakeholder’s needs. There are many potential solutions:

  1. Use your project tracking system (JIRA, DevOps, Project Management Server) and any 3rd party plugins
  2. Set up Excel to communicate through Power Query and link to the source (XML or JSON)
  3. Extract JSON data from the source using your favorite method and use a graphing tool to display on a website.

Option 1 can be expensive, since you may have to rely on expensive plugins. Option 2 is very simple, but is not necessarily web enabled and not the best solution if the stakeholder doesn’t have a tracking system account or is not on your network.

Number three sounds daunting, but once set up, it is easy to work with. The first time I did this it took about three hours to set up four charts. Now that I have a template, I can add custom charts in about 10-15 minutes. I am not a developer (although I know PHP); I am primarily a project leader/business analyst who knows some programming. If you cannot manage this, there probably is someone on your team that can. It will work with Java, .Net or just plain Javascript. Since this approach can be used on multiple projects and should be budgeted and tracked accordingly.

Tools Used:

  • Database (MySQL in this example) OR JSON web service
  • Server Side Script (PHP and PDO are used in this example)
  • Chart.Js- A free open source Canvas Chart Generator

You need to create views in your database (or a SQL query) that organizes the data the way you want it.

Getting Started with a Database:

Within PHP you need to create your Connection String. I am using PDO, since it will work with multiple databases. If you are connecting to a web service, you would skip these database connection steps.

$connect = new PDO("mysql:host=localhost;dbname="ProjectData", "DataBaseAccount", "Password");

This should be put in a separate file and saved in your /inc subfolder. you can then include it in your statement.

require_once("grid/conf.php");

You will need to write a sql statement to extract the data from the database. You can simplify this by using views to build and organize your data. For the three charts on the sample dashboard, I used:

$sqlCost="select id, Planned, Actual, CONCAT('Wk-',Week) as Week from vw_chart_cost order by week asc";

$sqlTask="select id,Task,Total_hrs, Complete_hrs from vw_chart_task order by id asc";

$sqlBurn="select id,CONCAT('DAY ',Day) as Day, Completed,Remaining_Ef, Remaining_Task from vw_chart_burndown";

Your statements would be unique to your desired fields. However, you should use a separate statement for each chart (unless you have multiple charts displaying the same data in different ways).

To convert the SQL statement to JSON, I wrote the following simple function:

function CreateJSON($sql,$con) {
 
$statement = $con->prepare($sql);
$statement->execute();
$data=$statement->fetchAll(\PDO::FETCH_OBJ);
$JSONString=json_encode($data);
return $JSONString;
}

The CreateJSON()  function takes the SQL statement and the connection string as parameters and returns a JSON string like the one below:

{"id":"1","Planned":"2300","Actual":"2100","Week":"Wk-1"},{"id":"2","Planned":"3275","Actual":"3180","Week":"Wk-2"},{"id":"3","Planned":"4285","Actual":"3950","Week":"Wk-3"},{"id":"4","Planned":"6200","Actual":"5900","Week":"Wk-4"},{"id":"5","Planned":"8800","Actual":"8900","Week":"Wk-5"},{"id":"6","Planned":"11600","Actual":"12600","Week":"Wk-6"}]

You would get this variable by setting a variable ($costJSON) to the results of the CreateJSON() function:

$costJSON=CreateJSON($sqlCost,$connect);

The variables hold the JSON strings that we will be used by Chart.JS to build the graph.

Building the Chart (Web Service or Database):

Now that you have your data source you are able to break it down for Chart.js  If you used the database (as I did) you simply

var jsonDataCost={
"jsonarray": <?php echo $costJSON; ?>

};

var labels = jsonDataCost.jsonarray.map(function(e) {
return e.Week;
});
var dataPlanned = jsonDataCost.jsonarray.map(function(e) {
return e.Planned;
});
var dataActual = jsonDataCost.jsonarray.map(function(e) {
return e.Actual;
});

If you are using an API, you need to do the following to get your data in a format that jsonarray can read.  You need to use the JQuery ajax protocol:
 

$.ajax({
type: "GET", //or "post"
url: "fully formed api url",
data: "{}",
dataType: "text",
success: function (data) {
alert("Ok:" + data);//take out when you are done testing
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert("Err:" + textStatus + "," + errorThrown.toString());
}
});
;

You will need to replace the PHP in "jsonarray": <?php echo $costJSON; ?> to whatever variable you assigned the data that is coming from the API.  It will take any properly formed JSON string.  You  can see the JSON strings used to create the charts in this example at the bottom of the page.

Now you can start building your graphs.  Within your HTML you need to create divs for them.  You can be creative with the layout and even make them draggable (I did not do this).

<canvas id="graphCost"></canvas>
<canvas id="graphTask"></canvas>
<canvas id="graphBurn"></canvas>

Now you have to build the Chart.js code to build each graph.  One sample is below:

var ctx = document.getElementById('graphCost');
var myChart = new Chart(ctx, {
type: 'line',

data: {
labels: labels,
datasets: [{
label: 'Actual',
data: dataActual,
fill: true,
borderColor: "#3e95cd",
backgroundColor:"#3c88bb",
},

{
label: 'Planned',
data: dataPlanned,
fill:false,
borderColor: "#c45850",
}]
},
options: {
responsive:true,
scales: {
yAxes: [{
ticks: {
beginAtZero: false
}
}]
},
title:{
display:true,
text:'Actual Cost vs. Planned, Cumulative'

}


}
});

This looks a lot more complicated than it is.  Most code editors (I use CodeLobster) will keep track of the brackets and parenthesis for you, and let you know when there is an imbalance.  Trust me, Chart.js is a tool is worth learning.  If you find the javascript daunting, you can use a script generator (or this one) to help you. Your browser’s error console will help you identify any problems.

Read about Chart.js

View the dashboard.