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
Archive by Author
SQL view for Fixed Allocation Accounts in Dynamics GP
January 23, 2013 
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 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 reading Voiding a partially applied Payables transaction in Dynamics GP
October 11, 2012 20 Comments
Periodically I get calls or see questions online about how to void a partially applied Payables transaction in Dynamics GP. This is one area where the Payables module differs drastically from the Receivables module and you have to jump through some hoops to actually accomplish this. The issue is that when you go to void […]
Continue reading Big announcement for Microsoft Dynamics GP’s Extender module
October 8, 2012 23 Comments
Last week, while searching for something on Microsoft’s PartnerSource, I came across an announcement that Microsoft is discontinuing sales of the Extender module and that both sales and support of Extender will revert back to eOne Solutions, the original developer of Extender, starting February 1, 2013. Please note that eOne has always sold a more […]
Continue reading Coding for specific dates in Crystal Reports
September 18, 2012 4 Comments
Below is an old tip from one of the GP Reports Viewer monthly newsletters. In the past few months I have had to look this up several times, because I just cannot seem to remember the syntax, so I thought I would put it on my blog, where it will be easier for to find […]
Continue reading SQL view for rolling 12 months of sales by item in Dynamics GP
August 27, 2012 15 Comments
Rolling twelve month reports are not always very easy to create, and I have been seeing more requests for them, so I thought I would show an example of one way to do this. The view below is a variation of my sales by item by month view, however instead of specifying all months in […]
Continue reading SQL view for Variable Allocation Accounts in Dynamics GP
August 23, 2012 10 Comments
Below is a view that will return a list of the distribution and breakdown accounts for all active variable 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, specially since you have to click on […]
Continue reading SQL view for commissions details in Dynamics GP
July 19, 2012 14 Comments
Commissions are typically difficult to create generic reports for, as many companies have unique commission structures. We find that out-of-the-box functionality in GP does not really work for most companies and often the commissions are recorded in ways other than what was intended. That said, if you find that you are using the commissions functionality […]
Continue reading Dynamics GP SOP line items with serial numbers and comments
June 4, 2012 21 Comments
Based on some reader requests, this new view is a combination of my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line items with the associated serial numbers (or lot numbers). […]
Continue reading Using Lookups in GP Reports Viewer to make parameter selection more user friendly
May 31, 2012 3 Comments
One of the critical elements in reporting is parameter selection. During report creation it is essential to make sure that the proper parameters are added to the report to correctly filter on the data the users want to see. However, it doesn’t just stop there. To ensure user friendly reports it is also very important […]
Continue reading Concatenating strings in SQL Server
May 21, 2012 7 Comments
This SQL Server tip comes from the April 2012 edition of the GP Reports Viewer newsletter. A request that we have seen many times when creating SOP invoices for Dynamics GP is to concatenate serial numbers. So if there is an item with serial numbers, instead of showing a list with one serial number per […]
Continue reading Finding resources
May 12, 2012 5 Comments
Almost on a daily basis I find myself looking for things like system requirements, end of support dates, build numbers and downloads for the various products that I work with. Even though search engines have greatly improved over the last few years, I still often struggle to find this information. While I consider myself to […]
Continue reading SQL view for sales quantities by customer by item by year
March 9, 2012 12 Comments
Seems like no many how many variations of sales reports there are, people will always want more. I recently had a request for something similar to my view for sales quantities by item by year, but also adding in the customer. Below is code for a view to accomplish this. This view makes a number of […]
Continue reading Emailing SOP invoices in GP 2010 made easy with GP Reports Viewer
February 29, 2012 11 Comments
Background / The Problem For many companies a long awaited new feature introduced with Dynamics GP 2010 was the ability to email SOP (Sales Order Processing) invoices directly from GP. Many of our customers have started to offer emailing of invoices to their customers and are seeing an increased number of requests for emailed invoices. However, […]
Continue reading Microsoft Dynamics Mayhem
February 12, 2012 5 Comments
February 2012 is Microsoft Dynamics Mayhem month over at Packt Publishing, the publishers of my Microsoft Dynamics GP 2010 Implementation book. What does this mean for you? If you have been waiting to pick up a copy of any of the Dynamics books (this includes GP, CRM, AX, NAV and even Sure Step), this is the […]
Continue reading SQL view for current Receivables aging in Dynamics GP
January 25, 2012 71 Comments
I have created a variation on my view that shows all unapplied Receivables transactions to show customer aging in buckets. This is only looking at functional currency and will return data in summary, meaning one row per customer with a balance. I am hard-coding the aging using the default aging setup installed with GP, which is aging by […]
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
-
SQL view for Payables payment apply detail in GP
-
Sales Transaction Entry GL distributions in Dynamics GP
-
SQL view to show AP apply information 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 Matthew, This is a limitation in GP. "Why" is ...
-
Matthew: Why does "Sales" type distr...
-
-
Kevin Mackol: Does this still work on new...
-
-
Alex: Hi Victoria, Hi Victoria, Hi Victoria,Thank you so mu...
Victoria Yudin 
Recent Blog Comments