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
Tag Archives: Dynamics GP
Updating Dynamics GP data directly in SQL
January 17, 2017 
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 Did you know you can change Void dates on Payables transactions?
September 20, 2016 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 SQL code to show top X percent of vendor data
March 21, 2016 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 Discount from Packt Publishing
September 15, 2015 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 Refreshable Excel report for historical GL trial balance in Dynamics GP
August 13, 2015 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 hosting – a great experience with RoseASP
March 16, 2015 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 Create a refreshable Excel report based on a SQL Server stored procedure
November 24, 2014 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 SQL stored procedure for all open AR transactions by customer name
November 24, 2014 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 SQL view to verify unposted SOP transaction totals in Dynamics GP
September 12, 2014 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 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 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 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 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 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 Happy New Year – 2013
January 1, 2013 22 Comments
It’s now become a little tradition of mine to sit in front of the computer when I get up on New Year’s Day, sipping coffee and checking my email…hoping that I once again can write a little blog post like this one. I just got that email I was waiting for, letting me know that […]
Continue reading How to add a sparkline to a report in SSRS
November 27, 2012 3 Comments
Introduction: This blog post is written by my friend and colleague Mickie Stamm. Mickie is a fabulous Dexterity, SQL and application developer and is one of the great minds behind GP Reports Viewer. By Mickie Stamm: In the last GP Reports Viewer newsletter I wrote about using rectangles as containers in SQL Server Reporting Services […]
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