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!!!
G
Working now! Thanks! Used this command 
FROM (SELECT [InventoryItemName], [UnitName],
DATENAME(Month,[StartDate]) as MES, DATEPART(Year,[StartDate]) as ANO,
CASE WHEN [UnitName] <> ‘UN’ THEN
CONVERT(INT,[Consumption])
ELSE
CONVERT(INT,[Consumption])
END as Consumo
If you look close… you will see that the CASE...END is not necesay…
Just use
[Consumption] in your case…
g.
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]
) as SOURCE_TABLE
for this line:
LEFT OUTER JOIN [PeriodicConsumptions] on [PeriodicConsumptionId] = [PeriodicConsumptions].[Id]
) as SOURCE_TABLE
as from version number:
G.
1 Like
Thanks, this report isn’t working as well. Can you please tell me what i should do differently?
SQL
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…
SELECT
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]
WHERE 1=1
-- 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:
2 Likes
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
DB: CE
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=1.0.0.0
mscorlib, Version=4.0.0.0
System, Version=4.0.0.0
Microsoft.Practices.Prism, Version=4.0.0.0
WindowsBase, Version=4.0.0.0
Samba.Infrastructure, Version=1.0.0.0
Samba.Infrastructure.Data, Version=1.0.0.0
PresentationFramework, Version=4.0.0.0
System.Xaml, Version=4.0.0.0
Samba.Presentation.Services, Version=1.0.0.0
FluentValidation, Version=3.4.0.0
Samba.Domain, Version=1.0.0.0
DevExpress.Xpf.Grid.v14.1, Version=14.1.6.0
DevExpress.Xpf.Grid.v14.1.Core, Version=14.1.6.0
PresentationCore, Version=4.0.0.0
Stateless, Version=1.0.0.0
System.Core, Version=4.0.0.0
System.Drawing, Version=4.0.0.0
System.Windows.Forms, Version=4.0.0.0
System.ComponentModel.Composition, Version=4.0.0.0
Samba.Localization, Version=1.0.0.0
Microsoft.CSharp, Version=4.0.0.0
Microsoft.Practices.ServiceLocation, Version=1.0.0.0
Samba.Persistance, Version=1.0.0.0
Microsoft.Practices.Prism.MefExtensions, Version=4.0.0.0
DevExpress.Xpf.Core.v14.1, Version=14.1.6.0
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
Machine
-AVM-PC
–Manufacturer = LENOVO
–Model = 4186
–TotalPhysicalMemory = 3215564800
–UserName = AVM-PC\AVM
[/quote]
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 
?
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?
[quote]
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
Took me a bit to pick that out… changed ‘=’ to ‘in’ … sneaky