Dynamics GP: Resolving Discrepancies Between Management Reporter and General Ledger

Table of Contents

Accurate financial reporting is paramount for sound business decisions. In Microsoft Dynamics GP, various reporting tools are employed to generate financial statements that are intended to reconcile with the General Ledger (GL) Trial Balance Report. A critical distinction when preparing these reports lies in whether they exclusively draw from GL data or if Analytical Accounting (AA) data is also incorporated. If Analytical Accounting data is utilized, reports can become skewed if the information within the AA tables does not precisely align with the corresponding data in the GL tables.

Management Reporter, when configured with Data Mart, is designed to read from AA tables if the Analytical Accounting module has been activated. This behavior is standard in versions up to Cumulative Update 9. Conversely, when Management Reporter operates with a Legacy provider, it offers the flexibility to choose whether to pull data from an AA-specific company or directly from a GL company. Understanding these underlying data sources is the first step in diagnosing any reporting discrepancies.

Symptoms of Data Mismatches¶

Financial reports may present inaccurate figures if they were compiled using Analytical Accounting data that does not match the General Ledger. Such discrepancies arise when records are either missing from AA tables or are bypassed due to inconsistencies with their corresponding GL entries. The integrity of financial reporting hinges on the seamless synchronization between these modules.

A common symptom is that the Management Reporter report totals do not agree with the GL Trial Balance. This often manifests as differences in beginning balances, transaction amounts, or year-end closing entries. Users might notice that specific accounts or dimensions appear to have incorrect balances when cross-referencing between the reporting tool and Dynamics GP’s native GL inquiries. These variances can lead to distrust in financial statements and complicate the audit process.

Root Causes of Discrepancies¶

Several factors can contribute to the misalignment between Analytical Accounting and General Ledger data, leading to inaccurate financial reporting. Identifying the specific cause is crucial for effective resolution. These issues often stem from installation oversights, user actions, data import practices, or system-level inconsistencies.

One prevalent cause is the incomplete installation of the Analytical Accounting module across all workstations where users process and post transactions. If AA is not universally installed, transactions keyed and posted on workstations lacking the module will exist in the GL but fail to create corresponding entries in the AA tables. This immediately creates a data gap. Another common scenario involves users intentionally disabling the Analytical Accounting module when encountering ‘aa’-related errors during transaction entry or posting. This action prevents AA tables from being updated, despite the GL tables receiving the data.

Importing records directly into GL tables without properly considering AA integration is another significant cause of discrepancies. Some users may also disable AA during import processes if they encounter errors, inadvertently bypassing the AA update mechanism. Similarly, if the GL year-end close routine is performed on a workstation where the Analytical Accounting module is not installed, the historical AA records may not be correctly moved, and no Balance Brought Forward (BBF) journal entry will be created in the AA tables. This results in incorrect opening balances for the new fiscal year within AA.

Currency ID mismatches can also lead to reporting inaccuracies. Management Reporter might fail to read an AA record if its currency ID differs from the corresponding GL record. This situation can arise from importing records with inconsistent currency data, the activation of Multicurrency functionality after transactions have been posted, or historical quality issues related to currency IDs that have since been rectified through product updates. Furthermore, restoring a Dynamics database over itself can cause the ‘Next Available Number’ in the AAG00102 table to revert to a value that has already been used. This can lead to duplicate header IDs when new records are inserted, corrupting data integrity.

Issues during an upgrade, such as to Microsoft Dynamics GP 2013, where a functional currency ID is set without registering for Multicurrency or failing to run check links as instructed, can also introduce data discrepancies. Posting interruptions, often due to system crashes or network issues during the transaction commit process, can leave records in an inconsistent state, where GL is updated but AA is not, or vice versa. Addressing these root causes requires a systematic approach, often involving SQL scripts and meticulous data validation.

Comprehensive Resolution Steps¶

Resolving data discrepancies between Management Reporter and General Ledger in Dynamics GP requires a structured approach, often involving direct database queries and specific system procedures. It is highly recommended to engage with professional support if you are uncomfortable executing SQL scripts or modifying database records. A thorough investigation ensures all related issues are identified and rectified, restoring the accuracy of your financial reports.

To begin the resolution process, it is imperative to secure a recent backup of your company database. This precaution ensures that you can revert to a stable state if any unforeseen issues arise during the data correction process. Additionally, setting up a test company with a copy of your live company data is strongly advised. This environment allows you to execute and validate all corrective scripts and procedures without risking your production data, ensuring the proposed solutions work as intended before implementation in the live system. This iterative testing approach minimizes disruption and validates the integrity of the data corrections.

