Tag Archives: General Ledger

Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP

May 25, 2016 by Victoria Yudin 20 Comments

A lot of the requests I get are for actual vs. budget reporting. Certainly FRx and Management Reporter can do this pretty easily, but more and more I see users wanting this in Excel with a refreshable report. This blog post will give you everything you need to create a refreshable Excel report that shows monthly […]

Continue reading →
Dynamics GP, Excel reports, GL SQL code, GP Reports code, GP Reports Viewer, GP SQL scripts
, , , ,

Refreshable Excel report for historical GL trial balance in Dynamics GP

August 13, 2015 by Victoria Yudin 7 Comments

Want to see your historical General Ledger trial balance in Excel? Yes, you can always print out the one from GP and dump it to Excel and manipulate the results, but there is an easier way. Below is a stored procedure you can use to create this. As an added bonus, if you’re using GP Reports Viewer you can […]

Continue reading →
Dynamics GP, Excel reports, GL SQL code, GP Reports Viewer, GP SQL scripts
, , ,

Refreshable Excel report for monthly GL budget in Dynamics GP

April 28, 2015 by Victoria Yudin 6 Comments

Dynamics GP has some great functionality for uploading a General Ledger budget from Excel. However, if you have a lot of accounts, getting the data back into Excel is sometimes a little more time consuming than desired. I recently created a refreshable Excel report that uses a SQL stored procedure for this and wanted to […]

Continue reading →
Excel reports, GL SQL code, GP Reports code, GP SQL scripts
, , , ,

SQL view for easier General Ledger transaction searches in Dynamics GP

September 4, 2014 by Victoria Yudin 10 Comments

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

SQL view for Dynamics GP open year GL trial balance with month end balances

July 9, 2014 by Victoria Yudin 30 Comments

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

SQL view for Dynamics GP open year GL trial balance

March 17, 2014 by Victoria Yudin 29 Comments

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

SQL view for Fixed Allocation Accounts in Dynamics GP

January 23, 2013 by Victoria Yudin 4 Comments

Below is a view that will return a list of the distribution accounts and percentages for all active fixed  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, especially when you have a lot of […]

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

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 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 Payables invoices originating from POP in Dynamics GP

September 1, 2010 by Victoria Yudin 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 reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Payables SQL code, POP SQL code
, , , , ,

Updates to SQL view to show all GL distributions for AP transactions

June 25, 2010 by Victoria Yudin 9 Comments

I have made a number of updates to my SQL view to show all GL distributions for AP transactions since I first published it. Some of these were in response to comments asking for additional fields, others were added when I came across new data to test with. Rather than publish another post with the latest revisions, I have updated the […]

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

SQL view with all GL distributions for AR transactions

February 10, 2010 by Victoria Yudin 48 Comments

As a corollary to my SQL view with all posted Receivables transactions, below is a view that will add all the General Ledger distributions to the AR transactions. I made a few changes to the original AR transactions view, primarily to remove some columns that are typically not needed and add underscores to the column names so […]

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

What’s with all these dates?

September 8, 2009 by Victoria Yudin 21 Comments

There are many different dates stored in Dynamics GP. This is typically a good thing, my philosophy is that it is always better to have more data than you need and not the other way around. However, it can get confusing rather quickly since a lot of the labels for the dates sound very similar. I will attempt […]

Continue reading →
Dynamics GP
, , , , , ,

SQL view for all posted GL transactions in Dynamics GP

August 11, 2009 by Victoria Yudin 123 Comments

Below is a SQL script to create a view showing all posted General Ledger transactions in Dynamics GP. This has the common columns asked for on reports, but you can certainly add your own as needed. For additional SQL code, please visit my General Ledger SQL views page or the GP Reports page. A few notes on this view: Year-end […]

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

Dynamics GP Trial Balance in Excel using FRx

July 17, 2009 by Victoria Yudin 43 Comments

While I work with SQL and Crystal Reports every day, there are some reports that are just easier created elsewhere. One example is a GL Trial Balance that you want to export to Excel. There are many ways of handling this requirement, I like using FRx for it.  Below are steps by step instructions on how to […]

Continue reading →
Dynamics GP, FRx, GP 10.0, GP 2010, GP 8.0, GP 9.0
, ,

Where did my transaction go?

April 11, 2009 by Victoria Yudin 7 Comments

This is a common question, especially with new users of Dynamics GP, and I was recently reminded of it by a post in the Microsoft Dynamics GP Newsgroup. While every transaction type will be slightly different, there is a basic lesson that is important for all GP users: beware of the Delete button. Most GP actions are updated automatically […]

Continue reading →
Dynamics GP
, , ,

Sales Transaction Entry GL distributions in Dynamics GP

December 8, 2008 by Victoria Yudin 106 Comments

The topic of General Ledger distributions for Sales Order Processing transactions has been known to cause a fair amount of confusion for Dynamics GP users.  I would like to address some of the typical questions we receive on this. Please click on the screen shots to see them larger/clearer.  Just to set the stage, I am talking about the […]

Continue reading →
Dynamics GP, GP 10.0, GP 2010, GP 8.0, GP 9.0
, ,

SQL view for Payables GL distributions in Dynamics GP

November 28, 2008 by Victoria Yudin 174 Comments

Here is a view that will return the General Ledger distributions for all posted or unposted payables transactions.  Unposted is also called ‘work’ and posted includes any open or historical transactions.  If you’re going to use this with SmartList Buidler, make sure to include the section at the end to grant permissions to DYNGRP. Disclaimer: I […]

Continue reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Payables SQL code
, , , ,
Older Entries