How to correct data errors that prevent Management Reporter integration

Summary
This article provides steps to run a Diagnostics.sql script for Management Reporter 2012 for Microsoft Dynamics SL 2011. This script can identify data errors that may prevent successful Management Reporter integration. Data errors can originate from many sources and may cause inconsistency between tables or create invalid field values. 

At the bottom of this article there are also a couple scripts used to find lowercase data that may cause issues in MR.
Resolution
Download the following script file, and then follow these steps to run the scripts against the Microsoft Dynamics SL Application database in Management Reporter. Change the output type to Text Output for easier viewing.

Note This same information is contained in the Diagnostics2.Sql file, which is included in the MR2.zip file.

Important Always make a database backup before you update any data in Management Studio.

Step 1

  • Do you have more than one currency ID set up?

    Select 'Step1a', CASE WHEN COUNT(*)>1 then 'YES' else 'NO' end, count(*) from currncy with (nolock)

    Notes
    • No = single currency
    • Yes = multiple currencies that are set up in Currency Maintenance and have the number listed
  • Is multicurrency enabled?

    Select top 1 'Step1b',case isnull((select MAX(mcactivated) from cmsetup),-99) WHEN 1 then 'ENABLED' when -99 then 'NOT SETUP' else 'NOT ENABLED' end

    Note This script returns either "Enabled" or "Not Set Up."

Step 2

Look for a bad currency setup record. Evaluate each situation. For help to do this, contact Microsoft Dynamics Technical Support.
  • This should be the base currency of the application database:

    Select 'Step2a',BaseCuryId from glsetup with (nolock)

  • The Base currency of the Actual ledger (balance type = 'a') should be the base currency of the database.

    Select 'Step2b',BaseCuryID,LedgerID, BalanceType from ledger with (nolock)
  • The BaseCuryID field of all companies in a given application database should all be the same.

  • This script shows the BaseCuryID field of all application databases that are associated with the System database.

    Select 'Step2c',BaseCuryID, CpnyID, databasename from vs_company with (nolock)
  • The BaseCuryIDs field should exist in the currency maintenance table. Any foreign currencies should also exist in this table.

    Select 'Step2d',curyid from currncy with (nolock)

Step 3

Look for incorrect values for the CuryID and ledgerID fields in the transaction and summary records. These statements access only the top 100 records. To see all records, remove the words "top 100" from the script.
  • Accthist that has a currency ID that is not in the currency maintenance table.

    Select top 100 'Step3a',CpnyID, LedgerID, FiscYr, Acct, SUB, CuryId, * from accthist with (nolock) where CuryId not in (select CuryId from Currncy with (nolock))

    Fix: You may need to update the CuryID field to match the BaseCuryID field. However, that record may already exist. In this case, you receive a 6909 message. If this occurs, determine whether the record is required.

  • Accthist that has a currency ID that does not match the ledger record (or for a ledger that is not in the ledger table).

    Select top 100 'Step3b',a.CpnyID, a.LedgerID, a.FiscYr, a.Acct, a.SUB, a.CuryId, l.BaseCuryID,* from accthist a with (nolock) left join Ledger l with (nolock) on a.LedgerID=l.LedgerID where l.LedgerID is null OR l.BaseCuryID<>a.CuryId

    Fix: The update depends on each situation.

  • CuryAcct that has a CuryID not in the currency maintenance table.

    Select top 100 'Step3c',CpnyID, LedgerID, FiscYr, Acct, SUB,BaseCuryID, CuryId, * from curyacct with (nolock) where CuryId not in (select CuryId from Currncy with (nolock))

    Fix: The update will depend on each situation. You may have to delete the record later if there is no corresponding AcctHist record. Also, you may have to update the CuryID field. However, that record may already exist. In this case, you receive a 6909 message. If this occurs, determine whether the record is needed.

  • CuryAcct with a BasecuryID that does not match the ledger record (or for a ledger that is not in the ledger table).

    Select top 100 'Step3d',c.CpnyID, c.LedgerID, c.FiscYr, c.Acct, c.SUB,c.BaseCuryID, c.CuryId,l.BaseCuryID, * from curyacct c with (nolock) left join Ledger l with (nolock) on c.LedgerID=l.LedgerID where l.LedgerID is null OR c.BaseCuryID<>l.BaseCuryId

    Fix: The update depends on each situation.

  • GLTran with a CuryID not in the currency maintenance table.

    Select top 100 'Step3e',CpnyID, LedgerID, FiscYr, Acct, SUB,BaseCuryID, CuryId,* from gltran with (nolock) where Posted='P' and CuryId not in (select CuryId from Currncy)

    Fix: The update depends on each situation. You may need to update the CuryId field to a valid value.

  • GLTran with a BaseCuryID field value that does not match the ledger record (or for a ledger that is not in the ledger table).

    Select top 100 'Step3f',g.CpnyID, g.LedgerID, g.FiscYr, g.Acct, g.SUB, g.BaseCuryID, g.CuryId, * from gltran g with (nolock) left join Ledger l with (nolock) on g.LedgerID=l.LedgerID where g.Posted='P' and g.LedgerID is null OR l.BaseCuryID<>g.BaseCuryId

    To fix: Update will depend on each situation. You may need to update the BaseCuryID field to a valid value.

