Tag Archives: GP Reports 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
, , ,

Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP

May 25, 2016 by Victoria Yudin 20 Comments

A lot of the requests I get are for actual vs. budget reporting. Certainly FRx and Management Reporter can do this pretty easily, but more and more I see users wanting this in Excel with a refreshable report. This blog post will give you everything you need to create a refreshable Excel report that shows monthly […]

Continue reading →
Dynamics GP, Excel reports, GL SQL code, GP Reports code, GP Reports Viewer, GP SQL scripts
, , , ,

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

Refreshable Excel report for historical GL trial balance in Dynamics GP

August 13, 2015 by Victoria Yudin 7 Comments

Want to see your historical General Ledger trial balance in Excel? Yes, you can always print out the one from GP and dump it to Excel and manipulate the results, but there is an easier way. Below is a stored procedure you can use to create this. As an added bonus, if you’re using GP Reports Viewer you can […]

Continue reading →
Dynamics GP, Excel reports, GL SQL code, GP Reports Viewer, GP SQL scripts
, , ,

Refreshable Excel report for monthly GL budget in Dynamics GP

April 28, 2015 by Victoria Yudin 6 Comments

Dynamics GP has some great functionality for uploading a General Ledger budget from Excel. However, if you have a lot of accounts, getting the data back into Excel is sometimes a little more time consuming than desired. I recently created a refreshable Excel report that uses a SQL stored procedure for this and wanted to […]

Continue reading →
Excel reports, GL SQL code, GP Reports code, GP SQL scripts
, , , ,

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 stored procedure for all open AR transactions by customer name

November 24, 2014 by Victoria Yudin 7 Comments

A little variation on my Unapplied AR Transactions view, this SQL stored procedure will return all open (unapplied) receivables transactions for customers with a name containing what is supplied as the parameter. In case you’re wondering why I am all of a sudden posting a stored procedure, I am going to follow this up later […]

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

SQL view for Dynamics GP open year GL trial balance

March 17, 2014 by Victoria Yudin 29 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 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 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
, , ,

SQL view for current Payables aging detail in Dynamics GP

April 24, 2013 by Victoria Yudin 35 Comments

I have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per open (unpaid) payables transaction. I am hard-coding the aging using the default aging setup installed with GP, which is aging by due date and using the […]

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

Changing part of a string in SQL Server using REPLACE

March 11, 2013 by Victoria Yudin 4 Comments

This latest SQL Server tip comes from the Feb/March 2013 GP Reports Viewer newsletter. Have you ever needed to change part of a string in SQL Server? I have seen this come up in a variety of situations, like needing to change account names in the General Ledger or needing to show something differently when […]

Continue reading →
Dynamics GP, GP Reports code, GP Reports Viewer, SQL coding, 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 Fixed Allocation Accounts in Dynamics GP

January 23, 2013 by Victoria Yudin 4 Comments

Below is a view that will return a list of the distribution accounts and percentages for all active fixed  allocation accounts in your Dynamics GP. Nothing fancy, but sometimes it is easier to have a report of these rather than have to look at them on the screen, especially when you have a lot of […]

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