Objectif Lune joins Upland Software.Learn more >

Back to all How-tos

Dynamic Charts for use with Web Dashboards

Original Author: Colin Casey

If you have ever sat down to think about how to create a dynamic chart (or two) for a Connect web page (or dashboard), only to find that it all seems too complex and there must be an easier way, then this could be the article for you. Having challenged myself to do it, and despite making every mistake in the book, I managed to achieve it, with a little help from my friends, in a way that I consider to be a ‘best practice’ approach.

This How To will explain what I did, so that you can do the same and even apply the methodology to other charts enabling you to impress people with your newly found skills. It might feel long and complex, but it’s just long. You should take your time to go through it because you will be surprised at how simple it can be.

Resources

Attached is a zip file containing my template, workflow and database. It uses two (2) charts so the functions are named with additional “1” or “2”, but it should not confuse you too much.

Note: The data comes from an Access database and you will need to create your own Connection to it. You MAY have to change the date format in the create_chart1 and create_chart2 functions (located in chartdiv1.js in the Designer template) to match your locale (my functions use DD/MM/YYYY)

Dashboard-Sample

Dynamic Charts – Overview

Before we jump into the detail of “how it’s done”, it’s important to understand the basic elements that make it possible to have a chart. There are many ways to do this, some more complex than others, but since I’m aiming at a dashboard, I chose to use the following method.

  1. Use an external chart library to create the chart for the client when the web page is requested from workflow.
  2. Have the chart request data from workflow using ajax every 60 seconds (or upon manual refresh)

This method means I don’t have to worry about data mappers, it also allows me to have several ajax functions – one for each chart – capable of requesting data from multiple sources in different formats. Creating multiple data sources within a single data mapper would be – in my opinion – overly complex.

Tip: Since this method uses ajax – we need to make sure the jQuery library is included in the template. If you don’t have it, you can get it by starting with foundation template and saving it to disk by dragging and dropping from the Designer!

amCharts

Obviously I wasn’t going to create the charts myself, so I had to pick a library to use and I chose amCharts. The main reason was because it has a really cool online UI that allows you create 95% of the html you need to get the job done without having to touch a piece of code!

So, before I talk about the Connect elements, it’s worth getting into the amCharts Live Editor which can be found here

Live Editor

Clicking on the link with bring up New wizard page below, from which you can select your chart base-point.

In my example, I chose the Line type, then date series, monthly

Once selected, you’re taken straight into the Live editor where you can play around with all of the chart values and attributes to get it ready for Connect. In the editor you can add/remove & edit columns, edit labels and even select some themes. each chart has it’s own set of “attributes”, so it’s worth playing around and getting to know them for the charts you think you might want to use more often.

Copy the HTML

Once you’ve got the chart looking how you want it, having edited the data & attributes to match your own data, then you just need to copy the HTML

There’s a button for it called Save HTML. When you click on it, you’ll get something like this…

You can then either copy it to the clipboard or save it to your local file system.

The Connect Template

Before we start transferring the html into the source page of Connect and expecting it to work, we need to understand the template basics.

Overview

The template will create the html in which the chart can be placed. The chart will be produced by the remote amCharts library after the client web page is received. It will then use an ajax call to workflow to populate that data. The Connect template needs to link all of these elements together and then make sure they all run at the right time.

amCharts HTML

The html below was produced by the amCharts editor, I’ve removed the amCharts javascript code – we’ll come back to that later.

<!DOCTYPE html>
 <html>
     <head>
         <title>chart created with amCharts | amCharts</title>
         <meta name="description" content="chart created using amCharts live editor" />
                  <!-- amCharts javascript sources -->
         <script type="text/javascript" src="https://www.amcharts.com/lib/3/amcharts.js"></script>
         <script type="text/javascript" src="https://www.amcharts.com/lib/3/serial.js"></script>
         <script type="text/javascript" src="https://www.amcharts.com/lib/3/themes/dark.js"></script>
                 <!-- amCharts javascript code -->
         <script type="text/javascript">
          .....
          .....
         </script>
     </head>
     <body>
         <div id="chartdiv" style="width: 100%; height: 400px; background-color: #282828;" ></div>
     </body>
 </html>

We don’t need all of it, just the amChart javascript sources and the <div>

amChart Javascript sources

These sources will vary from chart to chart, so make sure you check and include them all – and include them on your web page in the right order!!!

It’s easiest to use the Remote Javascript feature within Connect (although it won’t work if you’re off-line). If you want the off-line versions you can find some (not all) of them here

