Anual Consumption of Inventory Items

I ussualy like to see during the year wich product do sell better… so In my case this report is usefull. Hpe this would be usefull for others too!!!

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

Please note that the SQL has a CASE:

   CASE WHEN [UnitName] <> 'UN' THEN
  END as Consumo

This is because I recently changed UNITS to avoid decimal of bottles and pizzas… but I imagine that you can just use:

CONVERT(INT,[Consumption]) if your meassure unit is UNITS
CONVERT(INT,(CONVERT(numeric(6,2),[Consumption])*[UnitMultiplier])) if your meassure units is something else.

[Consumos MES a MES:3,1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]

SELECT [InventoryItemName], [UnitName],

FROM (SELECT [InventoryItemName], [UnitName],
	  DATENAME(Month,[StartDate]) as MES, DATEPART(Year,[StartDate]) as ANO,
      CASE WHEN [UnitName] <> 'UN' THEN
      END as Consumo
  FROM [PeriodicConsumptionItems]
  LEFT OUTER JOIN [PeriodicConsumptions] on [WarehouseConsumptionId] = [PeriodicConsumptions].[Id]
      FOR MES in ([January],[February],[March],[April],[May],[June],


  ORDER BY [InventoryItemName]



PD: Dear @emre, could you tell me why the last column is bigger that the rest, even tho all are 1?


1 Like

Just the sort of a report that i was looking for - i’m facing an error in this though, any idea what can be causing the stuff below:

try this in your SQL!!!


Working now! Thanks! Used this command :stuck_out_tongue:

FROM (SELECT [InventoryItemName], [UnitName],
DATENAME(Month,[StartDate]) as MES, DATEPART(Year,[StartDate]) as ANO,
CASE WHEN [UnitName] <> ‘UN’ THEN
END as Consumo

If you look close… you will see that the CASE...END is not necesay…

Just use

[Consumption] in your case…


1 Like

This report isn’t working correctly in 4.1.65. Is anyone else also facing the same issue?

you have to change these line:

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

for this line:

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

as from version number:


1 Like

Thanks, this report isn’t working as well. Can you please tell me what i should do differently?


This should fix it:

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [WarehouseConsumptions] ORDER BY [Id] DESC)

Full query:

SELECT [InventoryItemName],
     [Consumption] as StockFisico
FROM [PeriodicConsumptionItems]
WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [WarehouseConsumptions] ORDER BY [Id] DESC)
ORDER BY [InventoryItemId]

More accurate because it is constrained by last Closed WorkPeriod

    wp.[Id] as [WPID]
--, wp.[StartDate] as [WPBeg]
--, wp.[EndDate] as [WPEnd]
--, pci.[Id] as [pciID]
--, pc.[Id] as [pcID]
--, pci.[PeriodicConsumptionId] [pcIDbad]
--, wc.[Id] as [wcID]
--, pci.[WarehouseConsumptionId] as [wcIDbad]
--, wc.[WarehouseId] as [whID]
, wh.[Name] as [Warehouse]
, ii.[GroupCode] as [ItemType]
, ii.[Name] as [Item]
, ii.[TransactionUnit]
, CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as [QLarge]
, pci.[UnitMultiplier] as [Multiplier]
, ii.[BaseUnit]
, CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier])) as [QSmall]

FROM [PeriodicConsumptionItems] pci
left join [PeriodicConsumptions] pc on pc.[Id]=pci.[PeriodicConsumptionId]
left join [WarehouseConsumptions] wc on wc.[PeriodicConsumptionId]=pc.[Id]
left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]
left join [Warehouses] wh on wh.[Id]=wc.[WarehouseId]
left join [WorkPeriods] wp on wp.[Id]=pc.[WorkPeriodId]

