Archive | GP Reports code RSS feed for this section

Create a refreshable Excel report based on a SQL Server stored procedure

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

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
3d small people - negative to positive

SQL view to verify unposted SOP transaction totals in Dynamics GP

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

SQL view for easier General Ledger transaction searches in Dynamics GP

Just about every Dynamics GP customer I have worked with, at one time or another, has had occasion to search the General Ledger for a particular dollar amount. Usually, I use the Account Transactions SmartList for this and search for Debit Amount = XXX or Credit Amount = XXX (using the Match 1 or More search […]

Continue reading
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
cooking

Identify what modules your Dynamics GP sales data originates in

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
red white puzzle

SQL script for comparing voids between RM and SOP in Dynamics GP

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

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
Follow

Get every new post delivered to your Inbox.

Join 1,626 other followers