3d small people - concept of creating

New SmartList Builder features from eOne

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
MVP Logo

Happy New Year – 2014

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

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
wave of money

SQL view for Dynamics GP sales quantities and amounts by item by site by month

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

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
examine files

SQL stored procedure to search an entire database

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

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

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

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
briefcase invoice

SQL view for current Receivables aging detail in Dynamics GP

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
approved stamp

SQL view for current Payables aging detail in Dynamics GP

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

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

SQL view for sales quantities by item by site by year

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
percent symbol

SQL view for Fixed Allocation Accounts in Dynamics GP

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
MVP Logo

Happy New Year – 2013

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

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

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
exclamation mark

Big announcement for Microsoft Dynamics GP’s Extender module

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