Category: General Ledger

Detailed Period Closing List – SQL View for Dynamics GP

CREATE VIEW [dbo].[Detailed_Period_Closing_List]

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Detailed Period Close Data
-- Run Against Company Database
-- Provides Checklist for Fiscal Period Close Settings - Detailed
-- Visit for more cool GP Stuff!
-- Updated by: Shawn Dorward - As of 09/14/2017
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

YEAR1 Fiscal_Year,
PERNAME as Period, 
PERIODDT as Period_Start_Date, 
PERDENDT as Period_End_Date, 
case sy40100.Series
 when 2 then 'Financial'
 when 3 then 'Sales'
 when 4 then 'Purchasing'
 when 5 then 'Inventory'
 when 6 then 'Payroll'
 when 7 then 'Project'
 else ''
 end Series,
ODESCTN as Origin_Name,
case sy40100.Closed
 when 0 then 'Open'
 When 1 then 'Closed'
 else ”
end Status,
DEX_ROW_ID as Dex_Row_ID

FROM sy40100
WHERE Pername <> 'Beginning Balance' and series <> 1 and series <> 0

-- Adding Permissions to DYNGRP


GRANT SELECT on [Detailed_Period_Closing_List] to DYNGRP

GP #LifeHacks 103: Track JE’s in #Excel – Paste into GP

I love Excel!  Who doesn’t, right?  As an accountant, I live in Excel.  This is the easiest place for me to track and create Journal Entries. But after all that work, how do we get it into our accounting system, Microsoft Dynamics GP? Easy, we just ‘Paste’ the entry. Simple, I know.

GP Life Hack #103

Dynamics GP offers the cool functionality of pasting in copied data from Excel.  This includes distribution reference, the GL Account number, Debit Amount and the Credit Amount:


A 5th column can be used for Analytical Account Alias.

Simply copy all rows of the entry (do not include the header row) and open a new JE window.  Move your cursor into the account field in the scrolling window and hit the Excel paste button.

103 1

The data copied will now be ‘Pasted’ into the transaction entry window instead of re-keying. Totally awesome!

Keep in mind that when pasting into the transaction entry window, the accounts are validated – if any errors exist, you will get a report showing which accounts need your attention.

103 2.png

That’s it, and you save a TON of keying time. I just tested this and pasted 1000 lines of a Journal Entry and it took under 30 seconds!

Pro Tip! Save the excel file and attach it to the Journal entry so you can reference it at any time.

Thanks for reading!


GP Life Hack #103 … click to see original posting

GP #LifeHacks 133: Keep your budget data safe from accidental edits in GP

Budgets… not the most exciting topic but one of the most important aspects to any business.

Microsoft Dynamics GP has a user friendly (and excel friendly) budget component built within the financial module. Users can easily build an excel file based on previous year actual balances, existing budget files already in the system, as well as blank budgets. Using the Excel portion of the budget process means that companies can use formulas in excel to automatically update accounts based on other factors. Formulas can be linked to other account values, other worksheets, other files, etc. This makes budgeting for payroll expenses, taxes, depreciation, etc… very easy!

133 1.png
Another additional benefit to this process is that budget files can be broken down, perhaps by department, and distributed to different team members.  Once everyone updates their files, these Excel files can be imported back into Dynamics GP – combined into one budget, or multiple budgets.  At the end of the process, when the budget is final and approved by everyone involved in the process, we can now use this for reporting in Management Reporter and ISV products.

There’s a lot of work that goes into the budget process… and once the annual cycle is complete, it is probably time for a celebration.  Don’t forget to avoid any accidents and get that budget locked down in Dynamics GP!

GP #LifeHack 133

133 2.pngIf you are like me and have struggled with fellow users making changes to the GP Budget file, then you completely understand how frustrating it can be when you run your financial reports, and maybe even publish them, just to get questions about the reliability of the budget data. You can prevent this by using this GP Life Hack!

Use the padlock on the budget maintenance window to ensure that others don’t accidentally delete or adjust budget figures.


Locking the budget has many benefits but let’s be honest, that peace of mind is important. Using this little known function can save you time and ensure your hard work doesn’t take a reputation hit.

To the right of the budget ID is a little padlock – when clicked, a password window will pop up. Be sure to document the password used. Another helpful aspect to this would be to save the excel file off securely.

Now that your budget is secure, wouldn’t it be nice to be able to include this as an inquiry window within GP? Look for my next GP Life Hack as we uncover a very cool feature of GP that allows you to compare the budget to a specific account, or group of accounts, from an inquiry window – without running a report!

GP Life Hack #133 … click to see original posting

GP #LifeHacks 121: Keep your AP & AR in balance with your General Ledger

I know your team works hard… day in and day out… and at month end, you reconcile your Accounts Payable or Accounts Receivable sub ledger to your GL Trial Balance with an optimistic approach that never seems to really pay off.  No matter how hard you try, there is always a reconciling adjustment needed in order to balance…  UGH!  Whether it’s a penny, 1,000.00 or 61,312.76… it’s downright frustrating.

What is causing this variance?  Usually it is a journal entry.  We all love them, but let’s be honest… not everyone makes journal entries with the same level of confidence as you do.  Let’s get this game of ‘tug of war’ fixed once and for all!

Continue reading “GP #LifeHacks 121: Keep your AP & AR in balance with your General Ledger”