Archive | GP SQL scripts RSS feed for this section
dollar symbol

SQL view for Dynamics GP vendors with last activity date

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
target

SQL view for Dynamics GP open year GL trial balance with month end balances

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
mechanism

SQL view for Dynamics GP open year GL trial balance

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

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

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
3d small people - diagram

SQL view for rolling 12 months of sales by item in Dynamics GP

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
3d small people - team with the puzzles

SQL view for Variable Allocation Accounts in Dynamics GP

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
money

SQL view for commissions details in Dynamics GP

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
push the gear

Dynamics GP SOP line items with serial numbers and 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
hand truck

SQL view for sales quantities by customer by item by year

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
Follow

Get every new post delivered to your Inbox.

Join 1,470 other followers