Initial Preparations¶

Before running any SQL scripts that might modify data, a crucial step is to create a current backup of your company database. This provides a safety net, allowing you to restore your system to its previous state if any issues occur during the remediation process. It is an industry best practice for any direct database manipulation.

Furthermore, it is highly recommended to work in a test company environment. You should set up a test company that contains a complete copy of your live company data. This allows you to run all diagnostic and corrective scripts without affecting your production system. By validating the fixes in a test environment, you can ensure that the resolutions achieve the desired outcome before applying them to your live company, minimizing business disruption.

Step 1 - Distinct Years Validation¶

The first step in diagnosing data consistency involves verifying the distinct years recorded in both Analytical Accounting and General Ledger tables. It is crucial that the years are properly segregated between open and historical tables and do not overlap. Specifically, the AAG30000 table (AA Open) should align with GL20000 (GL Open), and AAG40000 (AA History) should align with GL30000 (GL History). No year should appear in both open and historical tables within either AA or GL.

Execute the following SQL queries against your company database to check for distinct years:

select distinct(YEAR1) from AAG30000 order by YEAR1
select distinct(YEAR1) from AAG40000 order by YEAR1
select distinct(OPENYEAR) from GL20000 order by OPENYEAR
select distinct(HSTYEAR) from GL30000 order by HSTYEAR

If these scripts reveal any overlapping years, years scrambled between tables, or inconsistencies with the GL tables, it indicates a significant data integrity issue. Such findings necessitate further investigation, often requiring professional assistance to untangle the historical data and ensure proper year-end close processing. Document these results thoroughly for any subsequent support engagements.

Step 2 - Finding Currency ID Discrepancies¶

Currency ID discrepancies between GL and AA tables can significantly affect multi-currency reporting and reconciliation. These mismatches can occur in both open and historical transactions, leading to reporting inaccuracies in Management Reporter. Identifying these specific transactions is key to correcting the financial data.

To identify transactions where the Currency ID does not match between GL and AA tables, use the AA_FindCurrencyDiscrepancy.sql script. If you do not use Multicurrency or only use a single currency ID, you might be able to directly update the CURNCYID and CURRNIDX fields in the AA (AAG30001/AAG40002) or GL (GL20000/GL30000) tables. However, it is essential to first test recent transactions to ensure the currency ID is correctly matching, indicating that the issue is not ongoing. If you need assistance with direct SQL table updates or can reproduce the issue on current transactions, professional support is recommended.

If Multicurrency is actively used in your environment, direct modification of currency IDs is not advised without expert guidance. In such cases, providing a text format of the script results is crucial, detailing which table possessed the currency ID and which did not. Further details, such as transaction age, your Microsoft Dynamics GP version, transaction origin, and your functional currency setup, will help diagnose the root cause, especially regarding Multicurrency Management registration and check links execution after setup.

Step 3 - Finding Unmatched Distributions¶

Unmatched distributions signify a critical break in data integrity where detailed transactional data in Analytical Accounting does not correspond with the General Ledger. This issue frequently arises from problematic data import processes or instances where users processed transactions on workstations where the Analytical Accounting module was either disabled or not properly installed. Such discrepancies mean that while the GL entry exists, its detailed AA allocations are missing or incorrect, leading to reporting inaccuracies.

Utilize the AA_FindUnmatchedDists.sql script to identify any detailed data that fails to match between the AA and GL tables. If any results are returned, these problem records are strong indicators of underlying integration or installation issues. It is important to note that this script is specifically designed for Microsoft Dynamics GP 10.0 SP2 and higher versions, and should not be used if you have not completed a GL year-end close on a version higher than SP2, as pre-SP2 AA data might not have been moved to history yet. Be aware that the script may show “result totals” within sections that are misleading; only focus on the totals immediately following a section that returned actual data. If results are found, attach a text file of these results when opening a support case for detailed analysis.

Step 4 - Finding Duplicate AA Code IDs¶

Duplicate Analytical Accounting code IDs can disrupt the integrity of your AA data, often stemming from data import activities or scenarios where an older copy of the Dynamics database was restored over itself. These duplicates can cause reporting tools like Management Reporter to misinterpret or omit data, leading to incomplete or incorrect financial views. Identifying these specific duplications is a key step towards restoring data accuracy and ensuring proper analytical reporting.

Execute the AA_FindDupaaTrxDimCodeID_AAG00401.sql script to detect any duplicated AA code IDs. Should this script return any results, it strongly suggests a data corruption issue within the AAG00401 table. Resolving these duplicates typically involves direct database manipulation to eliminate redundant entries, which can be complex and may incur consulting expenses if done by Microsoft. When seeking support, attach the script results in text format, along with the complete contents of your AAG00401 table, to facilitate a comprehensive assessment and guidance on resolution steps.

