Reconcile Receivable/Payable agings to the general ledger and voids

Voiding receivable or payable transactions can create differences when reconciling your subledgers to the GL. On the void windows for receivable or payables there are two date fields, one refers to the general ledger and one refers to subledger so be sure these fields are in the same month and year to eliminate any timing differences.

In either receivables or payables the void date field refers to subledger and the posting date field refers to the general ledger, here’s an example from the receivables void window:

For more detailed information about reconciling your agings to the GL you can refer to the this (more extensive) article.

https://support.microsoft.com/en-us/help/866570/information-about-differences-when-you-reconcile-general-ledger-to-payables-management-or-to-receivables-management-in-microsoft-dynamics-gp

Posted in Dynamics GP | Tagged | Leave a comment

Export Payables Detail Historical Trial Balance to excel from reporting services

Microsoft provides an accounts payable detail historical aging report in SQL report services that I have modified to easily export to excel, here’s what the output looks like:

GPpmHatbSSRSexport

This report originates from the standard report Dynamics GP provides when you create them from the Reporting tools setup window and uses the same stored procedure.  Also, I have defaulted and hidden the majority of the parameters that are never used.  The original report has 22 parameters!

To use this report you are required to have SQL reporting services (SSRS) installed and configured which creates a website that users can access and run GP reports from.  SSRS is a component of Microsoft SQL, and Business Intelligence Development Studio (part of Visual Studio) is used to develop, design, and publish reports.  After you download the RDL file below you will have to create a Development Studio Project or add it to an existing one, modify the datasource, then publish to your SSRS website.  Currently, the datasource is pointing to the default instance of SSRS and the TWO database which can be modified to your environment.  This report came from SQL 2008 and GP 2013.  I have published this report for a few clients who are on GP 2010 as well.

Download report

 

If you have any questions, feel free to email me at Steve@Reese-Consulting.com

Posted in Dynamics GP, SQL reporting | Leave a comment

Keeping the header rows visible in SQL reporting services

Have you every had problems where your reporting services report loses it’s header row as you scroll?  Take a look at this video – It helped the look and feel of my reports significantly.

Posted in SQL reporting | Leave a comment

FRx launcher run-time error 9 subscript out of range

Of all the issues that can come up from this error message, I have not found this one anywhere.  It’s a combination of running the FRx launcher (not designer) and a report with rounding.  The top is the error message and the bottom is what the launcher should be displaying.

FRx_launcher_round_error9

I tried this on two different FRx service packs (sp11 and sp12) and different computers with the same results.  The work-around is to do one of the following:

 

1. Remove rounding on the row.

2. Create another catalog ID with no rounding.

3. Ignore the warning – the report viewer will launch after the report is done.

 

Here are some more in-depth articles on error 9, mainly related to FRx Report Designer:


http://support.microsoft.com/kb/865978

http://support.microsoft.com/kb/871320

Posted in FRx | Leave a comment

In Dynamics GP 2013 R2 you can copy/paste a GL entry from excel directly to the transaction entry window

You’ll need the following columns set up in excel, just copy them to your clipboard (Ctrl+C), be sure to exclude the header row. The column names do not matter but the column order does matter.

Copy/paste these fields-distribution reference is optional

Copy/paste these fields-distribution reference can be blank

Open up the GL transaction entry window and choose Paste, be sure the journal entry window is blank, otherwise the paste button will be greyed out.  You cannot use the Ctrl+V shortcut – you must click on the paste button in yellow.

CopyPasteToGL2

If you click the paste button with one or more invalid GL account(s) nothing will be brought into the journal entry window and you must fix the issue first.  Plus, you will get a report showing the invalid accounts.

CopyPasteToGL3

You can only copy/paste one journal entry at a time.  However, this is such an improvement over the multi-user issues with text files and integration manager.

If you have problems, take a look at this post:

http://community.dynamics.com/gp/b/dynamicsgp/archive/2014/08/22/gp-2013-r2-copy-and-paste-from-excel-to-gl-nothing-happens.aspx

Posted in Dynamics GP | Leave a comment

Deletion of GL accounts during the year-end close process

In Dynamics GP when users run the general ledger year-end close some GL accounts may be deleted if the following criteria is met.

• No balance
• No activity for an open period
• No account history amounts
• Not part of an allocation account
• Not part of an unposted transaction
• No multicurrency data
• No transaction history records

However, I found it can be a variable allocation account.  One Dynamics GP customer uses FastPath for security as well as to track GL account deletions and the accounting manager showed up on a deletion report when she doesn’t have access to the account or unit account maintenance windows.  This caused a real problem during a sox audit.  I restored data before the year-end close and I verified the accounts were deleted during the GL close process and I found the support document below.

http://support.microsoft.com/kb/2452542

Posted in Dynamics GP | Leave a comment

Management Reporter Export Report Definition Listing to Excel

A client asked me if I can get a listing of report definitions out to excel so I contacted an MR colleague and here’s what she came up with that works well:

1. Open up Report Designer
2. Open up Report Definitions listing by choosing file>>open or Ctrl+O
3. Highlight the reports you want, to choose all click Ctrl+A
4. Choose copy – the only way is on the keyboard – Ctrl+C
5. Open excel and choose paste – Ctrl+V

