Stock Report With Unit Conversion and Cost Calculation

Version 4 V4 Custom Reports
1

Hope that works for everyone…

Report:

[Stock:2, 1, 1, 1]
>Item|Stock Fisico|Stock UNI|Costo
@@Stock
>TOTAL|||@@TotalStock

SQL @@Stock:

SELECT [InventoryItemName],
      ([InStock]+[Added]-[Removed]-[Consumption]) as StockFisico,
	  CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) as StockFisicoUN,
      CONVERT(numeric(6,2),([Cost]*([InStock]+[Added]-[Removed]-[Consumption]))) as Costo
      
FROM [PeriodicConsumptionItems]

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
      AND
	  CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) >= 1

ORDER BY [InventoryItemId]

SQL @@TotalStock:

SELECT CONVERT(numeric(6,2),SUM(([Cost]*([InStock]+[Added]-[Removed]-[Consumption])))) as Costo
      
FROM [PeriodicConsumptionItems]

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
      AND
	  CONVERT(INT,([InStock]+[Added]-[Removed]-[Consumption])*[UnitMultiplier]) >= 1
6 Likes