The objective of this tutorial is to try and get Excel experts to try their hand in creating online dashboards using the Python Django web framework and the ChartJS JavaScript charting library. There is a learning curve but once you have mastered the fundamentals and created a template to build upon, I hope you will find this is a reasonable alternative to creating dashboards on Excel. The time invested in your learning will pay off in the speed of creating new dashboard projects and hopefully a higher quality of output.

All the code has been uploaded to a GitHub repository. It also contains the steps you need to take to install and run the project locally on your PC. The project lives within a Python Virtual Environment. This means when you delete the repository on your PC, it also deletes the associated additional software installed to run the project. Once you have cloned the repository, you are free to make changes and use it as a learning resource. If you make a mistake, just make another clone of the repository and reset.

The technologies discussed in this tutorial are really worth checking out if you have a career in data analysis and reporting. They could represent the final stage of a data project, to display the results with a wide array of professional looking reports to choose from giving you a high degree of control over the output.

Having been a finance analyst most my career, Excel was always the weapon of choice for visually displaying data. With knowledge of macros and VBA you can build complete bespoke solutions for small to medium scale data product requirements. Although Excel is a comprehensive solution, it leaves you tied to the Microsoft stack. Once you are in, there is no reason to look outside the MS Office world and this blinds you to the other wonderful alternatives out there.

Although I was happy to create solutions on top of MS Excel and Access, I was exposed to the awesomeness of web reporting libraries only after starting web development. The problem suddenly goes from "how do I hack a report to do something specific?" to "all these libraries allow me to do this specific thing out of the box. Which do I choose?".

There are a number of mature solutions out there for online reporting. Although the price of MS Office has come down considerably over the years, all of the popular online reporting solutions out there are really high quality and completely free. Though they have a steep learning curve, once you learn some fundamentals, you can create secure, beautiful interactive dashboards with a high degree of control over the output.

Why would you want to do this tutorial

You have gone as far as you can in creating dashboards in Excel and Access and want to see what the market place in other technologies looks like. In the end, sending reports via email attachments can be cumbersome. Especially if there are many recipients of the reports, each needing to see only a small part of the bigger picture.

If you want an additional layer of security, I suppose you could password protect the files, but there are services out there that can crack these for you. Also the solution is inelegant.

What about version control? If there is a regular report coming out, there has to be a version control system in place, by either having the version in the file name or embedded somewhere in the file.

The report that you want isn't available on MS Office. Ever tried doing a Gantt chart on MS Office using conditional formatting? Not fun. The charting library we will be looking at have a variety of reports many of which are not available in MS Office.

Excel reports are static in nature. If you want more insight into how the numbers came about, there is no elegant solution to get there. Unless you are presenting data on a pivot table, there is no elegant solution for linking reports and data tables together. A properly designed web dashboard, can take you on a journey of analysis, presenting the data in a way where the user can draw their own conclusions.

The Solution

The solution will be a basic dashboard that can be accessed using a web browser. It will be designed on top of the Python Django web framework. The reports will be created on the ChartJS JavaScript library. Going through the solution, I will try and implement proper patterns for development like modularization where the data for the reports will be in one section of the site and the reports in another. After this you will have the option of creating a fully fledged dashboard solution quickly.

Assumptions

The tutorial assumes a working knowledge of Python and Django and that you have been through at least one cycle of implementing a web solution. This means you also have a working knowledge of HTML, CSS and JavaScript and how they do what they do on your webpage. Although everything will be explained along the way, some concepts are difficult to digest. Please feel free to contact me anytime and I will go into more detail about the how and why. I work on a Window machine running Python 3.6 and Django 2.0.

So Here We Go!

You need to start off with Python and Django already installed. This will keep the directory structure from being another level deeper than necessary. Your future self will thank you, because it is slightly better organized. Open a command prompt.

> pip install django
> django-admin startproject dashboards
> cd dashboards
> virtuanenv venv
> venv\scripts\activate
(venv) > pip install django
(venv) > python manage.py runserver

Success

In Django, an app is a reusable component can be used on multiple projects. The admin site is an example of an app. It comes pre installed in every new Django project. In the Django dashboard project, let's create an app called charts which will be responsible for all the reporting.

(venv) > django-admin startapp charts

A URL dispatcher associates functions with requests that come in. A request in this case is when a browser (client) asks a web server for a page (resource). Python functions are associated with URLs.

dashboards/urls.py

from django.urls import path, include

urlpatterns = [
    path('', include('charts.urls')),
    path('admin/', admin.site.urls),
]

charts/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.IndexView.as_view(), name='index'),
]

There are 2 URL dispatchers one for the whole project and one for the specific app. When the request first comes in, it goes to the project URL dispatcher. This has been configured to send all requests (except requests to the admin site (out of the scope of this tutorial)) to the app URL dispatcher. It is the job of the app URL dispatcher to associate requests with functions. Let's create a basic view function for now that we will add to later.

charts/views.py

from django.http import HttpResponse
from django.views import View

class IndexView(View):
    def get(self, request):
        return HttpResponse('Hello Views')

Templates

Including HTML in programming code makes it unpretty and unmanageable. The solution is to have an HTML into which you inject variables. The files stored separately away from the code. It is a way of implementing the MVC philosophy of web application development. It tries to separate out what the user interacts with (View), with what is in the database (Model) with what processing is done after retrieving it from the database (Controller).

Another important point is, If you want to get a web page with all the design work already thought of, a front end design framework can save you lots and lots of time and tears. As a standard, I use the Bootstrap framework just because there is such a good support community out there. Bootstrap changes the default formatting of the styles in your webpage so that all you have to do is just write standard HTML and it will give you a professional looking output.

