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.

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.

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.

“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.

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.

 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