How I created this Sales KPI Dashboard

Riyas_Rawther · January 29, 2018, 12:10pm

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)

port 802182×170 33.9 KB

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

conf916×562 74.3 KB
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

raghu9 · January 29, 2018, 2:30pm

Hello Riyas_Rawther, Thank you for this awesome guide.
Currently we are running our instance in Google Cloud. We followed every step of this guide , except port 80, because it was already in use, so we installed Metabase on port 12345.
But the Metabase page is not loading. Can you please help us with this?

kevingee · January 29, 2018, 2:52pm

Let’s see the error messages or a screenshot. Then we can help you

kolotayo · January 29, 2018, 6:36pm

@Riyas_Rawther thank you for sharing. I have successfully Connected my Dashboard will begin work on DashBoard Setup and see how it goes.

Thank You so much for sharing

raghu9 · January 30, 2018, 4:39am

here docker container running on 0.0.0.0:12345 , is this the problem?

kevingee · January 30, 2018, 9:07am

Metabase runs on port 3000. Try using that to connect.

kolotayo · January 30, 2018, 9:09am

i think this should work cos i am currently running it on my local instance using 127.0.0.1:3000 which works very fine but not Docker. This @kevingee suggestion should help

kolotayo · January 30, 2018, 9:14am

Dear @Riyas_Rawther,

I tried some of the script and below is what i got which makes perfect sense.

but you know it will be fun if i can use date filter to Filter also. Is there any option for Date Filter instead of using Yesterday and Today. I want to be able to see report for a Defined Period lets say like 1 Month, or 5 days by just selecting my date range on the Dashboard.

@kevingee and @Riyas_Rawther any suggestion.

saidsl · January 30, 2018, 12:10pm

@kolotayo

You can use the builtin functionality in Metabase to add a Date Variable for example:

Replace

AND DATE(posting_date) = CURDATE()

With

{{date}}

Then select posting date in the Variable Dialog box on the right side when it appears

You can also select the kind of date filtering you want

Then in the Dashboard you need to add the filter 56 pm

Then select the field you want as your date filter

37 pm

in this case the posting_date

29 pm

Hope this helps

kolotayo · January 30, 2018, 2:44pm

Thank You for your response @saidsl.

I got your point but i have spent over 20 minutes looking for where to edit the Variable… can you help with where to locate the Variable Functionality,

kolotayo · January 30, 2018, 2:50pm

SELECT
tabSales Invoice Item.barcode AS BarCode,
tabSales Invoice Item.item_name AS ItemName,
tabSales Invoice Item.item_group AS ItemGroup,
tabSales Invoice Item.uom AS Unit,
tabSales Invoice Item.qty AS InvcQty,
tabSales Invoice Item.rate AS Rate,
tabSales Invoice Item.amount AS Amount,
tabSales Invoice Item.warehouse AS Store,
tabSales Invoice Item.actual_qty AS StockQty,
tabSales Invoice Item.modified AS modified
FROM tabSales Invoice Item
WHERE date(tabSales Invoice Item.modified) IS NOT NULL
LIMIT 5000

Thats my script showing Invoice Item Summary, but i want to be able to filter by date like a specific period. Kindly shed more light on this .

Await your swift response

saidsl · January 30, 2018, 3:00pm

When you add {{date}} in the SQL Editor in metabase question the Variable box will appear on the right hand side or click on the 18 pm

kolotayo · January 30, 2018, 5:22pm

I was able to do this on the Editor but i can’t seem to use it on the my Dashboard

Riyas_Rawther · January 31, 2018, 1:41pm

After saving the Query/Result did you added to the dashboard?

kolotayo · February 1, 2018, 11:03am

yes i did add it to the dashboard. but below is what i am getting each time i filer with the date.

Using: SELECT
si.name, si.customer, sii.item_group, sii.item_name, sii.qty,sii.rate, sii.amount
FROM tabSales Invoice as si
INNER JOIN tabSales Invoice Item as sii ON si.name = sii.parent
WHERE {{date}}
LIMIT 5000

My Dashboard just grey out and the result is not changing.

saidsl · February 1, 2018, 2:43pm

It seems you added a pre-selected date when you were setting the variable.

You need to select “Date Filter” as per screen grab:

Then, you need to edit the dashboard

18 pm

Then click the “Edit Filter”

03 pm

And I think this is the part that you are missing is selecting the field you had defined when you set the filter up initially in the variable

Save the changes and click done

You should then be able to change the dates/ranges etc as per bekow

Hope this helps

kolotayo · February 1, 2018, 4:53pm

This makes more sense now but i can see the date filter inside Question Page but i cant see it on the Dashboard page. Below is what i have.

Any suggestion why that is so?

kevingee · February 1, 2018, 7:36pm

In the dropdown, do you see Time? Under it you have Date range, relative date… that is a clue.

I believe this guide provided by @saidsl is self-explanatory. Relax and go over it and all your questions will be answered.

saidsl · February 2, 2018, 1:39am

Select “Time” as the filter you want to and it will be added to the top left side of the dashboard.

This should help you further

kolotayo · February 2, 2018, 9:15am

I have added the Time Filter but if i add any i always have them but by Date Filter is not among the listed. i have tried all but still not giving me my date filter.

just like above