Archive | SOP SQL code RSS feed for this section

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

April 20, 2017 by Victoria Yudin 12 Comments

By popular request I have created a variation on my rolling 12 months of sales by item to show a rolling 12 months of sales by item and site ID in Dynamics GP. This view will return the last 12 months, not including the current month. So if you’re running this today – April 20, […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code, Uncategorized
, , ,

Dynamics GP sales amounts and quantities for rolling 12 months

March 14, 2017 by Victoria Yudin 17 Comments

Dynamics GP sales amounts and quantities for rolling 12 months

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code
, , ,

SQL view to verify unposted SOP transaction totals in Dynamics GP

September 12, 2014 by Victoria Yudin 5 Comments

Over the years we have seen a number of situations where a sales transaction total will be calculated incorrectly. It does not happen often and we’ve never been able to reproduce this on demand, so it’s very difficult to track down the cause for this. I have also seen a number of newsgroup posts talking […]

Continue reading →
Dynamics GP, SOP SQL code
, ,

Identify what modules your Dynamics GP sales data originates in

April 29, 2014 by Victoria Yudin 1 Comment

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 →
Dynamics GP, Receivables SQL code, SOP SQL code
, , ,

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

April 28, 2014 by Victoria Yudin 1 Comment

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 →
Dynamics GP, Receivables SQL code, SOP SQL code
, , ,

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

October 15, 2013 by Victoria Yudin 4 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

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

June 14, 2013 by Victoria Yudin 31 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

SQL view for SOP sales by customer by month

June 7, 2013 by Victoria Yudin 49 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

Assign sequential numbers in SQL Server

May 7, 2013 by Victoria Yudin 3 Comments

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 →
Dynamics GP, GP Reports code, GP Reports Viewer, SOP SQL code, SQL Server
, , , ,

SQL view for sales quantities by item by site by year

February 28, 2013 by Victoria Yudin 2 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

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

August 27, 2012 by Victoria Yudin 15 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code
, , ,

SQL view for commissions details in Dynamics GP

July 19, 2012 by Victoria Yudin 14 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code, SOP SQL code
, , ,

Dynamics GP SOP line items with serial numbers and comments

June 4, 2012 by Victoria Yudin 17 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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, , ,

SQL view for sales quantities by customer by item by year

March 9, 2012 by Victoria Yudin 12 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code
, , , ,

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

February 29, 2012 by Victoria Yudin 11 Comments

 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 →
Crystal Reports, Dynamics GP, GP 2010, GP Reports Viewer, SOP SQL code
, , ,

SQL view for sales quantities by item by year

January 23, 2012 by Victoria Yudin 32 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

SQL view for sales by item by month

August 29, 2011 by Victoria Yudin 48 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

SQL view for sales by item by year

August 11, 2011 by Victoria Yudin 34 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,
Older Entries