Step 5 - Finding Duplicate AA Header IDs¶

Duplicate Analytical Accounting header IDs (aaGLHdrID) between the open and historical AA tables can severely compromise data integrity and lead to reporting inaccuracies. This issue commonly arises from data import errors, the restoration of an older Dynamics database copy over a newer one, or interruptions during the posting process. Such duplicates mean that a single transaction might incorrectly appear in both open and historical records, or that distinct transactions share the same unique identifier, confusing reporting tools.

Execute the following SQL queries to determine if you have header IDs duplicated between your AA open (AAG30000) and historical (AAG40000) tables:

select aaGLHdrID from AAG30000 where aaGLHdrId in (select aagLHDrId from AAG40000)

Alternatively, you can run this script to see more detail:

select a.aaGLHdrID, a.JRNENTRY as OPEN_JE#,a.YEAR1 as 'OPEN_YEAR1',
b.JRNENTRY as 'HIST_JE#', b.YEAR1 as 'HIST_YEAR1' From AAG30000 a (nolock)
join AAG40000 b (nolock) on a.aaGLHdrID = b.aaGLHdrID
where a.JRNENTRY <> b.JRNENTRY

If either script returns results, it signifies a problem requiring attention. Provide the script results in text format and perform research on the identified records to determine if they are identical transactions appearing in both tables or different transactions merely sharing the same header ID. While steps to resolve this can be provided, engaging Microsoft for direct resolution may be considered a consulting expense due to the complexity and potential for data loss if not handled carefully.

Step 6 - Finding Mismatched Sequence Numbers for P&L Entries¶

Mismatched sequence numbers for Profit and Loss (P&L) entries, particularly those related to Retained Earnings (SOURCDOC ='P&L'), can cause reconciliation issues between Analytical Accounting and General Ledger data. This problem occurs when the SEQNUMBR (sequence number) field does not align between corresponding records in the open and history tables of AA and GL. Such discrepancies can lead to incorrect P&L balances appearing in Management Reporter, especially after a year-end close.

Run the AA_FindYEC_RetainedEarnings.sql script to identify records where sequence numbers do not match between AA and GL for Retained Earnings journal entries. If you had results from Step 3 for the ‘GLSEQNMBR’ sections, it is advisable to resolve those first before re-running this script for Step 6, as those core discrepancies might cascade and obscure the P&L specific issues. If results are still returned after addressing Step 3, save them in text format and include them in any support case.

It is important to note that you might receive results from this script even if the underlying data is valid, particularly if you have P&L entries with multiple distribution lines sharing the same ACTINDX. In such cases, carefully compare the SEQNUMBR values between AAG30001 and GL20000 (or AAG40001 and GL30000) for the returned results. If the SEQNUMBR values match, you can typically disregard the Management Reporter validation errors for those specific entries and proceed with other fixes.

Step 7 - Finding Missing BBF Entries¶

If your financial reports exhibit discrepancies in beginning balances, it is highly probable that the Balance Brought Forward (BBF) entries are missing from your Analytical Accounting tables. This common issue arises when the General Ledger year-end close routine is performed on a workstation that does not have the Analytical Accounting module installed or enabled. Consequently, while the BBF entries are correctly created in the GL, they fail to generate corresponding entries in AA, leading to misaligned starting balances.

Execute the following SQL scripts to determine if BBF entries present in GL are absent from AA 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')

For Microsoft Dynamics GP 2013 SP2 and earlier versions, resolving missing BBF entries in AA typically requires a consulting service to reopen the GL year, allowing for a proper re-close on a machine with AA installed. For Microsoft Dynamics GP 2013 R2 and later versions, a more streamlined solution exists: you can utilize the Reverse Historical Year checkbox within the GL year-end closing routine to temporarily reopen the year. Subsequently, reclose the year on a workstation where the AA code is fully installed. This ensures that the essential BBF entries are correctly created in both the Analytical Accounting and General Ledger tables, rectifying the beginning balance discrepancies.

Step 8 - Known Issue for AA Budgets (GP 2010 and GP 2013 only)¶

A specific quality issue, identified as #69354 TFS/69692 PS, affected Microsoft Dynamics GP 2010 and Microsoft Dynamics GP 2013 regarding Analytical Accounting budgets. This issue caused the top level of the budget tree to have an incorrect value in the aaLvlCodeString field within the AAG00902 table after modifying the budget. Since Management Reporter relies on a blank value in this field to correctly identify the main record for the budget tree, the erroneous value prevented AA budget data from being returned in MR reports.

