1/22/2013

How to Publish Management Reporter Reports to SharePoint

Recently I was asked to integrate Management Reporter reports to SharePoint Foundation 2010. With Management Reporter 2012, it's quite simple to achieve.
 
All you have to do is to follow the steps below
 
1. Go to SharePoint site and create a document library.
 
2. Assign site permissions for the people who need to see the reports. Also provide Design and contribute permissions to the Management Reporter process service user.
 
3. Go to Management Reporter and choose the report from report definitions you'd want to publish to SharePoint.
 
4. Click Output and Distribution tab from the report definition.
 
 

5. Check Generate to multiple report library locations and add the SharePoint document library link in Related Report Link location as shown below.
 
 
6. If you want this report to be automatically publishing to SharePoint, You can setup a report schedule in Report scheduler.
 
7. You can see the reports in SharePoint like this.
 
 
 
8. When you click any report, it'll open like this where you can have the option of viewing it in Excel, Report viewer or XPS document. You can also set a default view so that next time it won't ask for a choice.
 

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