Demonstration of sales reporting with graph using integrated results files from a database
🧑💻 Tip: Check the sales report preparation note to configure your 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.");
// 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"
}),
});
user
, password
, database
) with your own values.lineChart()
or areaChart()
) for alternative visualizations.