Archive | SOP SQL code RSS feed for this section
cooking

Identify what modules your Dynamics GP sales data originates in

I have posted a lot of queries for getting sales totals from the Sales Order Processing module. However, many companies also enter transactions directly in the RM (Receivables Management) module. In that case, if you need to see the data from both of these sources, you may need to take a slightly different approach to […]

Continue reading
red white puzzle

SQL script for comparing voids between RM and SOP in Dynamics GP

How do we find transactions that were voided in the Receivables Management (RM) module, but not in Sales Order Processing (SOP)? This question has come up few a times on newsgroups and also with our customers. Most recently there was a question on the Dynamics GP Community Forum about this. The script below is a slightly cleaned […]

Continue reading
wave of money

SQL view for Dynamics GP sales quantities and amounts by item by site by month

Here is a new view created by request for one of my blog readers. This view combines my Sales by Item by Month and Sales Quantities by Item by Site by Month views. It will return columns for each month with the quantity and sales amount side by side. The year is hard-coded to be the current year […]

Continue reading

SQL view for sales quantities by item by site by month in Dynamics GP

As many variations for item quantity summaries as I think I have posted on this blog, there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for […]

Continue reading

SQL view for SOP sales by customer by month

Someone asked me for code to get monthly SOP sales and I could have sworn I already had that. But I searched and didn’t come up with anything, so here is a new view to show SOP sales (the total of SOP invoices less returns) by month. As usual, I am making a few assumptions (listed […]

Continue reading
winner

Assign sequential numbers in SQL Server

Here is a cool little SQL Server tip from the April GP Reports Viewer newsletter. Say you have a list of rows and you want to assign sequential numbers to them, so that you always know which one is first, second, etc. Or so that you can display line numbers. This is often a need […]

Continue reading
forklift

SQL view for sales quantities by item by site by year

Below is another variation on my view for sales by item by year and view for sales quantities by item by year. This one shows the total item quantity sold by site by year. The view below makes a number of assumptions (listed in the view comments in green), and I am hard coding years from 2006 through 2013 […]

Continue reading
3d small people - diagram

SQL view for rolling 12 months of sales by item in Dynamics GP

Rolling twelve month reports are not always very easy to create,  and I have been seeing more requests for them, so I thought I would show an example of one way to do this. The view below is a variation of my sales by item by month view, however instead of specifying all months in […]

Continue reading
money

SQL view for commissions details in Dynamics GP

Commissions are typically difficult to create generic reports for, as many companies have unique commission structures. We find that out-of-the-box functionality in GP does not really work for most companies and often the commissions are recorded in ways other than what was intended. That said, if you find that you are using the commissions functionality […]

Continue reading
push the gear

Dynamics GP SOP line items with serial numbers and comments

Based on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line items with the associated serial numbers (or lot numbers). […]

Continue reading
hand truck

SQL view for sales quantities by customer by item by year

Seems like no many how many variations of sales reports there are, people will always want more. I recently had a request for something similar to my view for sales quantities by item by year, but also adding in the customer.  Below is code for a view to accomplish this. This view makes a number of […]

Continue reading
orange businessmen

Emailing SOP invoices in GP 2010 made easy with GP Reports Viewer

 Background / The Problem For many companies a long awaited new feature introduced with Dynamics GP 2010 was the ability to email SOP (Sales Order Processing) invoices directly from GP. Many of our customers have started to offer emailing of invoices to their customers and are seeing an increased number of requests for emailed invoices. However, […]

Continue reading
3d boxes

SQL view for sales quantities by item by year

Ever since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There are two ways of doing this, once from inventory and another from sales.  Hard to say which is the best, as I have seen arguments for both, […]

Continue reading
Orange man box

SQL view for sales by item by month

Here is a new Dynamics GP SQL view in reponsse to a reader that liked my view for sales by item by year, but wanted to see the same thing by month. I am making a few assumptions (listed in the view comments), and am hard coding one year at a time (2011 in this example). You can easily […]

Continue reading
dollar chrome symbol

SQL view for sales by item by year

I had a request from a blog reader for a view that shows sales of items by year. This seemed like pretty useful code to create, so I put the following view together. I am making a few assumptions (listed in the view comments), and am hard coding years from 2005 through 2011. You can easily change the years or […]

Continue reading
Follow

Get every new post delivered to your Inbox.

Join 1,477 other followers