Reports - Unable to divide by a non-whole number


#1

Where I live I have a state sales tax of 6.875% (yes .875). I also have a local sales tax of .50% for a total of 7.375%. Nearly all our sales include Sales Tax (7.375%) in the price, only a few sales do we add Sales Tax to the order.

I’m trying to create a report that will figure the pre-sales tax amounts, on the totals, that I can use to enter in our accounting software. I’ve notice that SambaPOS won’t calculate division on non-whole numbers.
image

[Sales Tax Defaut Numbers:4, 2, 2, 2, 1, 1]
>>Item|Qty|Ttl $|Tax|Calc
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum,O.ExactTotal.Sum.desc,O.Tax.Sum,=[O.ExactTotal.Sum]-[O.Tax.Sum]:(MG=Admissions)}

[Sales Divided by 2:4, 2, 2, 2, 1, 1]
>>Item|Qty|Ttl $|Tax|Calc
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum,O.ExactTotal.Sum.desc,O.Tax.Sum,=[T.TotalAmount.Sum]/2:(MG=Admissions)}

[Sales Divided by 2.9:4, 2, 2, 2, 1, 1]
>>Item|Qty|Ttl $|Tax|Calc
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum,O.ExactTotal.Sum.desc,O.Tax.Sum,=[T.TotalAmount.Sum]/2.9:(MG=Admissions)}

[Sale Tax Divided by 1.07375:4, 2, 2, 2, 1, 1]
>>Item|Qty|Ttl $|Tax|Calc
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum,O.ExactTotal.Sum.desc,O.Tax.Sum,=[T.TotalAmount.Sum]/1.07375:(MG=Admissions)}

As you can see in this sample report, if I divide by 2, it displays the correct amount. If I divide by 2.9 it displays the results as if it was 2.

My desired results is to get the Total and divide by 1.07375 (to get price before Sales Tax). I’m not sure if I have the syntax wrong, that maybe the issue???

I know part of the problem is SambaPOS Sales tax only allows for 2 decimal places. I’ve done a forum search to allow 3 decimal places in the Sales Tax. But everything I try, reverts back to 2 decimal places in the Tax Template. Even if I could get the decimal places to 3 places, rounding the Sales Tax in each individual sale by be off by .01. Multiply that how many are sold, it may through throw my numbers off. Getting the Pre-Sales Tax Total Amount, in Reports and then letting the accounting software figure out the sales tax portion would be, what I think, would be the easiest for me.

Unless, someone smarter has a better idea.


#2

How are you adding tax? Via Tax template?


#3

Yes, Tax Templates. 2 of them, one for the 6.875% and the other for .50%. I have the 6.875% set at 6.87%.


#4

Did you try O.Price? Or just do O.Price - O.Tax if you need to.

Heck just try T.TotalAmount.sum - O.Tax.Sum or T.Tax.sum


#5

That is not true. In the tax template itself you can set the decimal places and in reports you can format it with F functions to list more decimals.


#6

Actually that syntax you used works. What version are you running?

EDIT: NM no it doesnt.


#7

Thanks for you help. The =[O.Price.Sum]-[T.Tax.Sum] results in -.90.

But if you try to put in 3 decimal places, save, close the Tax Template tab, then reopen the template it displays 2 decimal paces.

I know having sales tax included doesn’t result in even numbers. For example: An item I sell for is $3.00
$3.00 divided by 1.07375 equals $2.7939464… ($2.79 rounded actual price) Say we sell 30 of them, that would be a total, pre-sales tax of $83.70.
Take the same $3 item multiply by 30 equals $90. Divide $90 by 1.07375 equals 83.82. That’s why I’m interested in the total amount.


#8

Running Version 5.2.14


#9

The reason its not working is because Decimal is protected its obviously used in the syntax for fields. So we need to find something that works.

Can you show a screenshot of that?


#10

Works fine for me.


#11

Did you save, close the Tax Template Tab? Then reopen the template?


#12

Yes I did that was after saving it. I am running latest version 5.2.18


#13

Ok, that may be the problem. I will try updating. That will get me closer. I’m still faced with this problem:

I’m trying to use the formula without decimals. According to the calculator this should work. But the formula isn’t displaying the results. I may have the syntax wrong:
(=[T.TotalAmount.Sum]*100000)/107375


#14

Why won’t subtracting T.Tax from total work?


#15

I’m trying to keep it even money. I don’t want to make alot of adjustments in the accounting program. I understand your point of view about using the actual tax amount collected for the sale and using that number.

I just updated to 5.2.18. It still isn’t saving 3 decimal places. In your screen shot did you close the 2nd tab, “Tax Templates” that is open (next to the Price Definitions)?


#16

Yes and it saved just fine. Maybe it’s your database. Go to program settings and maintenance then set decimals there.


#17

If it’s the decimal messing up syntax maybe try bracketing the number.
/(2.9)
Mathmatically it’s unnesercery but may help with expression parsing.


#18

I must be missing something somewhere else. I tried changing the decimal points in Settings -> Programs -> Maintenance. Tried 3 and 4. Tried Rebuilding database and Refresh Tax Transactions. I just can’t get 3 decimal places to stick in the Tax Template. I’m currently looking at the database using MSSMS.

JTRTeckh, I tried using (), no go. I even tried “” & ‘’ inside and outside the (). I also tried {} & [].


#19

Please restart SambaPOS after changing decimal places to 4.


#20

I tried restarting SambaPOS after each decimal change. I also restarted then took this screen shot of MSSMS of the Tax Template table:


Here is Menu Price table:

The 4 decimal places took for prices, but not the Tax Template.

I also made sure I tabbed out of the field of the Tax Template before saving.