Znote (recipes)
  Get Znote  

📊 Sales Report

Demonstration of sales reporting with graph using integrated results files from a database

 

📊 Sales Report

🧑‍💻 Tip: Check the sales report preparation note to configure your database.

📌 Total Sales Amount by Channel

📥 Fetch Data from Database

SELECT 
  channel,
  DATE_FORMAT(sale_date, '%Y-%m') AS month,
  SUM(price) AS total_sales_amount,
  COUNT(*) AS total_sales
FROM 
  Sales
GROUP BY 
  channel, month
ORDER BY month ASC;
// Fetch sales data from the SQL database and save it as a JSON file
const sqlQuery = loadBlock("orders");
const salesData = await getSQL(sqlQuery, "127.0.0.1", 3306, "mysql", "user", "password", "database");

// Save data locally
const filePath = __dirname + "/orders-channel.json";
_fs.writeFileSync(filePath, JSON.stringify(salesData));

print("Sales data successfully saved.");

📊 Visualizing Sales Data

// Load the JSON file and visualize the data in a bar chart
const json = require("./orders-channel.json");

barChart({
  horizontal: true,
  series: toSeries({
    data: json,
    x: "month",
    y: "total_sales_amount",
    category: "channel"
  }),
});

🔧 Customization Tips

  • 🏦 Replace database credentials (user, password, database) with your own values.
  • 📅 Modify the SQL query to include additional filters, such as specific date ranges.
  • 📈 Try different chart types (e.g., lineChart() or areaChart()) for alternative visualizations.

Related recipes