Archive | GP Reports code RSS feed for this section

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

Getting the name of a month from the month number in SQL Server

May 23, 2011 by Victoria Yudin 18 Comments

I was creating a SmartList for a customer recently and was using the GL11110 and GL11111 views in GP for monthly summaries of P&L accounts, but wanted to show the month names instead of period ID’s to make it a bit more user-friendly. I could have linked to the fiscal period setup table to get the period names, but […]

Continue reading →
GP Reports code, SQL coding, SQL Server
,

SQL view for all GL transactions in Dynamics GP

April 27, 2011 by Victoria Yudin 43 Comments

I have been asked a few times now to add unposted General Ledger transactions to my Posted GL Transactions view. Here it is. This view also excludes voided transactions and introduces new columns for transaction status and source doc. To see more information about GL tables in Dynamics GP, visit my GL Tables page. For more Dynamics […]

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

SQL view for last sale date of item

March 31, 2011 by Victoria Yudin 35 Comments

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

SQL view for last sale by customer and item

March 29, 2011 by Victoria Yudin 18 Comments

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

Customer shipping addresses in Dynamics GP

February 18, 2011 by Victoria Yudin 16 Comments

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

Yearly Fixed Assets depreciation totals in Dynamics GP

February 15, 2011 by Victoria Yudin 13 Comments

Around this time of year we get a lot of questions about closing the year for the Dynamics GP Fixed Assets module, since you cannot run January depreciation until you close the prior year. And right after we start getting requests for reports of prior year depreciation. Here is a view that shows year to date, last year […]

Continue reading →
Dynamics GP, Fixed Assets SQL code, GP Reports code, GP SQL scripts
, , , ,

Dynamics GP inventory by location with dates and sites

January 29, 2011 by Victoria Yudin 13 Comments

In response to a request from a reader, I have created a new version of the Inventory with Dates script to show the same information by Site ID. I also added a Quantity on Order column. You can see more Dynamics GP Inventory scripts here. Or visit my GP Reports page for links to additional GP […]

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

Change GP fiscal period names using SQL Server

January 27, 2011 by Victoria Yudin 15 Comments

Every time I create a new fiscal year in Dynamics GP or show customers how to do it, I get a little frustrated that I have to manually rename all the periods. Some users may not care that instead of January GP displays Period 1, but I don’t think that is very user friendly. So, for more years […]

Continue reading →
Dynamics GP, GP 10.0, GP 2010, GP 2013, GP 8.0, GP 9.0, GP SQL scripts, System/Setup SQL code
,

SQL queries for fiscal years and periods in Dynamics GP

November 24, 2010 by Victoria Yudin 9 Comments

Often when creating reports we’re asked to show the current year vs. last year or select the current fiscal year or period as a date range for values returned. Sometimes dates can be hard-coded, especially when the fiscal year is the calendar year. But some reports can be made more dynamic by using the fiscal periods set up in […]

Continue reading →
Dynamics GP, GP Reports code, System/Setup SQL code
, ,
Newer Entries
Older Entries