How can I query MS DYNAMICS to view prior years sales

I am trying to determine 2010&2011sales numbers. I would also like to see by type commercial or non commercial items with totals.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
Which Dynamics product are you using?
manelson05Author Commented:
Dynamics 10
Mfg, Finanicials and project actg
manelson05Author Commented:
Dynamics GP
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
There is no one answer to your question, as there are a lot of different ways to enter 'sales' data into GP.  Also 'commercial' and 'non-commercial' are not GP terms, so you would need to identify what determines this in your data to be able to segregate your results this way.  

All that said, I have a bunch of different views posted on my blog that may help you either get the data you are looking for or at the very least give you some starting points:

SOP (Sales Order Processing) views:

If this is not what you're looking for, or you need some more help, can you please provide more detail as to which modules in GP you are using to record sales and what exactly you want to report on?
manelson05Author Commented:
I am trying to determine total sales for the years 2010 and 2011 based on SOPNUMBE that start with CM and MM. All commerical SOPNUMBE begin with CM and all MIL start with MM
manelson05Author Commented:
We are using SOP and Inventory. I am wanting to see total sales for the two years.
I looked in SQL and did discover the SOPNUMBE defining MIL and Commercial sales as MM and CM.

Does this help you at all?
manelson05Author Commented:
I am trying to come up with total sales dollars not quantities.
manelson05Author Commented:
Is it possible to use INVOICe for yearly totals to get the total dollars sold that year?
Victoria YudinOwner / Dynamics GP ConsultantCommented:
How about something like this:

select year(DOCDATE) Sale_Year, left(SOPNUMBE,2) Sale_Type,  
sum(SUBTOTAL) Sales_Amt
from SOP30200 
where VOIDSTTS = 0 and year(DOCDATE) in (2010,2011) 
and left(SOPNUMBE,2) in ('MM', 'CM')
group by year(DOCDATE), left(SOPNUMBE,2)

Open in new window

Victoria YudinOwner / Dynamics GP ConsultantCommented:
PS - that is assuming that only invoices start with MM and CM.  If that is not the case, you can either restrict on invoices only by adding the following before the last line:
and SOPTYPE = 3

Open in new window

or you need to do something more complicated, for example if you want to subtract returns.  Let me know if that is the case and I will help with that code if you need.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
manelson05Author Commented:
Excellent, this proved to be very helpful.

Thank you very much!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.