Setting Up Templates

You need to tell Django where to look for your template files. It would also help if all the template files were in the same place.

Create a template directory in the charts folder to hold the files that will be sent as pages to the webserver. To get Django to know where to look for the templates, these need to be added to DIRS value in the settings.py file.

dashboard/settings.py

'DIRS': [os.path.join(BASE_DIR, 'charts/templates')],

Once this is done, you can directly render template files from code. Create an index.html file in the templates directory. Put in the starter bootstrap template at https://getbootstrap.com/docs/4.0/getting-started/introduction/

charts/views.py

from django.shortcuts import render
from django.views import View

class IndexView(View):
    def get(self, request):
        return render(request, 'index.html')

ChartJs

https://www.chartjs.org/ is a JavaScript charting library to implement high quality web based (SVG) charts. The charts are formatted for the web right out of the box. The library is loaded by including in the the <HEAD>...</HEAD> section of the HTML page.

<head>
  ...
  <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.2/Chart.min.js"></script>  
  ...
</head>

Create an API View that will use chart data

In order to make you dashboard modular, you need to separate out the data, the processing and the presentation (again back to the MVC pattern for development). You can process the data and bundle it up with the report but changing the design of the report and the format of the data are a completely different set of skills. As a project scales, this will eventually be handled by different people or teams. It is a good idea to think of this possibility early.

The way the solution will be built is by the report asking for data from a different location of the site. This solution separates the presentation (the report) from the final data set and how the application processes the data. ChartJS expects the data to be in a particular format. The format is in json which is one of the most popular ways servers talk to each other over the internet. We can create a Django view that returns data in the format expected by the chart. The data output is considered an interface to the data held by the application. This particular solution is known as an Application Programming Interface (API).

charts/views.py

from django.http import HttpResponse, JsonResponse
from django.views import View
import random

class APIViewPie(View):
    def get(self, request):
        values = [random.randint(1,100) for _ in range(3)]
        labels = ['Red Lorries', 'Yellow Lorries', 'Blue Lorries']
        label = "Distribution of Lorries"
        backgroundColor = ["rgb(255, 99, 132)","rgb(54, 162, 235)","rgb(255, 205, 86)"]
        data = {
            'datasets': [{
                'label': label,
                'data': values,
                'backgroundColor': backgroundColor,
            }],
            'labels': labels
        }
        return JsonResponse(data)

The view returns data for an arbitrary report, but also returns random numbers so different values are output when the report is refreshed.

The report is embedded into the HTML through JavaScript. This is the most popular way to embed graphics that have been generated by JavaScript into HTML.

<canvas id="bar" width="300" height="300"></canvas>
<script type="text/javascript">
  var endpoint = '/api/bar';
  $.ajax({
    method: "GET",
    url: endpoint,
    success: function(data){
      var ctx = document.getElementById("bar").getContext("2d");
      options = {title: {display: true,text: "Lorries per Month"},
      responsive: true,scales:{xAxes: [{stacked: true}],yAxes:
      [{stacked: true}]}}
      var myBarChart = new Chart(ctx, {
        type: 'bar',data: data,options: options,});},
    error: function(error_data){
      console.log("error");
      console.log(error_data);}});
</script>

This is a lot to unpack, but basically what it is doing is getting the data from a view and using it to create and embed a chart in HTML. The values for each of the slices are displayed when you hover your mouse over them. This is a major advantage of reports built for the web first. There are plenty of customisations that can be done to the charts. You can even go with your own colour scheme and explode some of the slices out of the pie.

pie

Where do we go from here?

This could be the beginning of something beautiful. Even though it is basic in its current form, It can be built upon to create a fully fledged application. Django is a mature framework and each of the problems you face, has been faced before and someone has probably built a solution around it. If you want to expand on this project consider the following. I plan to make these changes to the repository in the future.

Make the HTML more modular. This is pretty much a single page application where all the HTML code is bunched in one page. To help with future maintenance, all the pages should be broken into blocks and the common elements between pages should be separated out and inherited into all the locations they are used. For example, since the menu is on each page, making a change to it will mean updating multiple pages. If each page inherited a single menu, this would mean only one change and a smaller likelihood of errors happening.

Implement testing. Things fall apart, especially in the development world. Just because you have solved a problem now doesn't mean it will still be solved later. Just because something works right now doesn't mean it will work when you add more code. Implementing a proper testing strategy means as you inch closer to your objective, you can be confident that the work done in the past hasn't broken. There is a Test Driven Development (TDD) methodology that recommends writing tests before doing the actual coding.

Build more reports. There are a wide variety of reports to choose from and this is just one of many charting libraries out there. Getting good with one library means being able to transfer those skills to another library. It makes sense though to really master one library before exploring the others. Eventually you will settle and stick to one. It is simple to add a new chart to this application. Just create a new view and add a new chart to the main page.

Implement security. The report is visible to anyone who goes to the URL. Security will ensure only the right people are given access to the data. Django does both authentication and authorization out of the box. This means you can log a user in and give them access to different reports based on their role in the project.

Deploy. One of the primary objectives of this post was to propose a solution to distribute reports to multiple users. What we have created is just a solution that runs on your PC. There are many options available to you for deploying the solution to the web. This can vary from managing your own server to subscribing to a Platform as a Service (PaaS) provider. Each of these have related pros and cons and is a discussion in its self.

As I mentioned, all the code has been uploaded into the Github repository https://github.com/klameer/djangocharts. If you don't feel like typing any code, there are instructions to quickly install and run the project on your pc. You can use this as a starting point and make changes to it. If any of the things discussed here is unclear, please get in touch. Hopefully I can improve your understanding and you mine.