Featured

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

Create a refreshable Excel report based on a SQL Server stored procedure

November 24, 2014 by Victoria Yudin 16 Comments

A long awaited new feature is being released today for our GP Reports Viewer – Excel reports. What I think is truly amazing about this new feature is the fact that you can set up an Excel report based on a stored procedure to allow users to enter parameters and also to improve performance of your reports. […]

Continue reading →
Dynamics GP, Excel reports, GP Reports Viewer
, , ,

Year end close in Dynamics GP

November 14, 2008 by Victoria Yudin 102 Comments

Every year we get a slew of phone calls about closing the year in GP. Usually these start in December and I  thought I would beat the rush this year. Here are some resources and notes I have compiled over the years on the year end close. I will add to this post as I find more […]

Continue reading →
Dynamics GP, GP 10.0, GP 2010, GP 2013, GP 2015, GP 8.0, GP 9.0
, , , , , , , ,

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

Did you know you can change Void dates on Payables transactions?

September 20, 2016 by Victoria Yudin 12 Comments

I often surprise people by telling them to change the void dates when they void Payables transactions in Dynamics GP. If you’re voiding something that was just entered, leaving the defaulted dates is usually fine. However, when you are voiding an old check because you’re going to re-issue it, you typically want to void using the same date as the […]

Continue reading →
Dynamics GP, Uncategorized
,

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

Discount from Packt Publishing

September 15, 2015 by Victoria Yudin 2 Comments

Have you been thinking you want to get one of my books, but holding off? No time like the present! Through the end of September my publisher, Packt Publishing, is offering 50% off if you use discount code MDGP150. No more excuses. 🙂

Continue reading →
Dynamics GP
,

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

Dynamics GP hosting – a great experience with RoseASP

March 16, 2015 by Victoria Yudin 7 Comments

I recently had a great experience that I would like to share. We have a client that was on a very old version of Dynamics GP (8.0), very old version of SQL (2000) a very, very old server (going on 10 years), and outsourced IT. Long story short, their server went down, they lost 2 out of the 3 […]

Continue reading →
Dynamics GP

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

Happy New Year – 2015

January 5, 2015 by Victoria Yudin 22 Comments

I was out of town for a few days last week and it was a real pleasure to get the email notification that I am going to be a Microsoft Dynamics GP MVP for another year while I was away. This is the 11th year in a row for me. I am very honored to get […]

Continue reading →
Dynamics GP, Microsoft
,

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