Need a Sales Report that includes deposits (without tax)

rishi

I'm trying to find a way to create a report for "Gross Sales" which includes all payments received (whether deposits for orders or layaways, not just cash&carry sales) but pre-tax. Many landlords require this and consider any and all payments received, regardless of when the sale is completed or if it is, as "Gross Sales". Even if there is a workaround to get these numbers. The issue I'm running into is if I simple just combine the sales report in lightspeed with the report of open orders at the end of the month, and I do the same the following month, I would be running into duplicates if the prev. months open order totals turned into completed sales for the next month. I hope this makes sense. As an FYI, I also signed up to lightspeed accounting and quickbooks online. I haven't set it up yet, but if there's a way through the integration to achieve what I need, that could work also. Prev to transitioning to lightspeed, we manually tracked everything in a ledger from an old register.

Adrian Samuel

@rishi Hey Rishi, I'm going to bring in my colleague @josephmckeown here who might be able to speak more eloquently regarding the accounting integration.

Our initial questions are:

Do you have Lightspeed analytics?
Do you have any programming experience?

rishi

@Adrian Samuel
@josephmckeown

Hey! I don't have analytics and limited programming experience. What were you thinking in terms of a solution?

Adrian Samuel

@rishi, Joseph has a strong background in data and might be able to help create such a report in the custom report builder section of analytics.

I have background and experience with our Product's APIs and thus might be able to put forward some ideas as to how to produce such reports if you have a programming background

rishi

@Adrian Samuel
That would be great if @josephmckeown could do that for me. In the meantime Adrian, what were you thinking? Maybe I can put something together myself with your help and my limited ability to program?

Adrian Samuel

Hey @rishi

Here would be my programmatic method:


Go through the sale endpoint like so:

https://api.lightspeed.com/API/Account/{{accountID}}/Sale/{{saleID}}.json?load_relations=["SalePayments"]&completed=true;

As you can see in this GET/Read request the saleID is fixed so you'll need to loop through all the sales via their IDs throughout your given time range.


Already we're only receiving completed transactions and not those that our pending.

We need to apply a set of conditions in our application logic:

If the Sale.calcTotal is 0 then you know that the sale is only a credit payment (deposit for a layaway etc) so you can just check the SalePayments object for the SalePayments.SalePayment array and total only the positive numbers. Those numbers will be the total pre-tax credit amount (since credit payments have no tax applied to them).

If the Sale has a mix of transaction payments and credit payments, find the first array index in the SalePayments.SalePayment array for a given sale and multiply the .amount value by -1 to get a positive credit integer. That number that will be sum of credit payment values you’ve received for that sale and so already stands at a pre-tax value.

The sale.calcSubtotal of that same Sale object is the pre-tax total of items that was paid for.

Total all those numbers over your custom date range and you will have the data for your landlord

josephmckeown

Hi there Rishi, thank you for reaching out. Showing sales and deposits pre-tax will not available on one report as there are conflicting demands, which I can illustrate below. Taxes are only available on Sales reports, which do not include Deposits. Deposits are only available on Payments reports, which do not separate taxable-vs-non-taxable values.

Some easy solutions that exist in Retail are:

• Showing all Payments from the Payments report, then subtracting the taxes from the Sales report. (which I think gets you closest to the number that you are being asked to provide)
• Showing Sales only, before and after taxes are applied
• Showing all Payments and Deposits after taxes are applied

Showing Payment Methods pre-tax becomes a problem because of this situation:

Say that a customer buys a $100 jacket, on which there is $10 tax. The customer owes $110. They pay with $60 cash, and put the remaining $50 on their Credit Account. Where is the tax being collected?

Now we could possibly come up with a Payments report in Analytics, in which we used Calculations to re-create a pre-taxed value by payment method. There is an illustration of a similar solution in the Products by Payment Method article, for your consideration.

Thanks for the great question Rishi. Please let us know if we can help with anything further.