...
Build a Data Dashboard Using HTML, CSS, and JavaScript

Build a Data Dashboard Using HTML, CSS, and JavaScript

by


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:

  • name
  • version
  • description
  • main
  • scripts
  • author
  • license

Here is what my package file looked like.

{
  "name": "my-dashboard",
  "version": "1.0.0",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "dependencies": {
    "express": "^4.21.2",
    "sqlite3": "^5.1.7"
  }
}

4/ Install Express and SQLite

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  element.{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}

2) client/style.css{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}

/* client/style.css */
body {
    background-color: #f8f9fa;
    font-family: 'Arial', sans-serif;
}

h1 {
    text-align: center; /* Center the heading */
    margin-top: 20px; /* Add spacing above the heading */
    margin-bottom: 40px; /* Add spacing below the heading */
}

.container .filters {
    margin-top: 20px;
    margin-bottom: 60px !important; /* Ensure larger spacing between filters and Key Metrics */
}

.container #key-metrics {
    margin-top: 40px !important; /* Additional spacing above the Key Metrics section */
    margin-bottom: 20px; /* Optional spacing below */
}

.key-metrics div {
    margin: 10px 0;
    padding: 10px;
    background-color: #f4f4f4;
    border: 1px solid #ccc;
    border-radius: 4px;
}

/* Fix for DataTables Pagination Spacing */
.dataTables_wrapper .dataTables_paginate {
    text-align: center;
    margin-top: 10px;
}

.dataTables_wrapper .dataTables_paginate .paginate_button {
    margin: 0 12px;
    padding: 5px 10px;
    border: 1px solid #ddd;
    border-radius: 4px;
    background-color: #f9f9f9;
    color: #007bff;
    text-decoration: none;
    display: inline-block;
}

.dataTables_wrapper .dataTables_paginate .paginate_button:hover {
    background-color: #007bff;
    color: #fff;
    border: 1px solid #007bff;
}

.dataTables_wrapper .dataTables_paginate .paginate_button.current {
    font-weight: bold;
    color: #fff;
    background-color: #007bff;
    border-color: #007bff;
}{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}

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. {{{{ESC_TAG}}}}

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.{{{{ESC_TAG}}}}

3) server/server.js{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}

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}`);
});{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}

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:{{{{ESC_TAG}}}}

     
  1. Serves static files (like HTML, CSS, and JS) from the client subfolder so the frontend loads in the browser.{{{{ESC_TAG}}}}

  2. Provides a /data{{{{ESC_TAG}}}} 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.{{{{ESC_TAG}}}}
{{{{ESC_TAG}}}}

    4) client/script.js{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}

    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 = "
    No data available.{{{{ESC_TAG}}}}";
                }
                return;
            }

            // Initialize filters and dashboard content
            setupFilters(data);
            initializeDashboard(data);

            // Re-render charts when chart type changes
            document.getElementById('chart-type-selector').onchange = () => filterAndRenderData(data);
        })
        .catch((error) => {
            // Handle fetch error
            console.error('Error fetching data:', error);
            const app = document.getElementById('app');
            if (app) {
                app.innerHTML = "
    Failed to fetch data.{{{{ESC_TAG}}}}";
            }
        });
});

// 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  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));
    });
}{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}

    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 …{{{{ESC_TAG}}}}

    1/ Fetches sales data{{{{ESC_TAG}}}}{{{{ESC_TAG}}}}