Here I am sharing my knowledge how I created a Sales KPI Dashboard with Metabase
- Start an AWS Instance and install Docker - Link
- Install Metabase by docker run -d -p 80:3000 --name metabase metabase/metabase (I installed on port 80 (http://example.com) and you have to make sure port 80 is opened on your AWS EC2 Management Console → Security Groups)
Now we have to enable MariaDB to accept remote connections.
- SSH to your server
-
sudo nano /etc/mysql/my.cnf and comment out the line by adding # bind-address = 127.0.0.1
- same like you opened the port 80 on your Metbase Docker server you need to open port 3306. Use Custom and enter the Metabase Docker public ip/32
- On the /etc/mysql/my.cnf file add your Reporting Time Zone. For me it is set to +4:00 for UAE. Otherwise you might get wrong date/time
- now we need to create a read only user on mysql server (same server where you SSHed) to pull the data to Metabase server.
CREATE USER ‘user_remote_R_only’@‘localhost’ IDENTIFIED BY ‘myStrongeSTpassword’;
CREATE USER ‘user_remote_R_only’@‘%’ IDENTIFIED BY ‘myStrongeSTpassword’;
GRANT SELECT ON . TO ‘user_remote_R_only’@‘localhost’;
GRANT SELECT ON . TO ‘user_remote_R_only’@‘%’;
flush privileges;
Restart the MYSQL Server sudo service mysql restart
Access the Metabase Dashboard and configure the sql connection
Thats all…
Below some SQL Queries I used to create the Dashboard
Total Invoice Count
SELECT count(*) AS count
FROM tabSales Invoice
WHERE tabSales Invoice
.docstatus
= 1
Average Sales Amount
SELECT avg(tabSales Invoice
.base_grand_total
) AS avg
FROM tabSales Invoice
WHERE tabSales Invoice
.docstatus
= 1
Total Revenue
SELECT sum(tabSales Invoice
.grand_total
) AS sum
FROM tabSales Invoice
WHERE tabSales Invoice
.docstatus
= 1
NET Profit
SELECT
#tabSerial No
.sales_invoice,
#tabDelivery Note
.name,
#SUM(tabSerial No
.purchase_rate) AS Purchase Rate
,
#SUM(tabDelivery Note Item
.rate) AS Sales Rate
,
#SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate) AS ‘Profit’,
(SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate))/ SUM(tabSerial No
.purchase_rate) * 100 AS ‘Margin’
FROM tabDelivery Note
INNER JOIN tabSerial No
ON tabDelivery Note
.name = tabSerial No
.delivery_document_no
INNER JOIN tabDelivery Note Item
ON tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note Item
.item_name = tabSerial No
.item_name
WHERE tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note
.docstatus = 1
Yesterday’s Invoice Count
SELECT count(*) AS count
FROM tabSales Invoice
WHERE (date(tabSales Invoice
.posting_date
) = date(date_add(now(), INTERVAL -1 day))
AND tabSales Invoice
.docstatus
= 1)
Yesterday’s Grand Total Sales
SELECT sum(tabSales Invoice
.grand_total
) AS sum
FROM tabSales Invoice
WHERE (date(tabSales Invoice
.posting_date
) = date(date_add(now(), INTERVAL -1 day))
AND tabSales Invoice
.docstatus
= 1)
Yesterday’s Total Profit in %
SELECT
#tabSerial No
.sales_invoice,
#tabDelivery Note
.name,
#SUM(tabSerial No
.purchase_rate) AS Purchase Rate
,
#SUM(tabDelivery Note Item
.rate) AS Sales Rate
,
#SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate) AS ‘Profit’,
(SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate))/ SUM(tabSerial No
.purchase_rate) * 100 AS ‘Margin’
FROM tabDelivery Note
INNER JOIN tabSerial No
ON tabDelivery Note
.name = tabSerial No
.delivery_document_no
INNER JOIN tabDelivery Note Item
ON tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note Item
.item_name = tabSerial No
.item_name
WHERE tabDelivery Note
.name = tabDelivery Note Item
.parent AND DATE(posting_date) = DATE(NOW() - INTERVAL 1 DAY) AND tabDelivery Note
.docstatus = 1;
Today’s Invoice Count
SELECT count(*) AS count
FROM tabSales Invoice
WHERE (tabSales Invoice
.docstatus
= 1
AND date(tabSales Invoice
.posting_date
) = date(now()))
Todays Profit in %
SELECT
#tabSerial No
.sales_invoice,
#tabDelivery Note
.name,
#SUM(tabSerial No
.purchase_rate) AS Purchase Rate
,
#SUM(tabDelivery Note Item
.rate) AS Sales Rate
,
#SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate) AS ‘Profit’,
(SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate))/ SUM(tabSerial No
.purchase_rate) * 100 AS ‘Margin’
FROM tabDelivery Note
INNER JOIN tabSerial No
ON tabDelivery Note
.name = tabSerial No
.delivery_document_no
INNER JOIN tabDelivery Note Item
ON tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note Item
.item_name = tabSerial No
.item_name
WHERE tabDelivery Note
.name = tabDelivery Note Item
.parent AND DATE(posting_date) = CURDATE() AND tabDelivery Note
.docstatus
= 1
Today’s Sales
SELECT sum(tabSales Invoice
.grand_total
) AS sum
FROM tabSales Invoice
WHERE (date(tabSales Invoice
.posting_date
) = date(now())
AND tabSales Invoice
.docstatus
= 1)
Profits Invoice Wise - Table View Output
SELECT DISTINCT
DATE_FORMAT(tabDelivery Note
.posting_date,“%d %M %Y”) AS ‘Date’,
tabSerial No
.sales_invoice AS ‘Invoice #’,
tabDelivery Note
.name AS ‘Delivery #’,
SUM(tabSerial No
.purchase_rate) AS Purchase Rate
,
SUM(tabDelivery Note Item
.rate) AS Sales Rate
,
SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate) AS ‘Profit’,
(SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate)) / SUM(tabSerial No
.purchase_rate) * 100 AS ‘Margin’
FROM tabDelivery Note
INNER JOIN tabSerial No
ON tabDelivery Note
.name = tabSerial No
.delivery_document_no
INNER JOIN tabDelivery Note Item
ON tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note Item
.item_name = tabSerial No
.item_name
WHERE tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note Item
.docstatus = 1
GROUP BY tabSerial No
.sales_invoice
ORDER BY tabSerial No
.sales_invoice DESC
Yesterday’s Profit Amount - Number Output
SELECT
#tabSerial No
.sales_invoice,
#'tabDelivery Note
.name,
#SUM(tabSerial No
.purchase_rate) AS Purchase Rate
,
#SUM(tabDelivery Note Item
.rate) AS Sales Rate
,
SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate) AS ‘Profit’
#(SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate))/ SUM(tabSerial No
.purchase_rate) * 100 AS ‘Margin’
FROM tabDelivery Note
INNER JOIN tabSerial No
ON tabDelivery Note
.name = tabSerial No
.delivery_document_no
INNER JOIN tabDelivery Note Item
ON tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note Item
.item_name = tabSerial No
.item_name
WHERE tabDelivery Note
.name = tabDelivery Note Item
.parent AND DATE(posting_date) = DATE(NOW() - INTERVAL 1 DAY) AND tabDelivery Note
.docstatus = 1;
Today’s Profit Amount
SELECT
#tabSerial No
.sales_invoice,
#tabDelivery Note
.name,
#SUM(tabSerial No
.purchase_rate) AS Purchase Rate
,
#SUM(tabDelivery Note Item
.rate) AS Sales Rate
,
SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate) AS ‘Profit’
#(SUM(tabDelivery Note Item
.rate) - SUM(tabSerial No
.purchase_rate))/ SUM(tabSerial No
.purchase_rate) * 100 AS ‘Margin’
FROM tabDelivery Note
INNER JOIN tabSerial No
ON tabDelivery Note
.name = tabSerial No
.delivery_document_no
INNER JOIN tabDelivery Note Item
ON tabDelivery Note
.name = tabDelivery Note Item
.parent AND tabDelivery Note Item
.item_name = tabSerial No
.item_name
WHERE tabDelivery Note
.name = tabDelivery Note Item
.parent AND DATE(posting_date) = CURDATE() AND tabDelivery Note
.docstatus = 1;
Please check my queries and correct me if it is wrong… Also contribute more queries here… Happy Dashboarding