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
Tag Archives: SQL code
SQL view for sales by item by month
August 29, 2011 
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 SQL view for sales by item by year
August 11, 2011 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 Getting the name of a month from the month number in SQL Server
May 23, 2011 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 SQL view for all GL transactions in Dynamics GP
April 27, 2011 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 SQL view for last sale date of item
March 31, 2011 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 SQL view for last sale by customer and item
March 29, 2011 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 Customer shipping addresses in Dynamics GP
February 18, 2011 14 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 Yearly Fixed Assets depreciation totals in Dynamics GP
February 15, 2011 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 inventory by location with dates and sites
January 29, 2011 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 Change GP fiscal period names using SQL Server
January 27, 2011 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 SQL queries for fiscal years and periods in Dynamics GP
November 24, 2010 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 SQL view for Dynamics GP Checkbook Register
October 14, 2010 30 Comments
The view below will return all Checkbook transactions for Dynamics GP. It does not show details for the deposits because I wanted to keep this to one line per transaction, as it appears on the Checkbook Register in GP. To add deposit detail to you report, you can join table CM20300 on CM20200.CMTrxNum = CM20300.depositnumber. You […]
Continue reading SQL view for Payables apply detail and GL distributions in Dynamics GP
September 23, 2010 37 Comments
Who doesn’t need yet another view for Payables transactions in Dynamics GP? 🙂 The view below is a combination of my Payment Apply Detail and GL Distributions for AP Transactions views. It lists all Payments, then shows the transactions they were applied to and the GL distributions of those applied to transactions. You can find other Dynamics GP Payables […]
Continue reading Coding specific dates in SQL Server
September 13, 2010 3 Comments
Quite often when writing reports I need to write a formula to calculate dates. Dates are not very straightforward to code in SQL Server, and I have started keeping a list of various formulas so that I do not have to re-write the code every time. I have previously posted some code on calculating date differences in SQL, […]
Continue reading SQL view for Payables invoices originating from POP in Dynamics GP
September 1, 2010 40 Comments
This Dynamics GP SQL view originated from a request on the Dynamics GP customer forum, but is also something that I can see being useful in a variety of situations. It returns all posted Payables invoices that came from the Purchase Order Processing module with details of the items that were received on each invoice as well […]
Continue readingSQL view for asset account numbers in Dynamics GP
August 10, 2010 4 Comments
Want to see all the account numbers set up for all your Fixed Assets? You can do this in SmartList, but SmartList is sometimes limiting when you want to do complex searches. Here is a view that will return all the account numbers assigned for each asset in Dynamics GP. ~~~~~ CREATE VIEW view_Asset_Accounts AS /******************************************************************* view_Asset_Accounts […]
Continue readingSQL view for Inventory Price Levels in Dynamics GP
August 9, 2010 16 Comments
When you are creating prices for different price levels in Dynamics GP, there is no easy way to see these, or to see all the prices for a particular price level. Below is a view that can help with this. ~~~~~ CREATE VIEW view_Inventory_Price_Levels AS /******************************************************************* view_Inventory_Price_Levels Created on Aug 9, 2010 by Victoria Yudin – […]
Continue readingMy Last Book
Dynamics GP 2013 Implementation
My Previous Book
Dynamics GP 2010 Implementation
-
Using credit cards to pay vendors in Dynamics GP
-
SQL view for Payables GL distributions in Dynamics GP
-
SQL view for all posted GL transactions in Dynamics GP
-
Sales Transaction Entry GL distributions in Dynamics GP
-
SQL view to show AP apply information in GP
-
SQL view for Payables payment apply detail in GP
-
Year end close in Dynamics GP
- SQL view with AR apply detail
-
Granting access to a new SmartList in Dynamics GP
-
SQL view with all SOP line items in Dynamics GP
-
SQL view for unposted checks in Dynamics GP
-
SQL view for rolling 12 months of sales by item by site in Dynamics GP
-
Dynamics GP sales amounts and quantities for rolling 12 months
-
Updating Dynamics GP data directly in SQL
-
Did you know you can change Void dates on Payables transactions?
-
Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP
-
SQL code to show top X percent of vendor data
-
Discount from Packt Publishing
-
Refreshable Excel report for historical GL trial balance in Dynamics GP
-
Refreshable Excel report for monthly GL budget in Dynamics GP
-
Victoria Yudin: Hi Stephanie, I just tested setting it up in my G...
-
Stephanie Crawley: Yep, works like a dream when i do that. Our worka...
-
-
-
-
-
-
-
Lenny: Thank you so much for sharing all this info, espec...
-
Victoria Yudin 
Recent Blog Comments