Problem with Stock Report With Unit Conversion and Cost Calculation Custom Report

Version 4
1 
[quote="gerlandog, post:1, topic:2360, full:true"]
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



[/quote]

if i run the above query in sql studio i am getting nothing.

is there any prerequiste for this ?

2

It displays positive inventory.

3

but in my case it showing nothing although i have inventory data

4

Can you show us exact sql script you’re executing instead of the quote?

Pasted image1705×1090 45.1 KB

5 
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]
6

I removed that part to see negative amounts.

7

Pasted image1024×768 155 KB

still nothing

8

Can you show your inventory from warehouse screen?

9

Pasted image1024×768 50.3 KB

10

You have nothing In Stock @madiha :wink: That query shows it.

11

but normally what i am purchasing is my stock right… and in stock reflects the purchase value after ending work period ?

12

For what you want try this:

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 [WarehouseConsumptionId] FROM [PeriodicConsumptionItems] ORDER BY [WarehouseConsumptionId] DESC)

    
    ORDER BY [InventoryItemId]
13

still no result…

14

Show your PeriodicConsumptionItems table.

15

the table is empty…

16

This query reads data from PeriodicConsumptionItems table and data inserted into this table when you end work period. It does not shows real time inventory.

3 Likes
17

@madiha I am not trying to be mean but your questions often have hidden meaning that we find later in conversation. If you would take the time to explain your need fully from beginning we can avoid that confusion.

2 Likes
18

i am glad that you figure it out actually this problem exists since school time :slight_smile: