Sometimes people have troubles with their Management Reporter integration with AX 2012. I also encountered some problems in the past. If the synchronization is not working, or not working correctly, it could be related to data issues in your Microsoft Dynamics AX environment. In this post I will share some ways to find and solve some problems.
When you notice amounts are incorrect you can first have a look if the integration service is running and the last date/time when it has been updated. You can do this using the Management Reporter 2012 configuration console.
When this is not a very recent date/time the integration is not running. This should run every 45 seconds.
In the Management Reporter 2012 configuration console you can view the log files created by e.g. the data mart integration. Check if there are no errors. If there are errors they need to be resolved. There are multiple causes. Some of them are described below.
If the error details mentions you have an issue synchronizing users due to the license, you have installed Management Reporter, but not registered your license code. To solve this you need to get your license code. Then open the Report Designer application. Go to Tools and select the option Registration. The registration form opens. You can paste the License key in this form. Validate if it is correct and the click OK. If you have not installed the license, Management reporter is running in a trial/demo mode.
If you have this error, there is a page on Microsoft support to help you. You can click here to go to this page. The provided solution did not work for me in a particular scenario, but it guided me to solve it. The error is related having a value of an enumeration that does not exists. The resolution provided guides you to solve a problem related to the field Posting type. In my case due to a conversion of main accounts from a previous version a wrong value was used in the Main account type. You can use the resolution provided by Microsoft Support also with other enumeration fields.
If there are some other issues in the error log, you can try to find and solve them using SQL scripts. In one particular scenario I got some scripts from Microsoft Support with SQL queries which can find data which causes the synchronization errors and also even could create inconsistent data mart data. You can find the scripts below.
You need to have access to the database to run these queries. SQL Management studio is commonly used to run the scripts. In the Management studio you can select the Dynamics AX database and then click New Query. An empty Query form will be opened.
Now copy the scripts (can be found below) and paste it in the Query window. Then click the button Execute and wait for the results. If there are problems data will be shown in the results window. In this particular example there are some accounting entry records where the header is missing. You then need to investigate why the header is missing. This could be related to a wrong customization or manual data correction. If you know what caused it, you can also solve the error by e.g. recreating the header record.
Per query, at least a header is shown. So to know which table causes the error we have to find (in this case) the fourth query and try to understand what the query is showing. In this example it will show records from the GeneralJournalAccountEntry table where no record in the table GeneralJournalEntry exists.
--budgets assigned to invalid company select * from BUDGETTRANSACTIONLINE l with (nolock) join BUDGETTRANSACTIONHEADER h with (nolock) on l.BUDGETTRANSACTIONHEADER = h.RECID where h.PRIMARYLEDGER not in (select recid from ledger) --bad category assigned to an account that has transactions --Script start select MA.MAINACCOUNTID, MA.NAME, L.NAME as CompanyID from MAINACCOUNT MA with (nolock) join DIMENSIONATTRIBUTEVALUE DAV with (nolock) on DAV.ENTITYINSTANCE = MA.RECID join LEDGER L with (nolock) on L.CHARTOFACCOUNTS = MA.LEDGERCHARTOFACCOUNTS where MA.ACCOUNTCATEGORYREF not in (select ACCOUNTCATEGORYREF from MAINACCOUNTCATEGORY) and DAV.ISTOTAL = 0 and MA.ACCOUNTCATEGORYREF <> 0 and MA.MAINACCOUNTID in ( select DISTINCT MA.MAINACCOUNTID from GENERALJOURNALACCOUNTENTRY GJAE with (nolock) join DIMENSIONATTRIBUTEVALUECOMBINATION DAVC with (nolock) on DAVC.RECID = GJAE.LEDGERDIMENSION join MAINACCOUNT MA with (nolock) on DAVC.MAINACCOUNT = MA.RECID ) order by MA.MAINACCOUNTID --Script end --transactions with an invalid company select * from GeneralJournalEntry with (nolock) where ledger not in (select distinct recid from ledger) --transaction details with no associated transaction header information select * from GeneralJournalAccountEntry with (nolock) where GeneralJournalentry not in (select distinct RECID from GeneralJournalEntry with (nolock)) --invalid dimension combinations select * from GENERALJOURNALACCOUNTENTRY GJAE with (nolock) where GJAE.LEDGERDIMENSION not in (select distinct VALUECOMBINATIONRECID from DIMENSIONATTRIBUTELEVELVALUEVIEW with (nolock)) --Custom list financial dimension values select * from DIMENSIONFINANCIALTAG with (nolock) where VALUE is NULL --loops through all companies to identify null system dimensions --Script start DECLARE @TableName nvarchar(40) DECLARE @SQL nvarchar(max) DECLARE tables_cursor CURSOR FAST_FORWARD FOR select distinct DA.VIEWNAME from DIMENSIONATTRIBUTE DA where DA.TYPE <> 3 and viewname <> 'DimensionFinancialTag' order by DA.VIEWNAME OPEN tables_cursor FETCH NEXT FROM tables_cursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Checking ' + @TableName + ' table...' SELECT @SQL = 'SELECT VALUE, NAME, ''' + @TableName + ''' as TableName FROM ' + @TableName + ' WHERE NAME=''''' EXEC (@SQL) FETCH NEXT FROM tables_cursor INTO @TableName END CLOSE tables_cursor DEALLOCATE tables_cursor --Script end
That’s all for now. Till next time!