This article is aimed at those who know SQL and want to quickly set up a minimalist reporting solution (without going through the heavy artillery of PowerBI, Tableau, Looker Studio, etc.).
Let’s start with a database (of the Mysql, Mariadb or Postgresql type) including a monthly_sales table containing monthly sales amounts:
| month | sales_amount_eur |
|---|---|
2024-01-01 |
8730 |
2024-02-01 |
9620 |
2024-03-01 |
4210 |
2024-04-01 |
6732 |
2024-05-01 |
9921 |
2024-06-01 |
8176 |
2024-07-01 |
7623 |
Our aim is to:
- Generate a report showing this information in a graph.
- Refresh it every 1st of the month.
1 - Installation
Let’s start by installing CTFreak.
Under Ubuntu, open your terminal and run:
sudo snap install ctfreak
For alternative installations (Docker, Windows, Freebsd, …), see here.
2 - Login
Go to http://localhost:6700 and log in via admin / ctfreak.
3 - Adding a database
Go to Databases → New Database, select its type (e.g. Postgresql), then fill in its connection parameters:
Validate to add the database.
4 - Creating a project
A project will group together all our reporting tasks.
Go to Projects → New Project:
Validate to create the project.
5 - Creating a SQL Report task
Our report will be generated via a SQL Report task associated with our project.
To do this, go to Projects → Reporting → New Task, select SQL Report as the task type and then fill in the following information:
In the SQL query, the suffix _c_month allows you to indicate that date values should be formatted as months in the graph.
Validate this form to create a task which, based on an SQL query, will generate a monthly sales report every 1st of the month at 9 a.m., with a 2 year (730 days) retention period for the reports generated.
Let’s execute this task right now (via Projects → Reporting → Sales report -> Execute) to get the expected report:
Conclusion
As you can see, this report meets our initial requirements.
Feel free to use CTFreak’s other features to make improvements, such as:
- Add more charts.
- Add task parameters to define the sales period to be taken into account.
- Add read-only access for a given user (via the Viewer role).
- Send an e-mail each time a report is generated, with a link to open it (via a Notifier).
- Synchronize the generation of multiple reports (via Workflow tasks).