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
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
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
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
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
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;
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
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