Financial Reports from Management Reporter do not match the General Ledger Trial Balance Reports in Microsoft Dynamics GP
Many different reporting tools can be used to prepare Financial reports used to balance to the GL Trial Balance Report in Microsoft Dynamics GP. So you must determine if your Financial reports are reading from GL data only, or if AA data is also being used. If AA data is being used to prepare the Financial reports, they may be skewed if the data in the AA tables does not match the data in the GL tables.
- Management Reporter with Data Mart will read from AA tables if AA has been activated. This is by design in versions up to CU9.
- Management Reporter with Legacy provider allows you to choose if you want to pull from an AA company or a GL company.
Financial reporting may be incorrect, if AA data was used to assemble the report, and your AA data does not match GL data. Records may be missing from AA tables, or may be skipped if they differ from the corresponding record in GL.
There are several reasons that the AA data may not match the GL data or have issues, including:
- The Analytical Accounting module has not been installed on all workstations, where users are keying/posting transactions. Therefore, the record exists in GL, but not AA.
- Users are disabling the Analytical Accounting module if they received any error referencing 'aa', so AA tables are not updated.
- Records are being imported directly into GL tables, and not AA tables. Or users are disabling AA if they receive any 'aa' errors during the import process.
- Users are closing the GL year on a workstation where AA has not been installed. As a result the AA records are not moved to history and no Balance Brought Forward journal entry is created in the AA tables.
- Management Reporter may not read the AA record if the currency ID differs between the AA record and the corresponding record in GL, which could be caused by importing records, activating Multicurrency, or any quality issues with currency ID's that have since been fixed.
- The Dynamics database was restored over itself, causing the 'Next Available Number' (in the AAG00102 table) to be set back to a value that has already been used. This system will increment off of this value for the next header ID value as it inserts new records into the table, and so it can duplicates if it is inserting in a heard ID value that has already been used.
- If you upgrade to Microsoft Dynamics GP 2013 and set a functional currency ID, but are not registered for Multicurrency, or you do not run check links as instructed after setting up the functional currency.
- Posting interruptions
If you need assistance to correct any of the results returned from the scripts below, please open a support incident using the categories below to reach the correct Support Engineer Team. Please include the information as noted within each step.
- To open a support incident online, use this link: https://mbs.microsoft.com/support/newstart.aspx
- Use the Support Topic: Financial - Analytical Accounting, and the Sub-Topic: Data Consistency Issues
- To reach the Message Center to open a support incident, call 1-888-477-7877. Please provide as many details as you can.
- Please note that the support incident may be time consuming so please allow ample time to work with the Engineer.
- The support incident will be chargeable unless the only cause is determined to be a known quality issue in Microsoft Dynamics GP. If multiple issues are addressed, the case will remain chargeable.
- The scope of the support incident will include fixing all the data returned by the SQL scripts below, within the guidelines of what we can do in a regular support case. If additional assistance is needed to find the 'root-cause', this will require a new support incident to be opened. Some of the causes are common as explained in the CAUSE section above. However, for example, if you determine a currency ID difference exists on records that were imported into Microsoft Dynamics GP, this would require a new support incident to be opened to work with the eConnect/Integration Manager Support Team. The engineer maintains the discretion to advise when a new support incident is needed or when it may become a consulting engagement.
- ONE support case can be opened initially, with the results of all of the scripts from steps 1-7 below. The Engineer will work with you in ONE company, and you will be expected to correct the other companies on your own, using the same steps as the Engineer provided you for the first company. Additional assistance with multiple companies may require a new case and is up to the Engineer's discretion.
STEPS:Please copy/download the SQL scripts below, and execute against the company database in SQL Server Management Studio. Data returned by any of these SQL scripts below will cause Financial Reports in MR-Data Mart not to balance with GL Trial Balance reports.
***Please make a current backup of the company database before running any scripts that will update data.***
See KB #871973 -- Set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, or SQL Server 2012
NOTE: To view the results of each script in Text, you can press ALT - T (or click on Query in the top menubar, point to Results To, and click on Results to Text) and then execute the script again. This will provide more details for each section of results returned.
STEP 1 - DISTINCT YEARS
Please run the scripts below to verify that the years are distinct in each table and do not overlap with each other, and also are consistent with the GL tables. If you find the same year listed in both tables or scrambled between the tables or not consistent with the years in the GL tables, please open a support case for further assistance. Please include the results of both scripts.
select distinct(YEAR1) from AAG30000 order by YEAR1select distinct(YEAR1) from AAG40000 order by YEAR1select distinct(OPENYEAR) from GL20000 order by OPENYEARselect distinct(HSTYEAR) from GL30000 order by HSTYEAR
STEP 2 - FIND CURRENCY ID DISCREPANCIESPlease download the AA_FindCurrencyDiscrepancy.sql script to identify transactions where the Currency ID does not match between GL and AA tables in both open and history:
*If you do not use Multicurrency, or just use only one currency ID, you can simply update the CURNCYID and CURRNIDX fields directly in the AA (AAG30001/AAG40002) or GL (GL20000/GL30000) tables. Test to make sure the currency ID is matching on recently keyed transactions to ensure there is not an ongoing issue. Please open a support incident if you need assistance to update the CURNCYID directly in the SQL table, or if you are able to recreate this on a current transaction.
*If Multicurrency is being used, please open a support incident and include the following:
- Provide the results of the script in text format. We need to know which table had the currency ID and which table did not. And are these older or newer transactions? If older transactions and you just installed Multicurrency Management, did you run check links on Multicurrency Setup? Check links should go back and update the currency ID on all existing transactions.
- What version of Microsoft Dynamics GP are you using?
- Where did the transactions come from? Test to see if this is an ongoing issue. Verify if Multicurrency Management is marked in the Registration window (under Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and click on Registration.)
- What is your functional currency? How many currency ID's do you use?
STEP 3 - FIND UNMATCHED DISTRIBUTIONSPlease download the AA_FindUnmatchedDists.sql script to identify if any detailed data does not match between the AA and GL tables. If any results are returned, please open a support case for further assistance. Please attach the file of results in text format. These problem records are usually due to importing issues, or users working on a workstation where AA was disabled or not installed.
NOTE: This script should not be used for versions lower than Microsoft Dynamics GP 10.0 SP2, or where you have not yet completed a GL year-end close on a version higher than that (ie. where pre-SP2 AA data has not yet been moved to history at all).
NOTE: Please be aware that 'result totals' may show in-between the sections and are misleading. These result totals are a factor of how the script was written and can be ignored. You only need to be concerned with 'result totals' that are immediately following a 'section' and returned actual data.
STEP 4 - FIND DUPLICATE AA CODE ID'SPlease download the AA_FindDupaaTrxDimCodeID_AAG00401.sql script to see if any AA code ID's are duplicated (due to being imported). If any results are returned, please open a support case for further assistance. Please attach the results of the script in text format, and also the contents of the AAG00401 table. We can provide you the steps to resolve this, but having Microsoft do this for you may be a consulting expense. This issue is usually caused by importing, or by restoring an older copy of the Dynamics database over itself.
STEP 5 - FIND DUPLICATE AA HEADER ID'SPlease execute the below script to determine if you have header ID's duplicated between the AA open and historical tables. We can provide the steps to resolve this, but having Microsoft do this for you may be a consulting expense. This issue is usually caused by importing, or by restoring an older copy of the Dynamics database over itself, or a posting interruption.
If any results are returned, please open a support case and include the following information:
select * from AAG30000 where aaGLHdrId in (select aagLHDrId from AAG40000)
- Results of the script in text format.
- Research the records and advise if they are the exact same record, or if they are different records, but just have the same Header ID only.
STEP 6 - FIND MISMATCHED SEQUENCE NUMBERSDownload and run the AA_FindYEC_RetainedEarnings.sql script to look for records where the Sequence Numbers do not match between AA and GL between open and history tables for the Retained Earnings journal entry. If any results are returned, please open a support case and include the results of the script in text format. (Note, if you had any results in Step 3 above for the 'GLSEQNMBR' sections, we will want to have you fix those first and then run this script again for step 6, so we don't need your results now for step 6 if you had results in Step 3.)
STEP 7 - FIND MISSING BBF ENTRIESIf you find that your Financial reports are off by the amount of the beginning balances, the Balance Brought Forward (BBF) entries may be missing from the AA tables. Please run the scripts below to determine if the BBF entries in GL are missing from AA tables. This is caused by the user closing the GL year on a workstation where AA was disabled or not installed. For Microsoft Dynamics GP 2013 SP2 and prior versions, you will need to have a consulting service opened to reopen the GL year. If you are Microsoft Dynamics GP 2013 R2 or later versions, you can use the "Reverse Historical Year' checkbox in the GL year-end closing routine to reopen the year, and then reclose it again on a machine where the AA code is installed, so the BBF's are created in both the AA and GL tables.
-----------------------------------------------------PRINT 'OPEN year missing BBF'Select distinct (JRNENTRY) from GL20000 where JRNENTRY not in (select JRNENTRY from AAG30000)and SOURCDOC in ('BBF','P/L') PRINT 'HISTORY year missing BBF'Select distinct (JRNENTRY) from GL30000 where JRNENTRY not in (select JRNENTRY from AAG40000)and SOURCDOC in ('BBF','P/L')----------------------------------------------------
STEP 8 - KNOWN ISSUE FOR AA BUDGETSIn Microsoft Dynamics GP 2013 and Microsoft Dynamics GP 2010, there was a known quality issue (#69354 TFS/69692 PS) where the top level of the budget tree will have an incorrect value in the AAG00902 table (aaLvlCodeString field) if you proceed to modify the budget. Management Reporter looks for a blank value in this field to identify the main record for the budget tree and so this incorrect value causes MR reports to fail and no AA budget data is returned. This issue was fixed in R2 for Microsoft Dynamics GP 2013 (12.00.1745). SQL scripts are provided below to execute against the company database to look for this issue and also to resolve it.
Select * from AAG00902 where aaLvlCodeString <> '' and aaLevel = 0
If the above script returns any results, you can resolve the issue by running this script against the company database:
update AAG00902 set aaLvlCodeString = '' where aaLevel = 0
STEP 9 - AAG30002/AAG40002 sum totals do not match AAG30001/AAG40001
The sum of the allocated records in the AAG30002/AAG40002 tables should match the sum of the amounts in the AAG30001/AAG40001 distribution tables per transaction. If the amounts are allocated to several AA codes, you can have a different number of records between these tables, but the important part is that they match in 'sum' total per transaction. MR reports will not read the records if the sum totals do not match between these tables for each transaction. Download the 'find and compare' scripts for the open and historical years below and execute against the company database.
(Note, if you had any results in Step 3 above for the 'AAG30002/AAG40002' sections, we will want to have you fix those first and then run this script again for step 9, so we don't need your results now for step 9 if you had results in Step 3.)
Open year AA tables:
Historical year AA tables:
If any results are returned, save them to an .rpt report and open a support case for further assistance. Attach the results for review.
STEP 10 - DEBITS/CREDITS ON SAME DISTRIBUTION LINE
If transactions are hand-keyed, Microsoft Dynamics GP would create separate distribution lines for debits and credits. However, we have been finding imported distribution records where the debit and credit is on the same record, and this causes issues with MR because it's a data condition that you cannot recreate by hand-keying a transaction. So if you have imported in records, you can execute the scripts below against the company database to look for this data-condition and would need to update either the debit or credit field directly in the SQL table with the 'net' amount as needed.
----------------------------------select JRNENTRY from GL10001 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0)select JRNENTRY from GL20000 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0)select JRNENTRY from GL30000 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0) --if using AA, also compare the AA tablesselect AAG0.JRNENTRY from AAG30001 AAG1join AAG30000 AAG0 on AAG0.aaGLHdrID = AAG1.aaGLHdrIDwhere ((AAG1.DEBITAMT <> 0 and AAG1.CRDTAMNT <> 0) or (AAG1.ORDBTAMT <> 0 and AAG1.ORCRDAMT <> 0)) and AAG1.SOURCDOC not in ('BBF','P/L') select AAG0.JRNENTRY from AAG40001 AAG1join AAG40000 AAG0 on AAG0.aaGLHdrID = AAG1.aaGLHdrIDwhere ((AAG1.DEBITAMT <> 0 and AAG1.CRDTAMNT <> 0) or (AAG1.ORDBTAMT <> 0 and AAG1.ORCRDAMT <> 0)) and AAG1.SOURCDOC not in ('BBF','P/L')-------------------------------------
STEP 11 - REBUILD THE DATA MART
After all of the results have been fixed above, rerun each script to make sure no results are no longer returned.
Then rebuild the Data Mart in Management Reporter and generate your Financial reports again. (With SQL Change Tracking, some changes may update automatically, but not all, and therefore we consider it best practice to rebuild the Data Mart if any changes were made directly in SQL to ensure all changes are recognized.) Here are the steps to do this (as a precaution, you can backup the DDM database, as this will be deleted and re-created):
1. Open the Configuration Console on the server from the Start menu.
2. In the left hand navigation pane, click ERP Integrations.
3. Click the integration being used (note the server name and the database names), and disable the integration with the “Disable Integration” button.
4. Click “Remove” to remove the ERP Integration.
5. Click the Management Reporter Services in the left hand navigation and stop both of the Management Reporter Services.
6. Delete the DDM database in SQL (you can make a backup first, if needed).
7. Start the Management Reporter Services.
8. Configure the ERP Integration again by going to “File > Configure”.
9. Once the setup is completed, enable the ERP integrations.
With the AA data corrected, and the DDM updated MR should pull in the correct values.
STEP 12 - VERIFY REPORT BALANCESDetemine if the report balances to the GL Trial Balance report or not. If not, see if you can determine what the difference is. Is the difference still related to the Balance Brought Forward Entries not being correct? If so, please open a support incident for further assistance.
The Year-End Closing procedures for AA can be found in the link below:
960356 - The Year-end close procedures for Analytical Accounting in Microsoft Dynamics GP
MR system adapter adaptor YEC year end yr-end yr end year-end DM DDM
Article ID: 2910626 - Last Review: 05/31/2016 20:38:00 - Revision: 21.0
Microsoft Dynamics GP 2015, Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2010, Microsoft Dynamics GP 10.0 Service Pack 4, Microsoft Dynamics GP 10.0 Service Pack 3, Microsoft Dynamics GP 10.0 Service Pack 2
- kbmbsmigrate kbsurveynew KB2910626