Tag Archives: SQL code

SQL view for unposted checks in Dynamics GP

August 29, 2017 by Victoria Yudin 11 Comments

I have been asked more than a few times for a script that shows uposted checks and their apply detail. This can be helpful to replace the Check Batch Edit List report, which is universally hated by all GP users I have spoken to. create view view_unposted_check_batch as — ~ ~ ~ ~ ~ ~ […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , , ,

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
, , ,

Updating Dynamics GP data directly in SQL

January 17, 2017 by Victoria Yudin 20 Comments

I would like to start by saying that I do not normally advocate updating Dynamics GP data directly in SQL Server. Most of the things you need to do should be handled either inside the Dynamics GP application or with integration tools like Integration Manager or eConnect. This is especially true of transactional data and […]

Continue reading →
Dynamics GP, SQL Server
, ,

SQL code to show top X percent of vendor data

March 21, 2016 by Victoria Yudin 1 Comment

I had an interesting request the other day – how do you show the top 10 (or 20) percent of vendor payments (or invoices) for a particular date range? This is something that has to be done in a several steps – first you have to get your data, then summarize of it, then calculate the top […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code, SQL coding, Uncategorized
, , ,

SQL view for total payments to 1099 vendors in the prior year

January 20, 2015 by Victoria Yudin 8 Comments

We received several requests just yesterday for a listing of total vendor payments in the prior calendar year. Below is a view that will show the total payments in the prior year for all 1099 vendors. This is not necessarily meant to match the report that comes out from the Print 1099 window, rather this is total payments for […]

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

SQL view for easier General Ledger transaction searches in Dynamics GP

September 4, 2014 by Victoria Yudin 10 Comments

Just about every Dynamics GP customer I have worked with, at one time or another, has had occasion to search the General Ledger for a particular dollar amount. Usually, I use the Account Transactions SmartList for this and search for Debit Amount = XXX or Credit Amount = XXX (using the Match 1 or More search […]

Continue reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts
, ,

SQL view for Dynamics GP vendors with last activity date

July 17, 2014 by Victoria Yudin 13 Comments

I have seen many requests recently for a way to see what Dynamics GP vendors have had no recent activity. Since ‘recent’ can mean different things, I thought I would put together a view that shows the last activity date for each vendor. Once you have this you can easily filter for whatever date range you […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, ,

SQL view for Dynamics GP open year GL trial balance with month end balances

July 9, 2014 by Victoria Yudin 30 Comments

Based on a reader’s request, I have created a variation on my SQL view for Dynamics GP open year GL trial balance. This version will show you the month end balances instead of the net change for each month. Here is a sample of the results (please click on the image to see it bigger): […]

Continue reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts
, ,

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 open year GL trial balance

March 17, 2014 by Victoria Yudin 27 Comments

General Ledger trial balances have been a popular request lately. The view below will return a General Ledger trial balance showing the monthly net change for the first open year in your Dynamics GP. I’ve hard-coded month names to be the calendar year, if you are on a different fiscal year, you will need to […]

Continue reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts
, , ,

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 stored procedure to search an entire database

July 22, 2013 by Victoria Yudin 1 Comment

I don’t often re-post things from other blogs, but this is just too good not to share. David Musgrave recently published an update to his spSearchOnAllDB stored procedure on his blog, Developing for Dynamics GP. Simply run the script to create the stored procedure and you can search all the tables in a database for a […]

Continue reading →
SQL Server
,

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
, ,

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 current Receivables aging detail in Dynamics GP

May 1, 2013 by Victoria Yudin 54 Comments

Another reader request brings us this new script. Below is a view for current Receivables aging in detail. This code is only looking at functional currency and will return one row per open (unpaid) receivables transaction. I am hard-coding the aging using the following buckets and aging by due date: Current 0 to 30 Days 31 to […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code
, , ,
Older Entries