RSMUS.comManagement Reporter is a common tool for the Microsoft Dynamics ERP solutions, Dynamics AX, Dynamics GP and Dynamics SL alike. Its flexibility moves beyond traditional reporting functionality to allow companies to efficiently design a variety of reports. In this case, my client called about wanting to delete a company.
The following are the simple steps of deleting a company in Management Reporter:
- Log in as an Administrator to Management Reporter 2012 Configuration Console
- Under Companies, select the company to be deleted and click the button Delete Company
Simple enough, right? But what if you get the following error:
“This company is referenced by an existing report definition or reporting tree definition. Remove these definitions before deleting the company.”
Like many other Dynamics ERP consultant, I turn to search engines to help me resolve this error. I found this very helpful article but had to tweak it a bit. In my case when I encountered this error, my issue did not return any records from DBO.CONTROLTREEDETAIL and DBO.CONTROLREPORT with the select scripts supplied in the article. (You will know what I am talking about here when clicking the link below)
The first thing to do when you encounter an error would be to take a look at the logs in the Management Reporter Configuration Console. With the above error, in Service logs, you will see something like this:
System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint “FK_ControlTreeDetail_ControlCompany”. The conflict occurred in database “ManagementReporter”, table “dbo.ControlTreeDetail”, column ‘CompanyID’.
System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint “FK_ControlReport_ControlCompany”. The conflict occurred in database “ManagementReporter”, table “dbo.ControlReport”, column ‘CompanyID’. The statement has been terminated.
When I see a delete statement from a Knowledgebase article, I would always run a select statement first before doing the delete. Thus I ran the following scripts in the Management Reporter database:
SELECT * FROM DBO.CONTROLCOMPANY (Take note of the company ID from the ID column of the company you wish to delete)
SELECT * FROM DBO.CONTROLTREEDETAIL WHERE ID = ‘ABC’ (Where ABC is the ID from the ID column from select script above)
SELECT * FROM DBO.CONTROLREPORT WHERE ID = ‘ABC’ (Where ABC is the ID from the ID column from select script above)
In my situation, to make matters worse, the last two scripts did not return any results. So, I was really puzzled why I could not delete the company and where are the report and tree definitions that reference to the company as the error suggests.
Note: If results were returned in querying DBO.CONTROLTREEDETAIL and DBO.CONTROLREPORT then you can follow the link above. Now, if your occurrence is like what I encountered, the next couple of queries will be helpful.
There is another column in these tables (DBO.CONTROLTREEDETAIL and DBO.CONTROLREPORT) called CompanyID that might be storing the reference to your company, thus not letting you delete the company. This was my exact issue. Run the next following queries to resolve the error.
SELECT * FROM DBO.CONTROLTREEDETAIL WHERE CompanyID = ‘ABC’
SELECT * FROM DBO.CONTROLREPORT WHERE CompanyID = ‘ABC’
If the above scripts returned results, then you would have to run update scripts to set the CompanyID to something else other than the ID of the company you wish to delete to remove the references.
UPDATE DBO.CONTROLTREEDETAIL SET CompanyID = ‘123′ WHERE CompanyID = ‘ABC’ (Where 123 is another valid ID of another company from DBO.CONTROLCOMPANY)
UPDATE DBO. CONTROLREPORT SET CompanyID = ‘123′ WHERE CompanyID = ‘ABC’ (Where 123 is another valid ID of another company from DBO.CONTROLCOMPANY)
At this time, you can return to Management Reporter Configuration Console and delete the company or just run the following script. Please remember to always create a back up file of the database (Management Reporter, in this instance) before running any update or delete or database changes.
DELETE from DBO.CONTROLCOMPANY WHERE ID = ‘ABC’
If you are looking for support for your existing Dynamics solution, RSM can help. RSM has national practices for Dynamics AX, Dynamics GP and Dynamics SL. If you’d like to learn more about how we can help, contact our professional at erp@rsmus.com or call us at 855.437.7202. If you like to receive more tips like this in your inbox, subscribe to our Dynamics Community News publication.
By: Layla Doctor – Illinois Microsoft Dynamics GP partner
