Anual Consumption for Inventory Items

gerlandog · October 31, 2014, 6:10pm

This is the pivot report that I made with a strange SQL… I simplified it to work with Custom Reports Pivot Tables…

This report will give you a list of inventory items and a 12 column report month by month quantities.

It is nicer than the actual SQL, becuase it can be collapsed, so if you have more than one year you can compare totals by year…

[!Consumos MES a MES:3, 1, 1, 1]
>ITEM|Mes|Ano|[N]Consumo
@@ConsumosAnuales

ConsumosAnuales Script: @@ConsumosAnuales
SELECT [InventoryItemName],
	  FORMAT(DATEPART(Month,[StartDate]),'D2')+'-'+
	  DATENAME(Month,[StartDate]) as MES, 
	  DATEPART(Year,[StartDate]) as ANO,
      CONVERT(INT,(CONVERT(numeric(6,2),[Consumption])*[UnitMultiplier])) as Consumo

FROM [PeriodicConsumptionItems]

LEFT OUTER JOIN [PeriodicConsumptions] on [PeriodicConsumptionId] = [PeriodicConsumptions].[Id]

and the exported database toosl file:
ConsumosAnuales.zip (1.5 KB)

Actual Screen for Report:

Thanks!!!

G.