Just select the Javascripts folder in the design and right click to bring up New Remote JavaScript

Tip: client JavaScripts run on the remote users PC, whereas server Scripts run on the workflow PC. Client side JavaScript can’t access workflow variables whereas Server scripts can! In order to pass server information to client side scripts you need to create element attributes. Clients scripts can use JQuery to read these attributes values.

Make sure you do this for each one.

HTML <div>

The <div> is a placeholder for the chart. In this example it describes a box 100% wide and 400px deep with a background-color to show the graph in when it’s created. You can cut & paste this div into your current template – using the source TAB or create your own.

Tip: Make sure you place your <div> below some other html!

<p>This is my Chart</p> <div id="chartdiv" style="width: 100%; height: 400px; background-color: #282828;" ></div>

Client JavaScript Overview

In order to make the chart work there are a few elements that need to be combined. So, let’s start with the basic shape of a new client JavaScript resource that we might call chartdiv.js

// this function make sure the client document is loaded before code is executed
$( document ).ready(function() {  //this calls a function which creates a chart for the div with id="chartdiv"
    create_chart("chartdiv");     // this calls the same chart function every every 60 seconds
      setInterval( function () {          create_chart("chartdiv");
     }, 60000 );                  // this calls the same chart function with the button with id="refresh" is clicked     $("#refresh").on('click', function() {
         create_chart("chartdiv");      })

    function create_chart(div) {
        ...see below
    }
    function generate_data() {
        ....see below
    }
});

Chart Function

To make the client JavaScript more efficient the amCharts script is included within function. Most of it is a simple cut & paste from the amCharts HTML, with the exception of the dataProvider object – which was a static array of your sample data – being replaced by a function to generate data.

function create_chart(div) {
     AmCharts.makeChart(div,     
    {
         "type": "serial",
         "categoryField": "date",
         "dataDateFormat": "DD/MM/YYYY",
         "theme": "black",
         "categoryAxis": {
             "minPeriod": "DD",
             "parseDates": true
         },
         "chartCursor": {
             "enabled": true,
             "categoryBalloonDateFormat": "MMM YYYY"
         },
         "chartScrollbar": {
         "enabled": true
         },
         "trendLines": [],
         "graphs": [{
             "bullet": "round",
             "id": "AmGraph-1",
             "title": "Selected Value",
             "valueField": "column-1"
         }],
         "guides": [],
         "valueAxes": [{
             "id": "ValueAxis-1",             "title": "Total Used"
         }],
         "allLabels": [],
         "balloon": {},
         "legend": {
             "enabled": true,
             "useGraphSettings": true
         },         "titles": [{
             "id": "Title-1",
             "size": 15,
             "text": "Usage over Time"         }],
         "dataProvider": generate_data()
     }) }

Generating Data with Ajax

In the original HTML the dataProvider object was initialized with a static array of data. But we need to get our data from workflow so we use ajax to do that. The ajax function needs the parameters to communicate with workflow and one of those is the url.

The url for the workflow is passed using the variable %9. The server side script – applied to the selector #chartdiv – checks to see it contains a value (running from workflow) or not (in Design mode) and sets up the ajbase attribute accordingly.

var ajax_base_url;

if(automation.jobInfos.JobInfo9!='') {
    ajax_base_url = automation.jobInfos.JobInfo9;
} else {
    ajax_base_url = "http://127.0.0.1:8080";
}

results.attr("ajbase", ajax_base_url);

The generate_data function looks like this

function generate_data() {
     var jsondata;     var chartData = [];     var ajbase_url;       // this jQuery gets the workflow url from the attribute of chartdiv called ajbase      ajbase_url = $('#chartdiv').attr('ajbase') + "/getchart_data";
         $.ajax({
             type: "GET",
             url: ajbase_url,
             dataType: "json",
             async: false,
             success: function(json) {    
                // alert(JSON.stringify(json));
                 jsondata = json;
                 for(var i = 0; i< jsondata.Root.DataPage.length; i++) {
                     chartData.push({
                         "date": jsondata.Root.DataPage[i].Record.TRX_Date,
                         "column-1": parseInt(jsondata.Root.DataPage[i].Record.SumOfTRX_Qty)
                     });                 }         },
             error: function (request, status, error) {
                 alert("Could not get any data");
             }
          });          return chartData;
      }

