Tag Archives: GP SQL view
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

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

SQL view for current Receivables aging in Dynamics GP

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

SQL view for customer yearly totals in Dynamics GP

As a follow up to my SQL view for vendor yearly totals, here is something similar for customers. I have combined sales less credits/returns in one column, please take a look at the notes above the code (on lines 5 through 16 below) to see more details about the logic I am using. The code […]

Continue reading
briefcase invoice

SQL view for vendor yearly totals in Dynamics GP

There has been a lot of talk lately about the year end close Payables and Receivables. I feel like I have spent the last week or two justifying my reasoning for not needing to perform the year end close for Payables and Receivables to many of my customers and blog readers. (For more on this, […]

Continue reading
shaking hands

SQL view to show monthly totals for Dynamics GP Vendors

In response to a reader request, I have created this new view based on my Vendor Yearly Totals view that will show monthly totals for your Dynamics GP Vendors for all years. This view will show both the numbers and names of the months and assumes that you have 12 periods corresponding to the calendar […]

Continue reading
piggy bank

SQL view for bank deposits and receipts in Dynamics GP

About a year ago I published a view for a Checkbook Register and have received some follow up requests asking for a way to show the receipt details. Primarily this request seems to come from the need to see the difference in dates between the receipts and the deposits for tracking down possible bank reconciliation issues. I didn’t want to […]

Continue reading
briefcase invoice

SQL view for current Payables aging in Dynamics GP

Over the past few years I have had several requests for a summary current Payables aging report that can be easily exported into Excel. Yes, you can play with the Report Writer aging report to take out the headers and make it export to Excel, but sometimes there are other reasons for wanting a report outside […]

Continue reading
3d trolley

SQL view for last sale date of item

An interesting question came up in response to my last SQL view showing the last sale by customer and item. How do you show the latest sale of an item and who the customer was for that particular sale? This is a bit more difficult, as you have to make two passes through the data, first […]

Continue reading
empty cart

SQL view for last sale by customer and item

When was the last time a particular customer bought a particular item? Here is a little view that will tell you. For other Dynamics GP views and reporting tips, take a look at my GP Reports page. Or check out the SOP Tables page for more details about reporting on SOP data. ~~~~~ CREATE VIEW view_Last_Sale_By_Customer_Item AS /******************************************************************* […]

Continue reading
boxes

Customer shipping addresses in Dynamics GP

Need to see a list of all the shipping addresses for your Dynamics GP customers? You can use the view below which pulls out the ship to address and the associated tax schedule, shipping method , salesperson, etc. To see other SQL views for Dynamics GP receivables data, take a look at the Receivables SQL Views page. For […]

Continue reading
Follow

Get every new post delivered to your Inbox.

Join 1,361 other followers