Tag Archives: GP Reports code

Coding for specific dates in Crystal Reports

September 18, 2012 by Victoria Yudin 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 →
Crystal Reports, Crystal tips for GP, GP Reports Viewer
, ,

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

August 27, 2012 by Victoria Yudin 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 →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code
, , ,

SQL view for Variable Allocation Accounts in Dynamics GP

August 23, 2012 by Victoria Yudin 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 →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts
, ,

SQL view for commissions details in Dynamics GP

July 19, 2012 by Victoria Yudin 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, GP Reports code, GP SQL scripts, Receivables SQL code, SOP SQL code
, , ,

Dynamics GP SOP line items with serial numbers and comments

June 4, 2012 by Victoria Yudin 17 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 →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, , ,

Concatenating strings in SQL Server

May 21, 2012 by Victoria Yudin 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 →
GP Reports code, GP Reports Viewer, SQL coding, SQL Server
, ,

SQL view for sales quantities by customer by item by year

March 9, 2012 by Victoria Yudin 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 →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code
, , , ,

SQL view for current Receivables aging in Dynamics GP

January 25, 2012 by Victoria Yudin 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 reading →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code
, , ,

SQL view for sales quantities by item by year

January 23, 2012 by Victoria Yudin 32 Comments

Ever since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There are two ways of doing this, once from inventory and another from sales.  Hard to say which is the best, as I have seen arguments for both, […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

SQL view for customer yearly totals in Dynamics GP

January 9, 2012 by Victoria Yudin 35 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code
, , , , ,

SQL view for vendor yearly totals in Dynamics GP

January 4, 2012 by Victoria Yudin 20 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , , , ,

SQL view to show monthly totals for Dynamics GP Vendors

December 9, 2011 by Victoria Yudin 19 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL view for SOP email setup in GP 2010

October 24, 2011 by Victoria Yudin 9 Comments

I am starting to work with emailing SOP documents using Dynamics GP 2010 and new functionality that we are going to be releasing in an upcoming build of GP Reports Viewer. It’s pretty cool stuff, however, as I am working with customers to set this up, we’re finding that out-of-the box GP does not have any […]

Continue reading →
Dynamics GP, GP 2010, GP Reports Viewer, System/Setup SQL code
, , ,

SQL view for bank deposits and receipts in Dynamics GP

October 6, 2011 by Victoria Yudin 11 Comments

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 →
Bank Rec SQL code, Dynamics GP, GP Reports code, GP SQL scripts
, , ,

SQL view for current Payables aging in Dynamics GP

September 30, 2011 by Victoria Yudin 51 Comments

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 →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL view for user activity in Dynamics GP

September 12, 2011 by Victoria Yudin 19 Comments

There are many different pieces of code that I have seen for this over the years, but more often than not what our users ask me for is a way to quickly see who is logged into GP, what company, and when did they log in. While this can easily be seen on the User Activity window (GP | […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, System/Setup SQL code
,

SQL view for sales by item by month

August 29, 2011 by Victoria Yudin 48 Comments

Here is a new Dynamics GP SQL view in reponsse to a reader that liked my view for sales by item by year, but wanted to see the same thing by month. I am making a few assumptions (listed in the view comments), and am hard coding one year at a time (2011 in this example). You can easily […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,

SQL view for sales by item by year

August 11, 2011 by Victoria Yudin 34 Comments

I had a request from a blog reader for a view that shows sales of items by year. This seemed like pretty useful code to create, so I put the following view together. I am making a few assumptions (listed in the view comments), and am hard coding years from 2005 through 2011. You can easily change the years or […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, ,
Newer Entries
Older Entries