-- ensure we are pulling data for latest CLOSED WorkPeriod
    AND wp.[Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
    AND  pci.[WarehouseConsumptionId]=wc.[Id]

Sample Output:


Thanks but this code isn’t working either :(. Any idea why?

[quote]SELECT [InventoryItemName],
[Consumption] as StockFisico
FROM [PeriodicConsumptionItems]
WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [WarehouseConsumptions] ORDER BY [Id] DESC)
ORDER BY [InventoryItemId][/quote]

i’m getting this error:

[quote] -----------------------------
[General Info]

Application: SambaPOS
Version: 4.1.65
Region: en
Machine: AVM-PC
User: AVM
Date: 10/7/2014
Time: 11:05 PM

User Explanation:

AVM said “”

[Exception Info 1]

Top-level Exception
Type: System.Data.SqlServerCe.SqlCeException
Message: There was an error parsing the query. [ Token line number = 7,Token line offset = 35,Token in error = SELECT ]
Source: SQL Server Compact ADO.NET Data Provider
Stack Trace: at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Samba.Infrastructure.Data.SqlData.EFWorkspace.ExecSqlQuery(String sqlScript, String parameters)
at Samba.Addon.CustomReports.SqlUpdater.UpdateSqlTags(String line)
at Samba.Addon.CustomReports.CustomReportBuilder.AddRow(String row)
at Samba.Addon.CustomReports.CustomReportBuilder.ProcessLine(String line)
at Samba.Addon.CustomReports.CustomReportBuilder.Build(SimpleReport sp)
at Samba.Addon.CustomReports.CustomReportViewerViewModel.CreateCustomReport(String reportName, SimpleReport simpleReport, Boolean addHeader)
at Samba.Addon.CustomReports.CustomReportViewerViewModel.GetReport()
at Samba.Presentation.Common.Reports.AbstractReport.b__2()

[Assembly Info]

Samba.Services, Version=
mscorlib, Version=
System, Version=
Microsoft.Practices.Prism, Version=
WindowsBase, Version=
Samba.Infrastructure, Version=
Samba.Infrastructure.Data, Version=
PresentationFramework, Version=
System.Xaml, Version=
Samba.Presentation.Services, Version=
FluentValidation, Version=
Samba.Domain, Version=
DevExpress.Xpf.Grid.v14.1, Version=
DevExpress.Xpf.Grid.v14.1.Core, Version=
PresentationCore, Version=
Stateless, Version=
System.Core, Version=
System.Drawing, Version=
System.Windows.Forms, Version=
System.ComponentModel.Composition, Version=
Samba.Localization, Version=
Microsoft.CSharp, Version=
Microsoft.Practices.ServiceLocation, Version=
Samba.Persistance, Version=
Microsoft.Practices.Prism.MefExtensions, Version=
DevExpress.Xpf.Core.v14.1, Version=
PropertyTools, Version=2012.4.14.1

[System Info]

Operating System
-Microsoft Windows 7 Ultimate
–CodeSet = 1252
–CSDVersion = Service Pack 1
–CurrentTimeZone = 300
–FreePhysicalMemory = 878108
–OSArchitecture = 32-bit
–OSLanguage = 1033
–ServicePackMajorVersion = 1
–ServicePackMinorVersion = 0
–Version = 6.1.7601

–Manufacturer = LENOVO
–Model = 4186
–TotalPhysicalMemory = 3215564800
–UserName = AVM-PC\AVM


Try running this in SQL first just to test if its real or not. And then try it in custom reports. Other factors can cause Custom Reports to crash in Samba.

i’ve never used SQL outside of SambaPos. Which program do i use for running this code :slight_smile: ?

I just tested it and the code is good. Can you show your Custom Report to see how you set it up?

EDIT: never mind your getting a select error.

That code was good too…

This is how its set up.

Now show your script and are you setup to use SQL?

SELECT [InventoryItemName],

 [Consumption] as Stock

FROM [PeriodicConsumptionItems]

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [WarehouseConsumptions] ORDER BY [Id] DESC)

ORDER BY [InventoryItemId][/quote]

That is valid code. So something else is the problem. What version of SambaPOS and do you know for sure its running SQL? Double click the SambaPOS logo in upper left to enter windows mode and look at top of window… does it say SQ at end?

nope it doesn’t … changed to a single user database and had been playing around with that. Silly me. This must’ve been caused it. Thanks!

Yep that is SQL scripting it will not work for yours. There is really no reason to use a single user database. If you can run SQL then you should use SQL its just better and does not slow down your computer or take up too much space.

1 Like

If your database is SQL Server Compact that database engine does not support arbitrary values. Try this one .

SELECT [InventoryItemName],
     [Consumption] as Stock
FROM [PeriodicConsumptionItems]
WHERE [WarehouseConsumptionId] in (SELECT TOP 1 [Id] FROM [WarehouseConsumptions] ORDER BY [Id] DESC)
ORDER BY [InventoryItemId]
1 Like