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
Sales Performance Dashboard
Key Metrics
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.
Bootstrap is used for styling. Flatpickr is used for date inputs. Chart.js is used for visualisations, and DataTables is used for tabular display. Interactivity is handled by an external script.js file, which we’ll examine shortly.
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.
DataTables is a jQuery-based plugin that enhances standard HTML
elements, transforming them into fully interactive, feature-rich tables.
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.
Chart.js is a simple yet powerful JavaScript library for creating interactive, animated charts in web applications using the
We use a cascading style sheet (CSS) to style the basic visual components of our dashboard, for example, button and text colours, spacing between elements, etc.
The style.css file gives the dashboard its appearance and overall look. It’s a clean, light theme with ample spacing and layout adjustments for clarity and readability. The style.css file also customises the appearance of DataTables’ pagination buttons, making them more user-friendly and visually consistent with Bootstrap’s design.
3) server/server.js
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const app = express();
const PORT = 3000;
// Full path to your SQLite database
const DB_PATH = "C:\\Users\\thoma\\projects\\my-dashboard\\sales_data.db";
// Serve static files from the client directory
app.use(express.static(path.join(__dirname, '..', 'client')));
// Route to fetch data from SQLite database
app.get('/data', (req, res) => {
const db = new sqlite3.Database(DB_PATH, sqlite3.OPEN_READONLY, (err) => {
if (err) {
console.error("Error connecting to database:", err.message);
res.status(500).json({ error: "Database connection failed" });
return;
}
});
// Query the database
const query = "SELECT * FROM sales;"; // Replace 'sales' with your table name
db.all(query, [], (err, rows) => {
if (err) {
console.error("Error running query:", err.message);
res.status(500).json({ error: "Query failed" });
} else {
res.json(rows); // Send the query result as JSON
}
});
db.close((err) => {
if (err) {
console.error("Error closing database:", err.message);
}
});
});
// Catch-all route to serve the main HTML file
app.get('*', (req, res) => {
res.sendFile(path.join(__dirname, '..', 'client', 'index.html'));
});
// Start the server
app.listen(PORT, () => {
console.log(`Server running at http://localhost:${PORT}`);
});
This Node.js script contains the JavaScript code that sets up a basic Express server that powers the Sales Performance Dashboard. It does two main things:
Serves static files (like HTML, CSS, and JS) from the client subfolder so the frontend loads in the browser.
Provides a /data endpoint that reads from a local SQLite database (sales_data.db) and returns the entire sales table as JSON, enabling dynamic data visualisations and tables on the frontend.
4) client/script.js
let chartInstance = null; // Global variable to store the current Chart.js instance
// Wait until the DOM is fully loaded
document.addEventListener('DOMContentLoaded', function () {
// Fetch sales data from the backend API
fetch('/data')
.then((response) => response.json())
.then((data) => {
// Handle case where no data is returned
if (!data || data.length === 0) {
const app = document.getElementById('app');
if (app) {
app.innerHTML = "
";
}
});
});
// Initialize Flatpickr date pickers and category filter
function setupFilters(data) {
// Convert date strings to JS Date objects
const dates = data.map((item) => new Date(item.order_date.split('/').reverse().join('-')));
const minDate = new Date(Math.min(...dates));
const maxDate = new Date(Math.max(...dates));
// Configure start date picker
flatpickr("#start-date", {
defaultDate: minDate.toISOString().slice(0, 10),
dateFormat: "Y-m-d",
altInput: true,
altFormat: "F j, Y",
onChange: function () {
filterAndRenderData(data);
},
});
// Configure end date picker
flatpickr("#end-date", {
defaultDate: maxDate.toISOString().slice(0, 10),
dateFormat: "Y-m-d",
altInput: true,
altFormat: "F j, Y",
onChange: function () {
filterAndRenderData(data);
},
});
// Set up category dropdown change listener
const categoryFilter = document.getElementById('category-filter');
if (categoryFilter) {
categoryFilter.onchange = () => filterAndRenderData(data);
}
}
// Initialize dashboard after filters are set
function initializeDashboard(data) {
populateCategoryFilter(data); // Populate category dropdown
filterAndRenderData(data); // Initial render with all data
}
// Apply filters and update key metrics, chart, and table
function filterAndRenderData(data) {
const chartType = document.getElementById('chart-type-selector').value;
const startDate = document.getElementById('start-date')._flatpickr.selectedDates[0];
const endDate = document.getElementById('end-date')._flatpickr.selectedDates[0];
const selectedCategory = document.getElementById('category-filter').value;
// Filter data by date and category
const filteredData = data.filter((item) => {
const itemDate = new Date(item.order_date.split('/').reverse().join('-'));
return (
itemDate >= startDate &&
itemDate <= endDate &&
(selectedCategory === 'all' || item.categories === selectedCategory)
);
});
updateKeyMetrics(filteredData); // Update metrics like revenue and orders
drawChart(filteredData, 'chart-canvas', chartType); // Render chart
populateDataTable(filteredData); // Update table
}
// Update dashboard metrics (total revenue, order count, etc.)
function updateKeyMetrics(data) {
const totalRevenue = data.reduce((acc, item) => acc + parseFloat(item.total), 0);
const totalOrders = data.length;
const averageOrderValue = totalOrders > 0 ? totalRevenue / totalOrders : 0;
// Calculate total revenue per category to find top category
const revenueByCategory = data.reduce((acc, item) => {
const category = item.categories || "Uncategorized";
accProgramming = (accProgramming || 0) + parseFloat(item.total);
return acc;
}, {});
// Determine category with highest total revenue
const topCategory = Object.keys(revenueByCategory).reduce(
(a, b) => (revenueByCategory[a] > revenueByCategory[b] ? a : b),
"None"
);
// Display metrics in the DOM
document.getElementById('total-revenue').textContent = `$${totalRevenue.toFixed(2)}`;
document.getElementById('total-orders').textContent = `${totalOrders}`;
document.getElementById('average-order-value').textContent = `$${averageOrderValue.toFixed(2)}`;
document.getElementById('top-category').textContent = topCategory || 'None';
}
// Draw the selected chart type using Chart.js
function drawChart(data, elementId, chartType) {
const ctx = document.getElementById(elementId).getContext('2d');
// Destroy previous chart if one exists
if (chartInstance) {
chartInstance.destroy();
}
switch (chartType) {
case 'revenueOverTime':
// Line chart showing revenue by order date
chartInstance = new Chart(ctx, {
type: 'line',
data: {
labels: data.map((item) => item.order_date),
datasets: [{
label: 'Revenue Over Time',
data: data.map((item) => parseFloat(item.total)),
fill: false,
borderColor: 'rgb(75, 192, 192)',
tension: 0.1,
}],
},
options: {
scales: {
y: { beginAtZero: true },
},
},
});
break;
case 'revenueByCategory':
// Bar chart showing total revenue per category
const categories = [...new Set(data.map((item) => item.categories))];
const revenueByCategory = categories.map((category) => {
return {
category,
revenue: data
.filter((item) => item.categories === category)
.reduce((acc, item) => acc + parseFloat(item.total), 0),
};
});
chartInstance = new Chart(ctx, {
type: 'bar',
data: {
labels: revenueByCategory.map((item) => item.category),
datasets: [{
label: 'Revenue by Category',
data: revenueByCategory.map((item) => item.revenue),
backgroundColor: 'rgba(255, 99, 132, 0.2)',
borderColor: 'rgba(255, 99, 132, 1)',
borderWidth: 1,
}],
},
options: {
scales: {
y: { beginAtZero: true },
},
},
});
break;
case 'topProducts':
// Horizontal bar chart showing top 10 products by revenue
const productRevenue = data.reduce((acc, item) => {
const productName = item.product_names || 'Unknown Product';
acc[productName] = (acc[productName] || 0) + parseFloat(item.total);
return acc;
}, {});
const topProducts = Object.entries(productRevenue)
.sort((a, b) => b[1] - a[1])
.slice(0, 10);
chartInstance = new Chart(ctx, {
type: 'bar',
data: {
labels: topProducts.map((item) => item[0]), // Product names
datasets: [{
label: 'Top Products by Revenue',
data: topProducts.map((item) => item[1]), // Revenue
backgroundColor: 'rgba(54, 162, 235, 0.8)',
borderColor: 'rgba(54, 162, 235, 1)',
borderWidth: 1,
}],
},
options: {
indexAxis: 'y', // Horizontal bars
scales: {
x: { beginAtZero: true },
},
},
});
break;
}
}
// Display filtered data in a DataTable
function populateDataTable(data) {
const tableElement = $('#data-table');
// Destroy existing table if it exists
if ($.fn.DataTable.isDataTable(tableElement)) {
tableElement.DataTable().clear().destroy();
}
// Create a new DataTable with relevant columns
tableElement.DataTable({
data: data.map((item) => [
item.order_id,
item.order_date,
item.customer_id,
item.product_names,
item.categories,
`$${parseFloat(item.total).toFixed(2)}`,
]),
columns: [
{ title: "Order ID" },
{ title: "Order Date" },
{ title: "Customer ID" },
{ title: "Product" },
{ title: "Category" },
{ title: "Total" },
],
});
}
// Populate the category filter dropdown with available categories
function populateCategoryFilter(data) {
const categoryFilter = document.getElementById('category-filter');
categoryFilter.innerHTML = '';
categoryFilter.appendChild(new Option('All Categories', 'all', true, true));
// Extract unique categories
const categories = new Set(data.map((item) => item.categories));
categories.forEach((category) => {
categoryFilter.appendChild(new Option(category, category));
});
}
It’s our most complicated code file, but it has to do a lot. This JavaScript file powers the interactivity and data visualisation for the Sales Performance Dashboard. In short, it …
1/ Fetches sales data
When the page loads (DOMContentLoaded), it calls a backend API at the /data endpoint.
If no data is returned, a “No data available” message is displayed.
2/ Sets up filters
Uses Flatpickr date pickers to choose a start and end date based on the dataset’s min/max order dates.
Adds a category dropdown, allowing users to filter by product category.
Adds a chart type selector to switch between different chart visualisations.
3/ Initialises the dashboard
Populates the category filter with available categories.
Runs the first render with the full dataset.
4/ Applies filters and re-renders
Each time the user changes a filter (date range, category, or chart type), it:
Filters the dataset by date range and category.
Updates key metrics: total revenue, number of orders, average order value, and top revenue category.
Redraws the selected Chart.js chart.
Refreshes the data table.
5/ Draws charts with Chart.js
Revenue Over Time → Line chart showing revenue trends by date.
Revenue by Category → Bar chart aggregating total revenue per category.
Top Products → Horizontal bar chart showing the top 10 products by revenue.
6/ Displays tabular data
Uses DataTables (a jQuery plugin) to render a table of filtered orders, with columns for order ID, date, customer ID, product, category, and total.
7/ Keeps the UI in sync
Destroys and recreates charts/tables when filters change to avoid duplicates.
Keeps metrics, charts, and tables consistent with the active filters.
Running our dashboard
Now that we have all our code sorted, it’s time to run the dashboard, so go to the server subfolder and type in the following command.
$ node server.js
You’ll get a response to the above command, something like,
Server running at http://localhost:3000
Open a web browser and visit http://localhost:3000. You should see your dashboard populated with data from the SQLite database, as shown in the image below.
Image by Author
All the filters, chart selection, etc, should work as advertised.
Summary
In this article, I’ve walked you through creating a fully functional, interactive sales performance dashboard using core web technologies—HTML, CSS, JavaScript, Node.js, Express, and a local SQLite database.
I showed you how to create and populate a SQLite database in code that we could use as the source data for our dashboard. We also discussed the environment setup and both the front-end and back-end development processes, and briefly touched on our data dashboard functionality.
Finally, I walked you through and explained in detail the four code files we needed to create, and then showed you how to run the dashboard in a browser.
Towards Data Science is a community publication. Submit your insights to reach our global audience and earn through the TDS Author Payment Program.
Write for TDS
Related Articles
Three common Python tricks make your program faster, I will explain the mechanisms
8 min read
Our weekly selection of must-read Editors’ Picks and original features
Let’s travel 8 years back in time
Recreating lessons learned from Cole Nussbaumer Knaflic’s book in Python using Matplotlib
Use Python Logging like a Pro
Tutorial on how to use WebSockets to build real-time APIs in Go
We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.AcceptPrivacy Policy
