Programmatically rendering a SQL Server Reporting Services (SSRS) report from Microsoft CRM 4.0 and capturing output in a file.

Programmatically running a SQL Server Reporting Services report and capturing output in a file for use from CRM.

 

It is possible in CRM to use SQL Server Reporting Services in order to run a report and capture the output in a file.  The first problem is that you only see GUIDs listed for CRM reports under the SSRS report manager.  To actually be able to run the report on the report server from the CRM report name you must publish the report for external use. 

 

1.       Go to “Workplace” and then “Reports” in CRM and click on the report you want to edit and click “Edit Report”.

2.       Now click “Actions” and then “Publish Report for External Use”

 

 

 If you now go into Sql Report Manager at http://myreportserver.mydomain.com/reports and go to your specific org database name, you will see the report you published for external use and any sub reports that are utilized by it.  This now allows you to use the web-service and call it by name.

 

 

 

Now, create a plug-in or other application you want to run the CRM report.

In Visual Studio, make a web service reference to the SQL Server Reporting Services web service(. And use something similar to the following snippet of code to render the file and save the output to a file.

If you are using a separate application you would probably want to hardcode credentials, but if you were doing this in a plug-in or custom workflow assembly you would want to use the default credentials.

I also left some commented out code in this example to show how to populate report parameters.

 


ReportExecutionService rs = new ReportExecutionService();
 

//rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Credentials = new System.Net.NetworkCredential("username", "password", "domain");

rs.Url = "http://MySqlServer.MyDomain.com/reportserver/ReportExecution2005.asmx";

 

// Render arguments

byte[] result = null;

// Make sure you use your correct org database name of the following line

string reportPath = "/jmiley_MSCRM/Account Overview";

string format = "PDF";

string historyID = null;

string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

 

// Prepare report parameter.

//ParameterValue[] parameters = new ParameterValue[3];

//parameters[0] = new ParameterValue();

//parameters[0].Name = "EmpID";

//parameters[0].Value = "288";

//parameters[1] = new ParameterValue();

//parameters[1].Name = "ReportMonth";

//parameters[1].Value = "6"; // June

//parameters[2] = new ParameterValue();

//parameters[2].Name = "ReportYear";

//parameters[2].Value = "2004";

 

DataSourceCredentials[] credentials = null;

string showHideToggle = null;

string encoding;

string mimeType;

string extension;

Warning[] warnings = null;

ParameterValue[] reportHistoryParameters = null;

string[] streamIDs = null;

ExecutionInfo execInfo = new ExecutionInfo();

ExecutionHeader execHeader = new ExecutionHeader();

rs.ExecutionHeaderValue = execHeader;

execInfo = rs.LoadReport(reportPath, historyID);

//rs.SetExecutionParameters(parameters, "en-us");
String SessionId = rs.ExecutionHeaderValue.ExecutionID;

Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID);

try
{
result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

    execInfo = rs.GetExecutionInfo();
Console.WriteLine("Execution date and time: {0}",    execInfo.ExecutionDateTime);

 }
 catch (SoapException err)
 {
    Console.WriteLine(err.Detail.OuterXml);
 }
 // Write the contents of the report to an PDF file.
 try
 {
    FileStream stream = File.Create(@"c:\upload_report.pdf", result.Length);
    Console.WriteLine("File created.");
    stream.Write(result, 0, result.Length);
    Console.WriteLine("Result written to the file.");
    stream.Close();
  }
  catch (Exception error)
  {
     Console.WriteLine(error.Message);
  }

You can view full documentation on the SSRS web service from MSDN at:

http://msdn.microsoft.com/en-us/library/ms152787(SQL.90).aspx

 

Instead of PDF files, you can also create other types of files by changing the format string:

-          MHT web archive – “MHTML”

-          XML – “XML”

-          CSV – “CSV”

-          IMAGE – “IMAGE”

-          EXCEL – “EXCEL”

-          HTML – “HTML4.0”, “HTML3.2”, “HTMLOWC”

 

There are a few things to consider:

 

1.       The file copy operation above will use the app domain’s credentials for creating the output file.

If you are using a plug-in or workflow assembly this will be the CRM app pool security account.

If you want the file to be created on a different server, you may want to change the app pool security account to an Active Directory account instead of the default “network service”.

 

2.       Default credentials should be used for the web-service for plug-ins and workflow assemblies as it uses CRM security and will not let people access data through the reports that they do not have read access to in CRM.

 

This post is provided as-is and implies no warranty; Jamie Miley does not assume any responsibility for problems arising from the use of this information.