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
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.
These scripts solved my problem.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,151 Super User 2024 Season 2
Martin Dráb 229,963 Most Valuable Professional
nmaenpaa 101,156