This particular bug was resolved in Microsoft Dynamics GP 2013 R2 (version 12.00.1745). If you are using an affected version, execute the following SQL script against your company database to check for this issue:

Select * from AAG00902 where aaLvlCodeString <> '' and aaLevel = 0

If the script returns any results, it confirms the presence of this known issue. You can resolve it by running the following update script against your company database:

update AAG00902 set aaLvlCodeString = '' where aaLevel = 0

Executing this update will set the aaLvlCodeString field to a blank value for the top-level budget entries, allowing Management Reporter to correctly interpret and display your Analytical Accounting budget data. This simple fix can significantly improve the accuracy of budget versus actuals reporting.

Step 9 - AAG30002/AAG40002 Sum Totals Don’t Match AAG30001/AAG40001¶

Data integrity within Analytical Accounting requires that the sum of allocated amounts in the AAG30002 (open) and AAG40002 (history) tables precisely matches the sum of amounts in their respective distribution tables, AAG30001 (open) and AAG40001 (history), for each transaction. While the number of records might differ if amounts are allocated across multiple AA codes, the critical aspect is that the sum totals for a given transaction must align between these sets of tables. If these sum totals do not match, Management Reporter reports will fail to read the records correctly, leading to omissions and inaccurate financial statements.

Before proceeding with this step, if you encountered any results in Step 3 for the ‘AAG30002/AAG40002’ sections, it is essential to rectify those issues first. Once addressed, rerun the appropriate scripts for Step 9 to ensure no new discrepancies have emerged. For the current open Analytical Accounting year, use the AA_CompareAAG30001AndAAG30002SummaryValues.sql script. For historical data, utilize the AA_CompareAAG40001AndAAG40002SummaryValues.sql script. Execute these scripts against your company database.

Should either of these scripts return any results, it indicates a discrepancy in the sum totals between the detailed allocation and summary distribution tables. Save the results to an .rpt file. This file, containing the specific transactions with sum total mismatches, is vital for further analysis. Open a support case and attach these results for review, as professional assistance may be required to correct these complex data inconsistencies, which often stem from partial updates or data corruption.

Step 10 - Debits/Credits on Same Distribution Line¶

A common issue that prevents Management Reporter from accurately reading financial records occurs when both the debit and credit fields are populated on the same distribution line. This scenario contradicts standard accounting principles, where a single line should typically represent either a debit or a credit. Known causes for this anomaly include importing records in such a condition or using the Standard Cost Utility in Inventory with an override that incorrectly populates both fields. Such records can lead to distorted financial figures in reports.

To identify these problematic records, run the following SQL query scripts against your company database. These queries will scan the General Ledger work, open, and historical tables, as well as the Analytical Accounting tables, for entries where both debit and credit amounts are non-zero.

select JRNENTRY,CRDTAMNT,DEBITAMT,ORCRDAMT,ORDBTAMT,* from GL10001 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0)
select JRNENTRY,CRDTAMNT,DEBITAMT,ORCRDAMT,ORDBTAMT,* from GL20000 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0)
select JRNENTRY,CRDTAMNT,DEBITAMT,ORCRDAMT,ORDBTAMT,* from GL30000 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0)
--if using AA, also compare the AA tables:

