Management Reporter Integration Trouble Shooting Queries.

For troubleshooting I got some SQL scripts from Microsoft support in the recent past. You can try to see if one of those will give some results. Then you can focus on the errors. Hope this will help you now, otherwise you might use them in future.

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