NAV has detail at the LOT level. A lot can be in multiple bins and it is possible for the lot quantity to be correct but the BIN data incorrect. We can’t find any native functionality to display this data. Hence we would like to use Jet. All the data is in the Warehouse Entry Table. I need a unique row for each LOT/ZONE/BIN and sum the numeric fields as needed. Mostly weight or quantity.
Here is the SQL I wrote.
Select
distinct(concat(we.[Lot No_], we.[Item No_], we.[Zone Code], we.[Bin Code])) "Distinct Lots/Item/Zone/Bin"
, we.[Lot No_] , we.[Item No_] , we.[Zone Code] , we.[Bin Code]
, convert(numeric(10,4), sum(we.[Qty_ (Base)])) "QTY"
FROM dbo.[Test_2018-01-14$Warehouse Entry] we
GROUP BY we.[Lot No_] , we.[Item No_] , we.[Zone Code] , we.[Bin Code]
HAVING (Select
sum(we1.[Qty_ (Base)]) "QTY"
FROM dbo.[Test_2018-01-14$Warehouse Entry] we1
where we1.[Lot No_] = we.[Lot No_]
AND we1.[Item No_] = we.[Item No_]
AND we1.[Zone Code] = we.[Zone Code]
AND we1.[Bin Code] = we.[Bin Code]
) != 0
;
This produces the desired result but I would like it in Jet Professional so users can run it adhoc and on a nightly/weekly schedule.
I tried using and NL Row on the lot. I could make this a gigantic 3 level master/detail report and that may work, but the sum and the filter to only display records where the sum is <> 0 is very slow. Talking hours on about 1 milllion rows.
My sql takes less than a minute.
So I was going to try SQL= but Jet Support aritile (https://jetsupport.jetreports.com/hc/en-us/articles/219403077-Using-SQL-) says that SQL= is not available for NAV users.
What are my other options than using a different tool like SRS or Power BI?