A couple of points about this function:

  • async: false means that the function will wait for the data to come back. If it times out, the error function will display a message on the client’s PC.
  • upon success, the data from workflow is stored in a json object called json and then assigned to another call jsondata
  • the for loop, reads the TRX_Date and the SumOfTRX_Qty returned by workflow into the dataProvider object array using the .push() method
  • the result from workflow is always a string; the date value is a string field, whilst the column-1 field is an integer so the matching value needs to be converted

Tip: Field names from the database may be altered by the workflow – spaces converted into underscores etc. – so it’s important to view the data to make sure you know the final json names. You can use the alert(JSON.stringify(json)) function to display the result in the browser!

So, with the template setup, all we need is a workflow capable of delivering the dashboard and responding to the ajax requests

Workflow

Data First

The most important thing to get right is the communication between the web page and the dashboard. This is done through the ajax function calling a url handled by workflow.

A typical workflow would look like this

So, let’s go through the steps …

HTTP Server Input

This captures the url request from the ajax call and needs to be setup to return text/json.

Tip: text/json is not an option in the drop down list, so type it in manually!

Database Query

This task will get the data for your chart. The emulation should be set to XML (it will be converted to JSON next) and the SQL should ensure that the minimum amount of data is retrieved to avoid sending the entire database to the client.

In the Dynamic SQL tab (as shown below), at the Database connection string parameter, a DSN string must be inputted to enable access to the database (‘OL eDocs.accdb’, see resources).

In order to do this, we will download and install Microsoft Access Database Engine 2010 (‘AccessDatabaseEngine.exe’, not 64-bit) from the following link: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255 . Double click on the executable file to install it. Now, to create a Data Source Name (DSN) for our Workflow task, follow these quick steps:

  • Open the Windows Control Panel and navigate to Administrative Tools > ODBC Data Source(32-bit)
  • Click on the “System DSN” tab and click on Add
  • Select the driver that corresponds to your file type
  • In our case, we’re using an Access file. Select Microsoft Access Driver (*.mdb, *.accdb)
  • Click Finish to select your Access, CSV or Excel file
  • Give your DSN a name. We will call it “DSN_Test” for this example.
  • Under Database, click on Select… and browse to the location of the ‘OL eDocs.accdb’
  • Click OK.

Finally, to input the right Database connection string, click on the button beside the field (Connect to ODBC Data Source), click on the second tab, Machine Data Source and choose the DSN created earlier (ex: ‘DSN_Test’), click OK. Click OK again if you haven’t added any password for it. A string will be added automatically in the Database connection string field as shown above.

To know more about DSNs and how they can be used with Connect, you can refer to Extracting Database Information in a Data Mapping Configuration.

Script

The third task is a generic script (available in the dashboard_sample.zip) that converts the XML into a json string and assigns it to a variable called jsonData.

Create File

The fourth task creates a new file from the jsonData variable which is returned to the dashboard.

Tip: You can view the json data produced in the workflow to validate your final json object names for the ajax function in the template!

Tip: if the workflow is active and the services are running you should be able to preview the chart in Live mode from within the Designer!

Dashboard

The real test of the solution is to make the dashboard work from your browser.

The url for this dashboard would be http://<workflowIP:port>/dashboard

The result of the request will be handled by another workflow process with an HTTP Server Input – this time the MIME should be text/html

The second task simply sets the workflow IP & port address into jobinfo 9 which is passed to the template.

The template has no data and so the Record ID should be set to 0

Addendum: Data Mapper instead of Ajax?

Using a data mapper for your data is not covered in this article, but the key concepts are there. So take a look at the small changes below.

Workflow

First, you will need to change the workflow to get data, create a data map and then pass the data to the dashboard template using a valid Record ID.

Second, you won’t need the ajax processes anymore.

Server side script

The data mapping creates data at the server which needs to be sent to the client. You could build the dataProvider string in a server script by taking the code form the generate_data() function. Then use the attribute technique to pass data to the client . The example below can be used in a Server Script with selector #chartdiv.

var chartData = [];

for(var i = 0; i< record.tables.details.length; i++) {
    chartData.push({
        "date": record.tables['detail'][i].fields['TRX_Date'];
        "column-1": parseInt(record.tables['detail'][i].fields['SumOfTRX_Qty'];)
    });
}
results.attr('data-chart',JSON.stringify(chartData));

Note: TRX_Date and SumOfTRX_Qty are just examples!

Client side script

The client side script containing the generate_data() function can then be changed to get the attribute data-chart and return the data to the chart.

function generate_data() {
     var chartData = $("#chartdiv1").attr('data-chart');
Tags
Designer output web

Leave a Reply

Your email address will not be published. Required fields are marked *

Cancel