Try ERPNext Buy Support Partners Foundation Foundation Members

How I created this Sales KPI Dashboard

Below is what i got… Or could it be the Date and Time Format?

Good Morning @saidsl,

I think i have a little issue, If i join to tables together and i try to use a Variable based on one of the table, i get an error message whenever i try to run the report.

And the Dashboard link you sent was very helpful, i was able to do my Filter but the only issue is just filter that involves Joining.

Any help with that

@kolotayo

I have used the filter/s on table joins, it should not matter actually.

If you want to paste your sequel, I can have a look at it.

Regards
Said

1 Like

SELECT
si.name, si.customer, sii.item_group,
#TO_CHAR(si.posting_date, ‘DD-MM-YYYY’) AS “POST_DATE”,
si.posting_date, 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

Above is my script and i used Posting_date as filter

1 Like

@kolotayo

The issue you are having is that you have not wrapped the table names with `.

Try this:

SELECT
si.name, si.customer, sii.item_group,
#TO_CHAR(si.posting_date, ‘DD-MM-YYYY’) AS “POST_DATE”,
si.posting_date, 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 

Regards
Said

I have tried this and it keeps giving me error.

on the Sql Editor i am getting “Unknown Column ‘tabSales Invoice Item.modified’ in ‘where clause’” Which the field is very present in the table.

can you advise on what i am not getting right

You are have to be using the back tick and not the single quotes.

I have run your exact select statement as per the one version I sent you and it works fine.

Copy paste what I have sent and it should run fine.

In the case of the error:

it should be

`tabSales Invoice Item`.`modified`

Back ticks enclosing the Table name and the Column name

1 Like

Thank You @saidsl as this works perfectly fine

Welcome @kolotayo

1 Like

Dear @saidsl,

I am trying to restrict user from seeing specific dashboard.

is this possible?

@kolotayo

Unfortunately, thats not possible. straight off.

Some options to do so would be to embed the Dashboard into a page that only a set a users can view as Metabase has Public Share, Public Embed or use the Embed into an application.

I suggest you explore which one would suit you most.

Hope this helps

1 Like

i will check the two options and get back to you

Thank you for your support

Dear @Saidsl,

I am currently stock on a report i am looking for on metabase.

I want to be able to see the Stock in each of my warehouses e.g LA have 10qty of an item while CA has 24qty of that same item. But currently i am using total_projected_qty which gives me a total sum of 34qty and this is not helping my report. any help on how i can make this happen. i need to filter by warehouse to know what each warehouse are having

Await your response.

Hi Michael
Please make the end result on excel sheet and post the screenshot. Based on
that someone can post the query for you…

Riyas Rawther
IT Manager
I20 FZE & Digital Link Trading L.L.C
The Dubai Airport Free Zone,
Dubai, UAE

Phone: 971509756011, 971527824444
Skype: riyas.rawther
Email: riyas@i20dubai.com
URL: www.i20dubai.com

-sent from mobile-

1 Like

@kolotayo

Try and use Metabase’s builtin Query Builder and apply as per screen grab below

But basically, you need to group by warehouses to get the amount of each warehouse.

Play with the query builder as it will help you better understand how to achieve various reports. (easily)

1 Like

Thank You @saidsl. I was able to get for Sales of Item but i am looking for Inventory Position (like total No of Items left in a Store). As Below;

Item/Store Store A Store B Total

Item A 20 10 30
Item B 5 41 46
Item C - 10 10

The Issue i am facing currently is that on the Item Table, there is no column for Warehouse and the only quantity i could see is Total_Projected_Qty

oH

The above image is what i am talking about.

The Issue i am facing currently is that on the Item Table, there is no column for Warehouse and the only quantity i could see is Total_Projected_Qty

Thank You @saidsl. I was able to get for Sales of Item but i am looking for Inventory Position (like total No of Items left in a Store). As Below;

oH

The above image is what i am talking about.

The Issue i am facing currently is that on the Item Table, there is no column for Warehouse and the only quantity i could see is Total_Projected_Qty

@kolotayo

Try and use the tabStock Ledger Entry as per screen below also try and filter by warehouse as well.

Note, Metabase does not provide the functionality to get totals, you will need to take the sql generated by Metabase and apply the totals manually

1 Like

Point Taken. But let me share this screenshot;

Under Stock Qty i have the quantity(Total_projected_qty) of each item here but i want to add a filter (Warehouse). Below is my script;

SELECTtabItem.barcode AS barcode, tabItem.item_name AS item_name, tabItem.item_group AS item_group, (tabItem Price.price_list_rate) AS ‘Rate’,
(tabItem.total_projected_qty) AS ‘Stock Qty’, (tabItem Price.price_list_rate *tabItem.total_projected_qty) AS ‘Valuation’
FROM tabItem
INNER JOIN tabItem Price ON tabItem.item_code = tabItem Price.item_code
WHERE {{Category}}
GROUP BY tabItem.barcode, tabItem.item_name, tabItem.item_group
ORDER BY tabItem.barcode ASC, tabItem.item_name ASC, tabItem.item_group ASC

The Quantity there is the Total for all Stores but i want to merge a table to achieve that or is there any Qty that works as Store on Hand Qty apart from Total_projected_qty