Exporting a CRM SSRS Report from .NET Code

We recently came across a requirement where I had to export some custom SSRS Report to Excel file on a schedule and store the file in a file system path. This will contain some parameters to be passed and I had to pass some dynamics values to these parameters. Hence report snapshot is not an option here. 

One option was to go with a console application which can be scheduled using Windows Task Scheduler and offers much more flexibility over the functionality. On searching around internet, I didn't find much help. The solution was scattered across many blog posts and forums. Consolidating the solution we implemented here. 

The steps involve

1. Retrieving the report server URL from deployment properties using deployment service (But wasn't required for my case. So not including the code for that. I retrieved the report server URL from app.config)
2. Retrieving current user ID and organization ID, using a WhoAmIRequest.
3. Retrieving the report details from CRM (using report name probably). The report record in CRM contains the report name on SSRS, it will basically be GUID.
4. Initialize a report viewer control (.NET Control) pointing to the above report.
5. Save the report as Excel.

Posting a sample code. May not work as is, since I am just posting snippets of my production code. Please add reference to Microsoft.ReportViewer.WinForms assembly for ReportViewer control.

string reportServerUrl = "http://xrm.com"

WhoAmIRequest whoami = new WhoAmIRequest();
WhoAmIResponse iam = (WhoAmIResponse)orgService.Execute(whoami);
Organization orgObject = orgService.Retrieve(Organization.EntityLogicalName, iam.OrganizationId, new Microsoft.Xrm.Sdk.Query.ColumnSet(true)).ToEntity();

Guid? reportId = null;
using (Xrm serviceContext = new Xrm(orgService))
{
      reportId = (from r in serviceContext.ReportSet
                  where r.Name == reportNode.Attribute(XName.Get(nameAttribute)).Value
                  select r.ReportId).FirstOrDefault();
}

ReportViewer reportViewer = new ReportViewer();
reportViewer.ServerReport.ReportPath = @"/" + orgObject.Name + "_MSCRM/CustomReports/{" + reportId.ToString().ToLower() + "}";
reportViewer.ServerReport.ReportServerUrl = new Uri(reportServerUrl);
reportViewer.ProcessingMode = ProcessingMode.Remote;
reportViewer.ServerReport.Timeout = 1200000;
reportViewer.ServerReport.ReportServerCredentials.NetworkCredentials = credentials;
ReportDataSourceInfoCollection reportDataSources = reportViewer.ServerReport.GetDataSources();

DataSourceCredentials creds = new DataSourceCredentials();
creds.UserId = iam.UserId.ToString();
creds.Password = iam.OrganizationId.ToString();
creds.Name = reportDataSources[0].Name;

DataSourceCredentialsCollection credsCollection = new DataSourceCredentialsCollection();
credsCollection.Add(creds);

reportViewer.ServerReport.SetDataSourceCredentials(credsCollection);
reportViewer.ServerReport.SetParameters(new ReportParameter("statuscode", "1"));
reportViewer.ShowParameterPrompts = false;

string mimeType, encoding, extension;
string[] streams;
Warning[] warnings;
byte[] fileBytes = reportViewer.ServerReport.Render("Excel", string.Empty, out mimeType,
                            out encoding, out extension, out streams, out warnings);

string outputPath = reportNode.Attribute(XName.Get("OutputFilePath")).Value + "\\" + outputFileName;
using (FileStream fs = new FileStream(outputPath, FileMode.Create))
{
    fs.Write(fileBytes, 0, fileBytes.Length);
}