Step 4

Look for mismatched summary records.
  • Find CuryAcct records without AcctHist records.

    Select top 100 'Step4a',c.CpnyID, c.LedgerID, c.FiscYr, c.Acct, c.Sub, c.BaseCuryID, c.CuryId,*from CuryAcct c with (nolock)left join AcctHist a with (nolock) on c.CpnyID=a.CpnyID and c.Acct=a.Acct and c.Sub=a.Sub and c.LedgerID=a.LedgerID and c.FiscYr=a.FiscYrwhere a.CpnyID is null order by c.CpnyID, c.Acct, c.SUB, c.ledgerid, c.FiscYr

    Fix: Run the Fix4a.SQL script from MR2.zip on both single-currency and multi-currency databases if errors are reported.

  • Find Accthist without CuryAcct records.

    Select top 100 'Step4b',a.CpnyID, a.LedgerID, a.FiscYr, a.Acct, a.Sub, a.CuryId, *from AcctHist a with (nolock)left join Curyacct c with (nolock) on c.CpnyID=a.CpnyID and c.Acct=a.Acct and c.Sub=a.Sub and c.LedgerID=a.LedgerID and c.FiscYr=a.FiscYrwhere a.BalanceType='A' and c.CpnyID is null order by a.CpnyID, a.Acct, a.SUB, a.ledgerid, a.FiscYr

    Fix: For multicurrency databases, either initialize a curyacct record in Initialization mode in GL>Account History or post a transaction to the acct/sub/ledger/fiscyr/cpnyid/curyid. To create or update a curyacct record in GL>Account History>Currency tab, you must enter the data for each period. When you create a batch to post a transaction to the account, this should create a CuryAcct record that contains values for each period. Make sure that you use the year in which the error is reported as the year for posting. Then, reverse the batch to remove the dollars out of the account.

    For a single-currency database, open the record in GL>Account History during initialization mode if there are only one or two records. Change the beginning balance by some amount, save the record, revert it, and then save again. This creates the curyacct record. Or, you can run the script from step 6a in this section.

  • GLTrans without AcctHist

    This query will run slowly if there are many gltrans entries and if all the entries are valid. (The query actually runs faster if there is bad data.)

    Select top 100 'Step4c',g.CpnyID, g.LedgerID, g.FiscYr, g.Acct, g.Sub, g.BaseCuryID, g.CuryId,* from GLTran g with (nolock)left join AcctHist a with (nolock) on g.CpnyID=a.CpnyID and g.Acct=a.Acct and g.Sub=a.Sub and g.LedgerID=a.LedgerID and g.FiscYr=a.FiscYrinner join vs_company c with (nolock) on g.CpnyID=c.CpnyID and c.CpnyCOA in (select CpnyID from glsetup with (nolock)) -- only companies in this application DBwhere g.posted='P' and a.CpnyID is null order by g.CpnyID, g.LedgerID, g.FiscYr, g.Acct, g.Suboption (force order) -- added for performance in larger databases

    Fix: Evaluate the GLtrans field entries. If there is no AcctHist record, the financials are not using these transactions for reports
  • This is typically because the transactions are old. You may decide to delete them or take some other action. Evaluate these entries carefully.

Step 5

Look for GLTrans records that have invalid dates or periods. Such records can cause the following errors in Management Reporter:
  • Error text: Year, Month, and Day parameters describe an unrepresentable DateTime.
  • Error text: Object reference is not set to an instance of an object.
  • GLTrans records that have a blank FiscYr field.

    Select top 100 'Step5a',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate, Posted, Rlsed, BatNbr, Module, * from GLTran with (nolock) where FiscYr='' and (Rlsed=1 or Posted='P')

    To fix: Consider updating the FiscYr field based on the perpost value.

  • GLTrans records that have a perpost value that is far outside a normal range.

    Select top 100 'Step5b',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate, Posted, Rlsed, BatNbr, Module, *from GLTran with (nolock) where (PerPost>='204812' or PerPost<='195001') and (Rlsed=1 or Posted='P')

    Fix: These batches will all likely have a future period to post. You can open these batches in GL>Journal Transactions during Initialization mode.
    Either delete them, change the period to post, or process them, as appropriate.

  • GLTrans records that have a transdate value that is far outside a normal range (frequently by having a 2079 year).

    Select top 100 'Step5c',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate,Posted, Rlsed, BatNbr, Module, *from GLTran with (nolock) where (trandate>='01/01/2048' or trandate<='01/01/1950') and (Rlsed=1 or Posted='P') and TranDate<>'1/1/1900'

    Fix: Update trandate to crtd_datetime. This script shows how.

    update gltran set trandate=crtd_datetime where (trandate>='01/01/2048' or trandate<='01/01/1950') and (Rlsed=1 or Posted='P') and TranDate<>'1/1/1900' 

  • AcctHist records with a FiscYr field far outside a normal range.

    Select top 100 'Step5d',a.CpnyID, a.LedgerID, a.FiscYr, a.Acct, a.Sub, a.CuryId, *from AcctHist a with (nolock) where a.FiscYr>'2048' or a.FiscYr<'1950'

    Fix: These are probably unwanted records. In manipulating data historically, you might have set the fiscal year to a future year to keep the records for later possible review.

Step 6

Locate instances in which amounts in the AcctHist record do not match the amounts in the CuryAcct record.

Note Run this statement only after the issues in steps 1 to 5 have been fixed.

select top 1000 'Step6a', a.CpnyID, a.Acct, a.Sub, a.LedgerID, a.FiscYr, c.numCuryAcct, a.BegBal, c.begbal, a.PtdBal00, c.ptdbal00, a.PtdBal01, c.ptdbal01, a.PtdBal02, c.ptdbal02,   a.PtdBal03, c.ptdbal03, a.PtdBal04, c.ptdbal04, a.PtdBal05, c.ptdbal05, a.PtdBal06, c.ptdbal06, a.PtdBal07, c.ptdbal07, a.PtdBal08, c.ptdbal08,   a.PtdBal09, c.ptdbal09, a.PtdBal10, c.ptdbal10, a.PtdBal11, c.ptdbal11, a.PtdBal12, c.ptdbal12, a.ytdBal00, c.ytdbal00, a.ytdBal01, c.ytdbal01,  a.ytdBal02, c.ytdbal02, a.ytdBal03, c.ytdbal03, a.ytdBal04, c.ytdbal04, a.ytdBal05, c.ytdbal05, a.ytdBal06, c.ytdbal06, a.ytdBal07, c.ytdbal07,   a.ytdBal08, c.ytdbal08, a.ytdBal09, c.ytdbal09, a.ytdBal10, c.ytdbal10, a.ytdBal11, c.ytdbal11, a.ytdBal12, c.ytdbal12  from AcctHist a inner join (select count(*) as numCuryAcct, CpnyID, Acct, Sub, LedgerID, FiscYr, SUM(begbal) as begbal, SUM(ptdbal00) as ptdbal00, SUM(ptdbal01) as ptdbal01, SUM(ptdbal02) as ptdbal02, SUM(ptdbal03) as ptdbal03, SUM(ptdbal04) as ptdbal04, SUM(ptdbal05) as ptdbal05, SUM(ptdbal06) as ptdbal06, SUM(ptdbal07) as ptdbal07, SUM(ptdbal08) as ptdbal08, SUM(ptdbal09) as ptdbal09, SUM(ptdbal10) as ptdbal10, SUM(ptdbal11) as ptdbal11,SUM(ptdbal12) as ptdbal12, SUM(ytdbal00) as ytdbal00, SUM(ytdbal01) as ytdbal01, SUM(ytdbal02) as ytdbal02, SUM(ytdbal03) as ytdbal03, SUM(ytdbal04) as ytdbal04, SUM(ytdbal05) as ytdbal05, SUM(ytdbal06) as ytdbal06, SUM(ytdbal07) as ytdbal07, SUM(ytdbal08) as ytdbal08, SUM(ytdbal09) as ytdbal09, SUM(ytdbal10) as ytdbal10, SUM(ytdbal11) as ytdbal11, SUM(ytdbal12) as ytdbal12from curyacct group by CpnyID, Acct, Sub, LedgerID, FiscYr) c   on a.CpnyID=c.CpnyID and a.Acct=c.Acct and a.Sub=c.Sub and a.LedgerID=c.LedgerID and a.FiscYr=c.FiscYr  where round(a.BegBal,2)<>round(c.begbal,2) or round(a.PtdBal00,2)<>round(c.PtdBal00,2) or round(a.PtdBal01,2)<>round(c.PtdBal01,2) or   round(a.PtdBal02,2)<>round(c.PtdBal02,2) or round(a.PtdBal03,2)<>round(c.PtdBal03,2) or round(a.PtdBal04,2)<>round(c.ptdbal04,2) or   round(a.PtdBal05,2)<>round(c.PtdBal05,2) or round(a.PtdBal06,2)<>round(c.PtdBal06,2) or round(a.PtdBal07,2)<>round(c.PtdBal07,2) or   round(a.PtdBal08,2)<>round(c.PtdBal08,2) or round(a.PtdBal09,2)<>round(c.PtdBal09,2) or round(a.PtdBal10,2)<>round(c.PtdBal10,2) or   round(a.PtdBal11,2)<>round(c.PtdBal11,2) or round(a.PtdBal12,2)<>round(c.PtdBal12,2) or round(a.ytdBal00,2)<>round(c.ytdBal00,2) or   round(a.ytdBal01,2)<>round(c.ytdBal01,2) or round(a.ytdBal02,2)<>round(c.ytdBal02,2) or round(a.ytdBal03,2)<>round(c.ytdBal03,2) or   round(a.ytdBal04,2)<>round(c.ytdbal04,2) or round(a.ytdBal05,2)<>round(c.ytdBal05,2) or round(a.ytdBal06,2)<>round(c.ytdBal06,2) or   round(a.ytdBal07,2)<>round(c.ytdBal07,2) or round(a.ytdBal08,2)<>round(c.ytdBal08,2) or round(a.ytdBal09,2)<>round(c.ytdBal09,2) or   round(a.ytdBal10,2)<>round(c.ytdBal10,2) or round(a.ytdBal11,2)<>round(c.ytdBal11,2) or round(a.ytdBal12,2)<>round(c.ytdBal12,2)

For a single-currency database, open the record in GL>Account History during initialization mode if there are only a few records. Change the beginning balance by some amount, save the record, revert the record, and then save it again. This updates the Curyacct record to match the Accthist record. If there are many records, run the ResetCuryAcctfromAcctHistStep6a.sql script from the MR2.zip file.

In a multicurrency database, open GL>Account History during the initialization mode if there are only a few records, and then click the Currency tab. Correct each period individually, as appropriate. If there are many records, run the script MultiCurrencyStep6aFix.sql from the MR2.zip file. Any change is made in the BaseCuryID record.

After you complete Step 6, rerun the Diagnostics2.SQL script to verify that it no longer reports errors.



Additional scripts to find lowercase data that may cause issues in MR.

This script looks for multiple records where the Company.DatabaseName don’t have the same case (ex: SLDemoApp and SLDEMOAPP). Run this script against the system database.

select c.DatabaseName, c.CpnyID from Company c left outer join sys.databases sd on c.DatabaseName COLLATE Latin1_General_CS_AS= sd.name COLLATE Latin1_General_CS_AS

       where sd.name is null and DatabaseName in (select c1.DatabaseName from Company c1 left outer join company c2 on c1.DatabaseName = c2.DatabaseName where c1.DatabaseName=c2.DatabaseName and c1.DatabaseName COLLATE Latin1_General_CS_AS != c2.DatabaseName COLLATE Latin1_General_CS_AS)

To Fix: Update the databasename to be uppercase.




This script looks for lowercase company ID and subaccount in the accthist, curyacct and gltran tables. Run this script against the system database.


DECLARE @DbName VARCHAR(50)  
DECLARE @useStatement VARCHAR(256)
DECLARE @fullStatement VARCHAR(8000)

DECLARE db_cursor CURSOR FOR 
select distinct DatabaseName from Company

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DbName

WHILE @@FETCH_STATUS = 0  
BEGIN  

 set @DbName = LTRIM(RTRIM(@DbName))
 set @useStatement = 'USE ' + @DbName + ''
    set @fullStatement = '
    SELECT sub as ''Sub-AcctHist-' + @DbName + ''' , *
  FROM AcctHist
  WHERE UPPER(sub) <> sub COLLATE Latin1_General_CS_AS
  
  SELECT sub as ''Sub-GLTran-' + @DbName + ''', *
  FROM GLTran
  WHERE UPPER(sub) <> sub COLLATE Latin1_General_CS_AS
  

  SELECT sub as ''Sub-CuryAcct-' + @DbName + ''', *
  FROM CuryAcct
  WHERE UPPER(sub) <> sub COLLATE Latin1_General_CS_AS
  
  SELECT CpnyID as ''CpnyID-AcctHist-' + @DbName + ''', *
  FROM AcctHist
  WHERE UPPER(CpnyID) <> CpnyID COLLATE Latin1_General_CS_AS
  
  SELECT CpnyID as ''CpnyID-GLTran-' + @DbName + ''', *
  FROM GLTran
  WHERE UPPER(CpnyID) <> CpnyID COLLATE Latin1_General_CS_AS
  

  SELECT CpnyID as ''CpnyID-CuryAcct-' + @DbName + ''', *
  FROM CuryAcct
  WHERE UPPER(CpnyID) <> CpnyID COLLATE Latin1_General_CS_AS
  
 
  SELECT SUB as ''Sub-SubAcct-' + @DbName + ''', * from SubAcct where (SUB collate sql_latin1_general_cp1_cs_as <> upper(SUB))
   or (ConsolSub collate sql_latin1_general_cp1_cs_as <> upper(ConsolSub))
   '

  exec (@useStatement + @fullStatement)
  
       FETCH NEXT FROM db_cursor INTO @DbName  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor




To Fix: If you find any lowercase subaccounts you can run these to put them in uppercase.


update GLTran set Sub = UPPER(Sub)

update AcctHist set Sub = UPPER(Sub)

update CuryAcct set Sub = UPPER(Sub)



If you find any lowercase company ID's you can run these to put them in uppercase.

update accthist set cpnyid = UPPER(cpnyid) WHERE UPPER(CpnyID) <> CpnyID COLLATE Latin1_General_CS_AS

update curyacct set cpnyid = UPPER(cpnyid) WHERE UPPER(CpnyID) <> CpnyID COLLATE Latin1_General_CS_AS

update gltran set cpnyid = UPPER(cpnyid) WHERE UPPER(CpnyID) <> CpnyID COLLATE Latin1_General_CS_AS



This script looks for lowercase LedgerID in the accthist, curyacct and gltran tables. Run this script against the application database.

SELECT TOP 1 LedgerID FROM AcctHist WHERE UPPER(LedgerID) <> LedgerID COLLATE Latin1_General_CS_AS

SELECT TOP 1 LedgerID FROM GLTran WHERE UPPER(LedgerID) <> LedgerID COLLATE Latin1_General_CS_AS

SELECT TOP 1 LedgerID FROM CuryAcct WHERE UPPER(LedgerID) <> LedgerID COLLATE Latin1_General_CS_AS



To Fix: If you find any lowercase LedgerID's you can run these to put them in uppercase.

update AcctHist set LedgerID = UPPER(LedgerID) where LedgerID COLLATE Latin1_General_BIN like '%[a-z]%'

update GLTran set LedgerID = UPPER(LedgerID) where LedgerID COLLATE Latin1_General_BIN like '%[a-z]%'

update CuryAcct set LedgerID = UPPER(LedgerID) where LedgerID COLLATE Latin1_General_BIN like '%[a-z]%'




This script looks for lowercase CuryID in the accthist, curyacct and gltran tables. Run this script against the application database.

SELECT TOP 1 CuryID FROM AcctHist WHERE UPPER(CuryID) <> CuryID COLLATE Latin1_General_CS_AS

SELECT TOP 1 CuryID FROM GLTran WHERE UPPER(CuryID) <> CuryID COLLATE Latin1_General_CS_AS

SELECT TOP 1 CuryID FROM CuryAcct WHERE UPPER(CuryID) <> CuryID COLLATE Latin1_General_CS_AS


To Fix: If you find any lowercase CuryID's you can run these to put them in uppercase.

update AcctHist set CuryId = UPPER(CuryId) where CuryId COLLATE Latin1_General_BIN like '%[a-z]%'  

update GLTran set CuryId = UPPER(CuryId) where CuryId COLLATE Latin1_General_BIN like '%[a-z]%' 

update CuryAcct set CuryId = UPPER(CuryId) where CuryId COLLATE Latin1_General_BIN like '%[a-z]%'  


If no errors are reported after rerunning the Diagnostic2.SQL script, the Management Reporter Data Mart must be Integrated again using the steps in KB 2964624
Properties

Article ID: 2931999 - Last Review: 06/15/2015 13:24:00 - Revision: 8.0

Microsoft Dynamics SL 2011 Service Pack 2, Microsoft Dynamics SL 2011 Service Pack 1, Microsoft Dynamics SL 2011 Feature Pack 1, Microsoft Dynamics SL 2011

  • kbmbsmigrate kbsurveynew KB2931999
Feedback