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.

Is your integration service running?

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.

 MRtrouble3

When this is not a very recent date/time the integration is not running. This should run every 45 seconds.

 

Find errors in the log files

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.

MRtrouble4

 

Problem synchronizing users

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.

MRtrouble5

 

“Object reference not set to an instance of an object”

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.

 

 Other database issues

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.

MRtrouble1

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.

MRtrouble2

 

 SQL scripts

--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!

Microsoft Dynamics AX CommunitySubscribe to this blogger RSS Feed

Comments11
  1. Fredy TanSeptember 06, 2015   

    Hi Andre,

    Thanks for posting this.
    We have a problem where MR integration seems to run forever. We checked the log, it seems MR is looping.
    We tried so many solutions recommended by other blogs but no success. Then we found your blog, run your script above and found one record from your script. After we “fix” the problematic record, rerun your script, the result is blank.
    However, when we re-run MR integration again, the result is still the same, the integration process seems to be looping.

    Have you encounter such issues? Any idea what else we should check?

    Thanks.

    Best regards,
    Fredy

    • André Arnaud de CalavonSeptember 07, 2015   

      Hi Fredy,

      What do you mean with “looping”? MR runs every 45 seconds to update transactional data between the AX database and the DataMart. In addition every 5 minutes more static data like users and security is synchronized.

  2. Ed LemireSeptember 09, 2016   

    Has anyone had an issue where the users in AX do not sync properly to management reporter? We can’t seem to add a new user. The user synchronization between management reporter and AX seems to stop working. The symptom is that new user added to AX with accounting-related roles have not been added to management reporter automatically. There is a blog about this problem or one very similar. We followed the instructions on this blog and removed all possible AD obsolete users from MR database last week. But the user sync problem still persists. Any suggestions?

    • André Arnaud de CalavonSeptember 13, 2016   

      Hi Ed,
      Thanks for reading the blog. Have you checked if there are any synchronization errors? If not, check the security role(s) of this particular user. You can get further assistance on e.g. the Dynamics community.

  3. JP de VilliersSeptember 26, 2016   

    Hi,
    Is there a way to see if MR is still updating after a massive data load (for example a consolidation in AX) and MR is not yet fully in sync with AX?
    Thank you
    JP

    • André Arnaud de CalavonSeptember 27, 2016   

      Hi JP,

      Thanks for reading the blog. You can monitor if you continue to see new events coming in the Data Mart integration log. It is not possible to see what it is doing exactly on a certain point in time.

  4. MarkOctober 06, 2016   

    Hi Andre,

    The script (loops through all companies to identify null system dimensions) gave me some result.
    Honestly, i have no idea to fix it? Deleting it would cause any other problem? Is there anyway to fix it?

    Thank you very much
    Mark

    • MarkOctober 06, 2016   

      The screenshot of result is added in URL, please click to my name to see the picture

      • André Arnaud de CalavonOctober 21, 2016   

        Hi Mark,

        If you have created a question on the Dynamics community, it would be better to continue on the community forum where additional information can be provided. From these blog comments I can see you referred to the community website, but cannot see any details.

  5. RCOctober 19, 2016   

    Hi Mark,

    After installing management reporter CU15 for AX 2012 I am not able to see the AX companies. Refresh companies do not work. What do you reckon is the issue?

    • André Arnaud de CalavonOctober 21, 2016   

      Hi RC,

      You can try to recreate the datamart database in this scenario.

Leave a Comment!

Your email address will not be published. Required fields are marked *