Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

"Migrating" from MR Legacy Provider to Datamart

Posted on by

I'm asking & answering my own question, so hopefully I can help someone else who needs this info!

When switching a client from the Legacy Provider to the Datamart, you then need to update all the company references in both the Trees and the Report Definitions.

When you have a large set of databases, and different Building Block Groups for each, this can be quite tedious to do within the UI.

I have here a set of scripts (from Microsoft support) that allow you to perform the update from SQL.  Please note, use these scripts at your own risk, and please be sure to make a backup before you begin!

Run all scripts against the ManagementReporter database.

*Please make a backup of the Management Reporter Database

  --Selects list of Companies

select ID, Code, Name from ControlCompany

--Select a list of Reports and Tree’s that will be affected

select * from ControlReport where CompanyID=(SELECT [ID] FROM ControlCompany WHERE [Code] = 'TWO')

select * from ControlTreeDetail where CompanyID=(SELECT [ID] FROM ControlCompany WHERE [Code] = 'TWO')

  --Update Reports currently set at 'TWO-Curr' to 'TWO' company

Update ControlReport set CompanyID= (SELECT [ID] FROM ControlCompany WHERE [Code] = 'TWO-Curr') where CompanyID= (SELECT [ID] FROM ControlCompany WHERE [Code] = 'TWO')

  --Update Trees currently set at 'TWO-Curr' to 'TWO' company

Update ControlTreeDetail set CompanyID= (SELECT [ID] FROM ControlCompany WHERE [Code] = 'TWO-Curr') where CompanyID= (SELECT [ID] FROM ControlCompany WHERE [Code] = 'TWO')

*This post is locked for comments

  • Suggested answer
    Mark101 Profile Picture
    Mark101 139 on at
    RE: "Migrating" from MR Legacy Provider to Datamart

    Lyn,

    This is great. FYI for CU13 and higher, you need to add "Reporting." in front of your table names. I have used this in multiple conversions. Both directions.

  • Verified answer
    Lyn Barr Profile Picture
    Lyn Barr on at
    RE: "Migrating" from MR Legacy Provider to Datamart

    These scripts solved my problem.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,151 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,963 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans