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
Archive by Author
Identify what modules your Dynamics GP sales data originates in
April 29, 2014 
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 SQL script for comparing voids between RM and SOP in Dynamics GP
April 28, 2014 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 SQL view for Dynamics GP open year GL trial balance
March 17, 2014 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 Identifying reporting needs in a Microsoft Dynamics GP 2013 implementation
January 28, 2014 2 Comments
Want to learn more about identifying reporting needs when planning a Dynamics GP implementation? Check out this article adapted by MSDynamicsWorld.com from my latest book, Microsoft Dynamics GP 2013 Implementation. There are still a few weeks to go in the book giveaway – you just need to post a comment on that blog post to be entered […]
Continue reading Win a free copy of my latest book from Packt
January 21, 2014 43 Comments
I have teamed up with Packt Publishing to organize a giveaway of my latest book, “Microsoft Dynamics GP 2013 Implementation”. Three lucky winners will receive a copy of the book. To enter the giveaway, all you need to do is visit the Packt book page to check out the book details, then post your expectations from the […]
Continue reading New SmartList Builder features from eOne
January 6, 2014 3 Comments
As promised, eOne has released new SmartList Builder features as soon as it got the product back from Microsoft. (If you’re not sure what I mean by that, here is a reminder.) I am hoping to test all of this out in the upcoming week, but in the meantime, I am very excited by some of […]
Continue reading Happy New Year – 2014
January 1, 2014 15 Comments
I found out today that I am going to be a Microsoft Dynamics GP MVP for another year! This makes the 10th year in a row for me. 🙂 I am very honored to get this award again and would like to thank everyone reading this – I could not do it without you! For those […]
Continue reading How to get rid of negative payment transactions in Dynamics GP
November 14, 2013 15 Comments
A few versions ago Microsoft introduced the ability to enter a negative payment (Cash Receipt) for a customer in Microsoft Dynamics GP. I have now worked with a number of customers that have these negative payments sitting on customer accounts who needed help getting rid of them. Below is what this typically looks like when you’re […]
Continue reading SQL view for Dynamics GP sales quantities and amounts by item by site by month
October 15, 2013 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 Important SmartList Builder announcement
September 10, 2013 13 Comments
On the heels of the Extender and Collections Management announcements over the last year, it has just been announced that SmartList Builder is joining the Microsoft Dynamics GP OEM modules “coming home”. You can read about all the reasons and details for this in Martin Olsen’s comprehensive announcement on the eOne blog. This is certainly […]
Continue reading SQL stored procedure to search an entire database
July 22, 2013 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 view for sales quantities by item by site by month in Dynamics GP
June 14, 2013 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 SQL view for SOP sales by customer by month
June 7, 2013 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 Assign sequential numbers in SQL Server
May 7, 2013 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 SQL view for current Receivables aging detail in Dynamics GP
May 1, 2013 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 SQL view for current Payables aging detail in Dynamics GP
April 24, 2013 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 Changing part of a string in SQL Server using REPLACE
March 11, 2013 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 readingMy Last Book
Dynamics GP 2013 Implementation
My Previous Book
Dynamics GP 2010 Implementation
-
Using credit cards to pay vendors in Dynamics GP
-
SQL view for Payables GL distributions in Dynamics GP
-
SQL view for all posted GL transactions in Dynamics GP
-
Sales Transaction Entry GL distributions in Dynamics GP
-
SQL view to show AP apply information in GP
-
SQL view for Payables payment apply detail in GP
-
Year end close in Dynamics GP
- SQL view with AR apply detail
-
Granting access to a new SmartList in Dynamics GP
-
SQL view with all SOP line items in Dynamics GP
-
SQL view for unposted checks in Dynamics GP
-
SQL view for rolling 12 months of sales by item by site in Dynamics GP
-
Dynamics GP sales amounts and quantities for rolling 12 months
-
Updating Dynamics GP data directly in SQL
-
Did you know you can change Void dates on Payables transactions?
-
Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP
-
SQL code to show top X percent of vendor data
-
Discount from Packt Publishing
-
Refreshable Excel report for historical GL trial balance in Dynamics GP
-
Refreshable Excel report for monthly GL budget in Dynamics GP
-
Victoria Yudin: Hi Stephanie, I just tested setting it up in my G...
-
Stephanie Crawley: Yep, works like a dream when i do that. Our worka...
-
-
-
-
-
-
-
Lenny: Thank you so much for sharing all this info, espec...
-
Victoria Yudin 
Recent Blog Comments