select AAG0.JRNENTRY  from AAG30001 AAG1
join AAG30000 AAG0 on AAG0.aaGLHdrID = AAG1.aaGLHdrID
where ((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 AAG1 join AAG40000 AAG0 on AAG0.aaGLHdrID = AAG1.aaGLHdrID
where ((AAG1.DEBITAMT <> 0 and AAG1.CRDTAMNT <> 0)
or (AAG1.ORDBTAMT <> 0 and AAG1.ORCRDAMT <> 0))
and AAG1.SOURCDOC not in ('BBF','P/L')

To rectify these records, you will need to directly update the CRDTAMNT, DEBITAMT, ORCRDAMT, and ORDBTAMT fields in the relevant GL/AA SQL tables. The goal is to “net” the amount on either the debit or credit field, ensuring that only one of these fields contains a non-zero value for a given line. It is highly recommended to correct each record manually, one by one, using its Dex_Row_Id to ensure precision and prevent unintended data changes. If a large volume of records requires correction, consulting your Microsoft Partner for assistance with mass updates via scripts is advisable, as this can be a complex task with significant data implications.

Step 11 - Rebuild the Data Mart¶

After successfully completing all necessary data corrections based on the previous steps, the final crucial action is to rebuild the Data Mart in Management Reporter. Rerun each diagnostic SQL script from the previous steps to confirm that no discrepancies are returned. Although SQL Change Tracking can automatically update some changes, it does not cover all direct SQL modifications. Therefore, rebuilding the Data Mart is considered a best practice to ensure that all corrected data is fully recognized and synchronized within Management Reporter.

As a precautionary measure, it is advisable to back up your Data Mart (DDM) database before proceeding, as it will be deleted and recreated during this process. Follow these detailed steps to rebuild the Data Mart:

1. Open the Configuration Console on the server where Management Reporter is installed, typically accessible from the Start menu.
2. In the left-hand navigation pane, select ERP Integrations.
3. Identify the integration currently in use, noting its server name and database names for later re-configuration. Then, click the Disable Integration button to temporarily stop data flow.
4. Once disabled, select Remove to remove the existing ERP Integration configuration. This action prepares the system for a fresh Data Mart creation.
5. Navigate to Management Reporter Services in the left-hand navigation pane and stop both of the Management Reporter Services to ensure no active connections interfere with the database deletion.
6. In SQL Server Management Studio, delete the existing DDM database. Remember to create a backup beforehand if you opted for this precaution.
7. Return to the Management Reporter Configuration Console and restart both of the Management Reporter Services.
8. Reconfigure the ERP Integration by going to File > Configure. Follow the on-screen prompts to set up the integration using the server and database names noted in step 3.
9. Once the setup is complete, enable the ERP integrations. This action will trigger the Data Mart to rebuild, pulling in the newly corrected Analytical Accounting and General Ledger data.

With the Analytical Accounting data corrected in the source database and the Data Mart fully updated, Management Reporter should now accurately reflect the correct financial values in your reports.

Step 12 - Verify Report Balances¶

The final and most critical step is to thoroughly verify that your Management Reporter reports now accurately balance to the General Ledger Trial Balance report. Generate your financial reports in Management Reporter and compare them rigorously against the corresponding GL Trial Balance directly from Dynamics GP. This direct comparison is essential to confirm that all previous data correction efforts have yielded the desired outcome.

Carefully examine all key financial figures, including revenues, expenses, assets, liabilities, and equity, ensuring they align precisely. Pay particular attention to beginning balances and year-end closing entries, as these are common areas for residual discrepancies, especially if related to Balance Brought Forward (BBF) entries. If, after this comprehensive review, the reports still do not balance, or if you can pinpoint a specific difference, such as an ongoing issue with BBF entries, it indicates that further investigation is required. In such cases, promptly open a new support incident, providing all relevant details and the results of your latest verification, to receive additional assistance in pinpointing and resolving any remaining inconsistencies.

Further Assistance and Considerations¶

Should you require professional assistance to rectify any results returned from the SQL scripts, it is advisable to open a support incident. When contacting support, ensure you use the appropriate categories, such as “Financial - Analytical Accounting” and “Data Consistency Issues,” to direct your query to the correct Support Engineer Team. Provide as many details as possible from your diagnosis and the results of the scripts.

Be aware that resolving complex data consistency issues can be time-consuming, so allocate ample time to work with the assigned Engineer. Support incidents of this nature are typically chargeable, unless the sole cause is definitively identified as a known quality issue within Microsoft Dynamics GP. If multiple issues are addressed during a single incident, the case will generally remain chargeable. The scope of a standard support incident will cover fixing data returned by the provided SQL scripts within established guidelines. However, if additional assistance is needed to determine the “root cause” of the discrepancies (e.g., if currency ID differences are linked to eConnect/Integration Manager imports), a new support incident might be required. The Engineer retains discretion to advise when a new incident or a consulting engagement is more appropriate. For initial support, one case can cover the results of all 10 steps for one company, with the expectation that you apply the learned steps to other companies independently. Additional assistance for multiple companies may require a new case, subject to the Engineer’s discretion.

Conclusion¶

Ensuring the accuracy and consistency of financial data between Analytical Accounting and the General Ledger in Microsoft Dynamics GP is vital for reliable reporting through Management Reporter. The systematic approach outlined, from identifying symptoms and root causes to executing specific SQL diagnostic scripts and rebuilding the Data Mart, provides a robust framework for resolving common discrepancies. While many issues can be addressed with careful troubleshooting, complex scenarios often benefit from professional guidance.

Have you encountered similar data discrepancies in your Dynamics GP environment? What challenges did you face, and what steps proved most effective in resolving them? Share your experiences and insights in the comments below, or discuss any aspects of these resolution steps. Your contributions can help the community navigate these common reporting hurdles.