It doesn’t export the column headers but that’s a minor issue

MR_ReportListing

Posted in Management Reporter | Leave a comment

How do I remove a budget password in Dynamics GP 2010

A client forgot the password for her Dynamics GP budget, here’s a SQL update statement to blank out the password, be sure not to put quotes around the password because it’s not a text field.  It should be run against your company database.

update GL00200
set BUDPWRD = 0x00202020202020202020202020202020
where BUDGETID =’Your budget ID’

After I ran this update statement my client was able to get into her budget just fine.

Here’s where I found the answer:

http://community.dynamics.com/gp/f/32/p/70117/128092.aspx

 

Posted in Dynamics GP | Leave a comment

Export group name in SSRS 2008 to worksheet tab name in excel

Recently, I tried to name a worksheet tab in excel from each group in SQL Reporting Services 2008.  I found that the group name needs to be entered into the tablix member group called “PageName”. The first screenshot shows the result, notice each worksheet tab coincides with the group name.

SSRS_ExportGroupName1

Here’s how to do it:

SSRS_ExportGroupName2

  1. After building your group report, click on the group name (Row Groups) in the bottom left of your screen, then go to the properties window.  If it’s not displaying like in the screenshot you can choose F4 or choose view>>properties window.  In the properties window, it should display “Tablix Member”.  It should not display “Tablix”, you might be at the properties of the whole report.
  2. Scroll down and expand on the Group section and find the field called PageName and enter the field value of the group name
  3. Try viewing the report then exporting it to excel to verify

Here are some websites to try if this didn’t help

http://reportingservicestnt.blogspot.in/2010/10/exporting-to-excel-and-renaming-sheets_26.html

http://blogs.msdn.com/b/robertbruckner/archive/2010/05/16/report-design-naming-excel-worksheets.aspx

http://msdn.microsoft.com/en-us/library/dd255234.aspx#WorksheetTabNames

 

 

Posted in SQL reporting | 3 Comments

Change fixed assets from fiscal to calendar year end

After changing the GL year end from fiscal to calendar on GP10sp2 by reopening all the history years with Microsoft support we found that fixed assets would not depreciate correctly because it was in the middle of a fiscal year even after we rebuilt the FA calendar. We tried resetting the year and recalc remaining life from mass change but nothing worked. We opened a Microsoft support case and here are the steps we took to resolve it, all of this being done in a test company.

1. Delete the GL fiscal period setup tables (delete SY40100 delete SY40101) from management studio. This is only in a test company, we will create an FA calendar script from it later.
2. Recreate the GL fiscal period setup window (in GL) – enter one full year on the old fiscal periods, then enter an 18 month year ended 12/31, then one year on a calendar basis. This 18 period fiscal year will overlap the current “Depreciated to Date” for all the assets on the old fiscal year end. For example, the depreciated to date was 10/31/12 and the fiscal year end was 6/30 so we entered the following (GL not fixed assets) in the fiscal period setup window:
a. 2012: 07/01/2011 – 06/30/2012
b. 2013: 07/01/2012 – 12/31/2013 (long year)
c. 2014: 01/01/2014 – 12/31/2014
3. Rebuild the fixed assets calendar – MS Dynamics>>tools>>utilities>>fixed assets>>build calendar (and replace). This will look at the (GL) fiscal period setup window and rebuild all the FA calendar where 2012 and prior will be on a fiscal basis, 2013 will be 18 periods, and 2014 and above will be on a calendar basis.
4. Run the attached script called “Get FA calendar Inserts” provided by support and run it against your test company only. The results of this script is what you need to save as a new script which deletes the FA calendar table and inserts the new FA calendar as described above. This script is mainly used to copy the FA calendar to other company databases which you can use if you have other companies that use fixed assets.
5. You may want to test depreciation by running “depreciate one asset” then verify it’s consistent with prior depreciation amounts through inquiry>>fixed assets>>financial detail window.
6. Test your FA calendar script – Refresh your test company, then run the script you saved from above
7. In the test company, run depreciation and verify all your reports agree to the general ledger for cost and accumulated depreciation.
8. Backup your live company, then run the script you created above in your live company and run depreciation and verify your reports agree to the general ledger for cost and accumulated depreciation.
9. Remember, during the FA GL posting routine, be sure you enter an ending date up to 18 periods through 12/31/13, then in January 2014 it will go back to a standard calendar where 1/31/2014 ending period for the FA general ledger posting window will be 2014-001.

10. If all your assets are set to depreciation based on “original life”, I recommend changing it to depreciate based on “remaining life”, otherwise all your assets will be fully depreciate.

11. Also, when you refresh the test company be sure to run the test company script, see the link below:

Here are some links to other reference information that might help:

–Guidelines for fiscal period/year changes with Fixed Assets in Microsoft Dynamics GP 10 and 2010
http://community.dynamics.com/product/gp/gptechnical/b/dynamicsgp/archive/2012/10/09/guidelines-for-fiscal-period-year-changes-with-fixed-assets-in-dynamics-gp-10-and-2010.aspx

–How to reset the life of an asset or of a group of assets in Fixed Asset Management in Microsoft Dynamics GP
https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;861544

–Calendar Script
Get_FA_Calendar_Inserts.sql

Posted in Dynamics GP | 4 Comments