Tag Archives: General Ledger
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

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

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
percent symbol

SQL view for Fixed Allocation Accounts in Dynamics GP

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
3d small people - team with the puzzles

SQL view for Variable Allocation Accounts in Dynamics GP

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

SQL view for all GL transactions in Dynamics GP

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
empty cart

SQL view for Payables invoices originating from POP in Dynamics GP

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

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

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

SQL view with all GL distributions for AR transactions

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

What’s with all these dates?

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

SQL view for all posted GL transactions in Dynamics GP

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 Trial Balance in Excel using FRx

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

Where did my transaction go?

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

Sales Transaction Entry GL distributions in Dynamics GP

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

SQL view for Payables GL distributions in Dynamics GP

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

Year end close in Dynamics GP

Every year we get a slew of phone calls about closing the year in GP. Usually these start in December and I  thought I would beat the rush this year. Here are some resources and notes I have compiled over the years on the year end close. I will add to this post as I find more […]

Continue reading

Dynamics GP GL table information

A new page has been posted up with the General Ledger table information.  A list of all the various pages available can be seen on the main GP Reports page.  I have also included links there to some additional reporting resources published by fellow Dynamics GP bloggers.

Continue reading


Get every new post delivered to your Inbox.

Join 1,718 other followers