1/22/2013

Deletion of company in Management Reporter


One might see this error when attempting to delete a company in Management Reporter Configuration console.

 

"This company is referenced by an existing report definition or reporting tree definition. Remove these associations before deleting the company"


For those who aren't aware as to how to delete or create a company in Management Reporter, It happens through configuration console which only Administrators can see that.  

The first step of troubleshooting is to go and see if any existing report definitions or reporting tree definitions linked to this company.Change those definitions to other companies so that those references to the company in question are removed, if any.
 
There might be occasions where the company cannot be deleted despite no visible references in the definitions.
 
To troubleshoot this, Go to Management Reporter service logs in the configuration console. This is the first level of troubleshooting for Management Reporter. Anything goes wrong, one must go and see what exactly the log is telling you. I got a log 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.

To resolve this, I went to Management Reporter database and took a backup of the same. Then, I ran the below statement to get the company ID
 
SELECT * FROM DBO.CONTROLCOMPANY
 
You can retrieve the company ID from ID column in the said table. The company database ID will be stored in the corresponding Code column.
 
I ran the following delete statements on two tables.
 
DELETE FROM DBO.CONTROLTREEDETAIL WHERE ID = '123' (Replace 123 with the ID retrieved from control company table)
 
DELETE FROM DBO.CONTROLREPORT WHERE ID = '123' (Replace 123 with the ID retrieved from control company table)
 
These statements above will delete all orphaned references in the report definitions and reporting tree definitions related tables for the company to be deleted.
 
After the above statements, we can go to configuration console and delete the company by just clicking 'Delete company'.