dashboard for your customers, clients, or fellow workers is becoming an essential part of the skill set required by software developers, data scientists, ML practitioners, and data engineers. Even if you work primarily on back-end processing, the data you’re processing usually needs to be “surfaced” to users at some point. If you’re lucky, your organisation may have a dedicated front-end team to take care of that, but often it will be down to you.
Being a straight-up Python developer with no experience in HTML, JavaScript, etc., is no longer an excuse, as many Python libraries, such as Streamlit and Gradio, have emerged over the last few years.
This article is not about them, though, because I am one of those straight-up Python developers, and I’ve already done the Streamlit and Gradio thing. So it was time to roll up my sleeves and see if I could learn new skills and create a dashboard with those old front-end development stalwarts: HTML, JavaScript, and CSS.
The data for our dashboard will come from a local SQLite database. I created a sales_data table in SQLite containing dummy sales data. Here is the data in tabular form.
Image by Author
Below is some code that you can use to follow along and create your own SQLite database and table with the data as shown.
In case you’re wondering why I’m only inserting a handful of records into my database, it’s not because I don’t think the code can handle large data volumes. It’s just that I wanted to concentrate on the dashboard functionality rather than being distracted by the data. Feel free to use the script I provide below to add additional records to the input data set if you like.
So, we stay in the Python world for just a bit longer as we set up a SQLite DB programmatically.
import sqlite3
# Define the database name
DATABASE_NAME = "C:\\Users\\thoma\\projects\\my-dashboard\\sales_data.db"
# Connect to SQLite database
conn = sqlite3.connect(DATABASE_NAME)
# Create a cursor object
cursor = conn.cursor()
# SQL to create the 'sales' table
create_table_query = '''
CREATE TABLE IF NOT EXISTS sales (
order_id INTEGER PRIMARY KEY,
order_date TEXT,
customer_id INTEGER,
customer_name TEXT,
product_id INTEGER,
product_names TEXT,
categories TEXT,
quantity INTEGER,
price REAL,
total REAL
);
'''
# Execute the query to create the table
cursor.execute(create_table_query)
# Sample data to insert into the 'sales' table
sample_data = [
(1, "2022-08-01", 245, "Customer_884", 201, "Smartphone", "Electronics", 3, 90.02, 270.06),
(2, "2022-02-19", 701, "Customer_1672", 205, "Printer", "Electronics", 6, 12.74, 76.44),
(3, "2017-01-01", 184, "Customer_21720", 208, "Notebook", "Stationery", 8, 48.35, 386.80),
(4, "2013-03-09", 275, "Customer_23770", 200, "Laptop", "Electronics", 3, 74.85, 224.55),
(5, "2022-04-23", 960, "Customer_23790", 210, "Cabinet", "Office", 6, 53.77, 322.62),
(6, "2019-07-10", 197, "Customer_25587", 202, "Desk", "Office", 3, 47.17, 141.51),
(7, "2014-11-12", 510, "Customer_6912", 204, "Monitor", "Electronics", 5, 22.5, 112.5),
(8, "2016-07-12", 150, "Customer_17761", 200, "Laptop", "Electronics", 9, 49.33, 443.97)
]
# SQL to insert data into the 'sales' table
insert_data_query = '''
INSERT INTO sales (order_id, order_date, customer_id, customer_name, product_id, product_names, categories, quantity, price, total)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
# Insert the sample data
cursor.executemany(insert_data_query, sample_data)
# Commit the transaction
conn.commit()
# Close the connection
conn.close()
print(f"Database '{DATABASE_NAME}' has been created and populated successfully.")
Dashboard Functionality
Our dashboard will have the following functionality.
Key Metrics. Total revenue, total orders, average order value, top category
Different Chart Types. Revenue Over Time (line chart), Revenue by Category (bar chart), Top Products by Revenue (horizontal bar chart)
Filtering. By date and category
Data Table. Display our data records in a paginated and searchable grid format.
Setting up our Environment
Next, we have a series of steps to follow to set up our environment.
1/ Install Node.js.
Node.js is a runtime environment that enables you to run JavaScript outside the browser, allowing you to use JavaScript to build fast and scalable server-side applications.
So, ensure Node.js is installed on your system to enable you to run a local server and manage packages. You can download it from the Node.js official website.
2/ Create a main project folder and subfolders
Open your command terminal and run the following commands. I’m using Ubuntu on my Windows box for this, but you can change it to suit your preferred command-line utility and system.
$ mkdir my-dashboard
$ cd my-dashboard
$ mkdir client
% mkdir server
3/ Initialise a Node project
$ npm init -y
This command automatically creates a default package.json file in your project directory without requiring user input.
The -y flag answers “yes” to all prompts, using the default values for fields like:
SQLite is a lightweight, file-based relational database engine that stores all your data in a single, portable file, eliminating the need for a separate server.
Express is a minimal, flexible web application framework for Node.js that simplifies the building of APIs and web servers through routing and middleware.
We can install both using the command below.
$ npm install express sqlite3
Now, we can start developing our code. For this project, we’ll need four code files: an index.html file, a server.js file, a client.js file, and a script.js file.
Let’s go through each of them step by step.
1) client/index.html
Key Metrics
{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}
This HTML file establishes the basic visual elements of our Sales Performance Dashboard, including interactive filters for date and category, a section displaying key sales metrics, a dropdown menu to select chart types, and a table for raw data. {{{{ESC_TAG}}}}
Bootstrap {{{{ESC_TAG}}}}is used for styling. Flatpickr {{{{ESC_TAG}}}}is used for date inputs. Chart.js{{{{ESC_TAG}}}} is used for visualisations, and DataTables {{{{ESC_TAG}}}}is used for tabular display. Interactivity is handled by an external script.js{{{{ESC_TAG}}}} file, which we’ll examine shortly.{{{{ESC_TAG}}}}
Bootstrap is a popular front-end framework, initially developed by Twitter, that helps you build responsive and visually consistent web interfaces more easily and quickly.{{{{ESC_TAG}}}}
DataTables is a jQuery-based plugin that enhances standard HTML {{{{ESC_TAG}}}}
elements, transforming them into fully interactive, feature-rich tables.{{{{ESC_TAG}}}}
Flatpickr is a lightweight, customizable JavaScript date and time picker. It lets users select dates (and optionally times) from a sleek pop-up calendar instead of typing them manually.{{{{ESC_TAG}}}}
Chart.js is a simple yet powerful JavaScript library for creating interactive, animated charts in web applications using the