Inventory Value report Not working correctly?

V5 Question
1

Hi All

Concerning the Inventory value report at this tutorial:

It is showing me the incorrect information, here is a picture:

Here is the code for the report:

[Inventory Cost Report:5, 3, 2, 2, 2]
>Item|Unit|Inv|Cost|Total
{REPORT CONSUMPTION DETAILS:C.Name,C.Unit,C.Inventory, C.Cost, ([C.Inventory]*[C.Cost])}
Totals||{REPORT CONSUMPTION DETAILS:C.Inventory.sum, C.Cost.sum, [([C.Cost]*[C.Inventory]).sum]}

Can anybody help me solve these errors?

2

Cost is an Average over time; it is not exact based on latest purchase.

End of Day records are for correcting count of stock and may not reflect Cost until a new WP is opened.

3

I Understand, but this is giving me the incorrect value.

Mathematically this is how it works:

Inventory Edit*Cost(0)+GRV*Cost=Total cost
(20*0=)0+(10*10=)100=100
Total Cost/Total Inventory=Cost
100/30=3.33

But it should be doing this

    GRV Costs/Amount Of GRV's Done=Average Cost
    10/1=10
    Inventory*Average Cost=Inventory Value
    30*10=30

Is there a way to this on the report?

I had an sql report that did this on SambaPOS3 but the database changed since then.

4

Hi all

Sorry this is rather important, we can’t have such incorrect stock values on our reports, and i can not seem to get it to work the way it did on V3.

5

This is what my report used to do:

v3transactions.png1351×314 24.4 KB

Unfortunately the layout of tables has now changed to this:

v5transactions.png1211×96 5.34 KB

Meaning that my sql:

With Temp As 
(SELECT InventoryItems.Name,AVG(InventoryTransactions.Price) as 'Average Cost',
PeriodicConsumptionItems.PhysicalInventory as 'In Stock',
AVG(InventoryTransactions.Price) *PeriodicConsumptionItems.PhysicalInventory  AS 'Inventory Stock Average Value'
FROM InventoryItems
left join InventoryTransactions on InventoryItems.Id=InventoryTransactions.InventoryItem_Id 
left join PeriodicConsumptionItems on InventoryItems.Id=PeriodicConsumptionItems.InventoryItemId 
where PhysicalInventory>0 
and PeriodicConsumptionItems.WarehouseConsumptionId=
(SELECT TOP 1 PeriodicConsumptions.Id 
FROM PeriodicConsumptions
ORDER BY PeriodicConsumptions.Id  DESC)
Group by InventoryItems.Id,PeriodicConsumptionItems.PhysicalInventory,InventoryItems.Name)
SELECT InventoryItems.Name,AVG(InventoryTransactions.Price) as 'Average Cost',
PeriodicConsumptionItems.PhysicalInventory as 'In Stock',
AVG(InventoryTransactions.Price) *PeriodicConsumptionItems.PhysicalInventory  AS 'Inventory Stock Average Value'
FROM InventoryItems
left join InventoryTransactions on InventoryItems.Id=InventoryTransactions.InventoryItem_Id 
left join PeriodicConsumptionItems on InventoryItems.Id=PeriodicConsumptionItems.InventoryItemId 
where PhysicalInventory>0 
and PeriodicConsumptionItems.WarehouseConsumptionId=
(SELECT TOP 1 PeriodicConsumptions.Id 
FROM PeriodicConsumptions
ORDER BY PeriodicConsumptions.Id  DESC)
Group by InventoryItems.Id,PeriodicConsumptionItems.PhysicalInventory,InventoryItems.Name

Does not work on the new version of sambapos