Inventory Value report Not working correctly?


#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:

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