Inventory Value report Not working correctly?

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?

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.

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.

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.

This is what my report used to do:

Unfortunately the layout of tables has now changed to this:

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