We need to create a sales report by Ship To Address that shows the State we shipped to and dollar amounts shipped, to deal with the the hot mess that is the current sales tax situation.
Thanks!
Query AR_InvoiceHistoryHeader (and if you need line details, link to AR_InvoiceHistoryDetail using InvoiceNo and HeaderSeqNo).
Thanks for the quick answer. Can I get this to work in BIE?
I don't know BIE that well... perhaps someone else has that answer for you.
I'd go with Excel, and a pivot table.
Greetings,
You should be able to add the Ship To State to the Shipping History Invoices View in BIE and then save that view as a setting. Once added, the Ship to State can be filtered.
To do this, open the Shipping History Invoices View from the Sales Order Explore Menu. Then go to Edit - Column Settings. Click Add in Column Settings and Expand the AR_INvoiceHistoerHeader Data Source to see all the columns. Scroll down the columns until you find the ShipToState and double click it to add it as a selected column in Column Settings (should have a check mark in the column list). Click OK in Column Settings.
Scroll the view to the right to find the Ship To State column then click the column heading and drag it to wherever you want it in the view. Once you have the view the way you want, save it as a public or private view depending on who should have access by going to File - Save Setting.
If modifying the existing BIE view(s) does not work for you, you would need to work with a Sage 100 Development Partner to have them create a custom BIE view.
hth,
Kent Mackall
Thank you so much, Kent! That was perfect, got exactly what we needed!!!
That's going to work for states with a simple sales tax. The issue may be where there is a state/city type more complex tax.
Not try to calculate sales tax Wayne, (that will be next project to tackle) just determine where we stand in relationship to the state by state thresholds, thank you South Dakota vs. Wayfair :-(
The simplest way is to use the A/R Invoice History Report. You can choose bill to or ship to.
*Community Hub is the new name for Sage City