web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / WA Sales and B & O...
Microsoft Dynamics GP (Archived)

WA Sales and B & O Tax

(0) ShareShare
ReportReport
Posted on by Bullsai

Dilemma

My client needs a tax report that tracks four elements:

  1. In-State (WA) Sales
  2. Out-of-State Sales
  3. Wholesale Sales
  4. Retail Sales

The Tax Period Report (Reports-->Company-->Taxes) does great for showing the various WA State taxes, which I've setup in Tax Schedules; however, it obviously has no way to differentiate between In-State/Out-of-State and Wholesale/Retail sales.

I'm sure someone must've done this before.  How did you track these various items and then report back on them?  I thought about setting up two new Tax Details (Wholesale/Retail), but that doesn't seem like it would work either.

I've been playing around with building an SSRS report, but have not been overly successful.

Any ideas would be much appreciated.

Kind regards,

Todd Bowlsby

*This post is locked for comments

I have the same question (0)
  • Richard Schultz Profile Picture
    Richard Schultz 1,085 on at

     Bullsai,

     I'd think that Crystal (or SSRS) would work nicely to put this together.  If you have SQL 2005, I'd stick with Crystal (much better interface) but I've heard good things about the SSRS UI in SQL 2008.

    It sounds like the issue is the logic surrounding differentiation of the 4 types of sales.  If so, here are some suggestions:

    The RM00101 table has a STATE field - use that field to determine if a customer's invoice is in Washington or not.  To simplify the reporting logic, perhaps you might create a SQL view with the following SELECT statement:

      SELECT
       RM00101.STATE,
       Case
         When RM00101.STATE = 'WA' then 'In-state'
         Else 'Out-of-state'
         End LOCATION
      FROM RM00101

    You could link the view on the STATE field to your RM20101/RM30101 to define an invoice as in-state or out-of-state.

    That's the easy one.  The wholesale/retail is tougher.  How do you determine, manually, if a given invoice is a wholesale or retail sale?  Many clients will sell wholesale to one set of customers, and retail to another set.  If that's your setup, then you can use a udf on the RM00101 table to differentiate your customers between "wholesale" and "retail".  It will mean some data entry, but it might get you where you need to be.

    Finally, if you're putting all this in one report, I think you might be best off with a Cross tab like this:

                         Wholesale    Retail
    In-state       |
    Out-of-state |

    unless you can be sure that each of your four categories is mutually-exclusive.

    I look forward to hearing how it goes!

     

  • Bullsai Profile Picture
    Bullsai on at

    Richard:

    Thanks for the reply; I appreciate it.

    What I ended up doing to resolve this was to create four new Tax Details and essentially three new Tax Schedules as follows:

    Tax Details

    • INSTATE - In-State Retail Sales (0% tax calculation)
    • WHOLESALE - In-State Wholesale Sales (0% tax calculation)
    • OUTRETAIL - Out-of-State Retail Sales (0% tax calculation)
    • OUTWHOLESALE - Out-of-State Wholesale Sales (0% tax calculation)

    Tax Schedules

    • Added INSTATE Tax Detail to existing Tax Schedules
    • Added WHOLESALE Tax Schedule with the only Tax Detail being WHOLESALE
    • Added OUTRETAIL Tax Schedule with the only Tax Detail being OUTRETAIL
    • Added OUTWHOLESALE Tax Schedule with the only Tax Detail being OUTWHOLESALE

    What this does for me is utilize the exisiting Tax report to break out each area very nicely.  My client's tax accountant liked it very much and assured me it provided everything she needed.

    Thanks, again, Richard.

    Todd Bowlsby (a.k.a., Bullsai)

  • Richard Schultz Profile Picture
    Richard Schultz 1,085 on at

     Nifty approach.  Do you assign the tax details on a customer basis, or do they need to be assigned at the time of invoicing?  Might be handy to have a second diagnostic report to ensure that invoices/customers are coded correctly, particularly on an on-going basis.

  • Bullsai Profile Picture
    Bullsai on at

    The Tax Schedules are assigned to the individual addresses on the Customer record.  This is especially helpful in a state like WA, since they passed a destination-based tax law in July of 2008.

    In addition, if my client happens to have a Customer that they normally sell Wholesale to but this particular sale is a Retail sale, then, yes, they change the Tax Schedule on-the-fly in the Sales Order.

    GP does have native reports that allow for auditing the Customer records to ensure they are setup properly.

    Have a great day.

    Kind regards,

    Todd Bowlsby

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans