Correct data errors that prevent Management Reporter integration

  • Article

This article provides a script to correct data errors that prevent Management Reporter integration.

Applies to:   Microsoft Dynamics SL 2011 Service Pack 2, Microsoft Dynamics SL 2011 Service Pack 1, Microsoft Dynamics SL 2011, Microsoft Dynamics SL 2015
Original KB number:   2931999

Summary

This article provides steps to run a Diagnostics.sql script for Management Reporter 2012 for Microsoft Dynamics SL 2011 and Dynamics SL 2015. 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.

MR4.zip

Note

This same information is contained in the Diagnostics4.Sql file, which is included in the MR4.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)

    Note

    • 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 with doing 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

    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.FiscYr where 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.FiscYr where 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.FiscYr inner 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 DB where g.posted='P' and a.CpnyID is null order by g.CpnyID, g.LedgerID, g.FiscYr, g.Acct, g.Sub option (force order) -- added for performance in larger databases

    Fix: Evaluate the GLtrans field entries. If there is no AcctHist record, the financial isn't 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')

    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' or len(a.FiscYr) < 4

    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 ytdbal12
         from
            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 MultiCurrencyStep6aFix.sql script from the MR2.zip file. Any change is made in the Base CuryID 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)

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

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

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

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