cancel
Showing results for 
Search instead for 
Did you mean: 

Summary sales report in SAP Business One to show number and amount of sales by US state

0 Kudos

In the USA those who sell products online now have to collect sales tax from customers in the states where product is shipped. It is called Economic Nexus. Many have a threshold or $100,000 in sales to that state OR 200 or more transactions in that state. How can I rund a report in SAP Business One for a 12 month period to show me each US state and the total value and number of sales transactions made BY STATE so we can determine whether we have economic nexus in each state.

Accepted Solutions (0)

Answers (3)

Answers (3)

zal_parchem2
Active Contributor
0 Kudos

Hello Paul - the first thing you have to define is "Sales". We all have Amazon to thank for this. &;-D

Do you consider "Sales" to be only AR Invoices - OR - Do you consider "Sales" to be AR Invoices and AR Credit Memos??? If you are considering AR Credit Memos, then Aziz' SQL can be changed from OINV to ORIN.

Check this out as exmple for Invoices and Credit Memos -

https://wiki.scn.sap.com/wiki/display/B1/SAP+B1+SQL+D-SL+AR+Inv+and+CM+List+by+Gross+Profit

Do you consider Item Dollars, Freight Dollars, Tax Dollars as "Sales"??? Services Type Documents also???

Whenever I did SQL for Nexus, we had to define it different ways (depending upon the state and the type of "presence" you have there - as you probably well know). All of the reports I did were at the low level on INV1 and RIN1.

Check this out as example for Invoices and Credit Memos by Sales Person (depends upon your definition of "Nexus" as to how this can be modified -

https://wiki.scn.sap.com/wiki/display/B1/SAP+B1+SQL+D-SL+AR+Invoices+and+Credit+Memos+by+Sales+Perso...

Hope Abdul and Aziz helped you, but just checking to make certain all is well...

Regards - Zal

Abdul
Active Contributor
0 Kudos

User query recommend by aziz just put an condition to remove canceled transactions condition will be where canceled = 'N'

azizelmir
Contributor
0 Kudos

Hi Paul,

Here below the requested query.

Select T1.ShiptoCode as 'Ship to', Count(T1.Docentry) as 'Number of Sales Transactions', Sum(DocTotal) as 'Total Value' From OINV T1 Group By ShiptoCode

Hope this helps!

Aziz