My client brought this to my attention. When you add up the quantities of the products sold the ones that we looked at (Top) doesn’t add correctly. We are trying to understand why this would be.
Savanna Dry 330ml (6 pack) sold 611 QTY x 150 (Selling Price) = 91650 (On report = 77850)
Corona 355ml (6 pack) sold 432 QTY x 160 (Selling Price) = 69120 (On report = 35200)
The database is SQL server express 2019. Database size without compression 7.8GB. They have been using SambaPOS 5 since 2018. The ticket records in the DB table = 561000+ and the orders records in the DB table = 1111541.
Can this be a result of too big DB? I upgraded them about 1 month ago to SambaPOS 5.3.6. They have 6 departments.
Hookah bar, (1 x Terminal)
Bottle bar, (1 x Terminal)
Bar, (10 x Terminal)
Kitchen, (3 x Terminal)
KDisplay (Kitchen displays), - 2 x Terminals (1 x Kitchen, 1 x Customer display)
VIP Customers (6 x Terminals)
Network is on gigabit network.
We cannot really determine when the calculations have not been accurate, they have only recently picked up this problem.
Any one have an idea of where to look?
Database size and the network wouldn’t affect anything like this.
If that’s the default Item Sales Report then the totals in the right most column are from the orders table, not any of the price definitions. The price in the orders table is set by the price of the menu item on the ticket.
My guess is some prices on the ticket aren’t what they should be.
If you’re not using any custom price definitions, run this script in SSMS and it will give you a list of orders where the ticket price is less than the defined price.
o.Price AS SalePrice,
mipr.Price AS OriginalPrice,
FROM dbo.Orders o
INNER JOIN dbo.Tickets t
ON t.Id = o.TicketId
INNER JOIN dbo.MenuItems mi
ON mi.Id = o.MenuItemId
INNER JOIN dbo.MenuItemPortions mip
ON mip.Name = o.PortionName
AND mip.MenuItemId = o.MenuItemId
INNER JOIN dbo.MenuItemPrices mipr
ON mip.Id = mipr.MenuItemPortionId
WHERE o.Price < mipr.Price
ORDER BY o.TicketId;
This is the result for the query you asked. If I put the WHERE BETWEEN in, it only looks for the last workperiod and nothing fishy came up.
If I run it against the whole of December, I can see where they made price changes, but that was normal as the prices did increase.
Well, there goes that idea. Hmm.
see how this matches up to the reports total:
DECLARE @Start DATETIME = '2021-12-25 14:23';
DECLARE @End DATETIME = '2021-12-26 13:17';
SUM(Price * Quantity)
BETWEEN @Start AND @End
GROUP BY MenuItemName
ORDER BY MenuItemName;
Well, price per quantity match in SQL.
Is the report you’re using the default Item Sales Report or is it custom-made?
I saw that when I add the line WHERE … AND DecreaseInventory = 1 then the Orders are closer to the actual. I see that not all Orders has DecreaseInventory and I’m not sure why.
Default Sale Report that I’m using
But now I’m also worried that the Work Period report might also have a similar issue. They customer uses this for cash ups.
Good catch on the DecreaseInventory.
Well shoot. I’m out of ideas. Not knowing how the backend calculates the order’s ExactTotal I don’t know what else to check.
As for the work period report, I’ve never audited the totals. I’ve always just trusted them. Now you have me wondering.
How was the report prior to upgrading to 5.3.6?
Report prior to 5.3.6? I cannot tell you now. I have a few older DB backups but to get the SambaPOS 5 version that worked with that DB version would be a mission.
Strange thing I noticed is that my other departments, Kitchen and Hookah calculates correctly. Kitchen works with weighed meat and chicken wings that they BBQ for the clients. The reason I mention this is that the sale report only shows whole amounts, not ex. 109,8991 or whatever this is. So the kitchen sales is always a few cents out because of the whole number.
I’m trying to build a custom report now for this but I’m totally lost with custom reports.
Hopefully someone else can give more clarity and in the mean time I just want to get more accurate data.
Thank you for the help so far. Your queries was a big help as I did all of the queries one by one and then tried to compare.
I’m still running 5.3.0 if you wanted to PM a db backup, I could run the report to compare.
Loaded the db into 5.3.0 and this is the result from the last full work period:
Restore the same db you sent me into 5.3.6 and then execute
DELETE FROM dbo.WorkPeriods WHERE Id = 3650 in SSMS and then rerun Item Sales Report. Let’s see what it shows on your end now that we’re working with the same data.
I have to go water the dog and will check back in a bit.
Cool thanks, I’ll check on my side. Just having lunch with family now. Will update in a bit. Thank you so much.
Any discounts etc would also affect it. Make sure ODI = True is set in your report syntax.
The reports are accurate but you need to understand what it is doing. Gifted items, or discounted, price changes, etc will all be reflected.
Thank you @Jesse. Just checked now and I do see a lot of Gift transactions in the workperiod. Do you have a SQL script that I can run to see who these users was?
There is not a sql script for that but you can use a new feature in 5.3.6 to see which admin user executed the gift command button.
Look up the report ticket details and AC. Field Syntax. Use report viewer in sambapos to see what is available to use.
@Jesse is there a better tutorial than the one in the kb to use the report viewer? Not sure where to find the report viewer…