Dynamics 365 Programmatically export PDF from SSRS report

Dynamics 365 Programmatically export PDF from SSRS report

In Dynamics CRM, user can manually run reports on a record, or view (list of records) but there are some special cases when you need to run and download report programmatically. For example:

1. Automatically run report on a record, then generate the PDF/Word/Excel file and attach to the email record then send to customer.
2. Provide an API for an external system to execute report and download PDF file
3. Create a custom ribbon button to allow user to download the PDF report directly instead of opening the standard report in CRM and downloading PDF file

To do this, we need programmatically call the reporting service to execute and generate PDF files

Here are steps we will do in this article

1. Setup reporting service

2. Create web reference to the reporting service

3. Publish reports for external use

4. Excute report and generate PDF file using C#

5. Conculusion

Setup reporting service

Make sure you install reporting service for CRM. Follow this document if you have not installed 

If you install reporting service correctly, you can open the "Reporting Services Configuration Manager"

Open the "Web Service URL", you will see the report server URL

If you open the Web Service URL, you can see your reports

The reporting web service is the URL above concat with the sring "reportexecution2005.asmx"

In this case, the reporting service is http://xxx-dev/ReportServer/reportexecution2005.asmx

If you open this URL in a browser, you will see the XML response link this

Take note of this URL because you will need it for next step

Create web reference to the reporting service

Open your solution (source code) in visual studio, then add a Service reference for a project

Click on Advanced button

Click on Add Web Reference

Enter the reporting service url into the URL box

After adding successfully, you can see the reporting service is added to your project under Web References

It's almost ready to execute the report using C#

Publish reports for external use

Since we're going to execute the reports from outside, we need to publish them for external use.

Open a report, then select Actions \ Publish Report for External Use

Excute report and generate PDF file using C#

Until now, it's ready to write your code to execute the report

public byte[] RenderSSRSReport(string userName, string passWord, string domain, string reportingServiceUrl,
    string reportName, ParameterValue[] parameters, string formatType)
{
    byte[] result = null;
    string historyID = null;
    // Create a device information for the report
    string devInfo = "" +
                        "False" +
                        "False" +
                        "False" +
                        "True" +
                        "True" +
                        "True" +
                        "";
    // Init some output parameters
    string encoding;
    string extension;
    string mimeType;
    Warning[] warnings = null;
    string[] streamIDs = null;

    // Create a network credential to authenticate with the reporting service
    NetworkCredential cred = new NetworkCredential(userName, passWord, domain);

    // Init reporting execution service with network credential.
    var reportService = new ReportExecutionService()
    {
        Url = reportingServiceUrl,
        Credentials = cred
    };


    try
    {
        ExecutionHeader execHeader = new ExecutionHeader();

        reportService.ExecutionHeaderValue = execHeader;
        reportService.LoadReport(reportName, historyID);

        if (parameters != null)
        {
            reportService.SetExecutionParameters(parameters, "en-us");
        }

        result = reportService.Render(formatType, devInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);
    }
    catch (Exception ex)
    {
        if (ex is SoapException)
        {
            SoapException sexc = ex as SoapException;
            _trace.Trace($"Error generating report - {sexc?.Detail?.InnerText}");
            throw;
        }
        else
        {
            _trace.Trace($"Error generating report - {ex?.Message}");
            throw;
        }
    }

    // result is byte[]
    return result;
}

Explain parameters:

1. reportingServiceUrl: this is the reporting service URL, like http://xxx-dev/ReportServer/reportexecution2005.asmx
2. userName, passWord, domain: are your account in CRM. You will execute the report on behalf of this user and use this user to retrieve CRM data for the report.
3. reportName: is the name of the report including the prefix. The pattern is:

   • string.Format("/{0}/{1}", reportPathPrefix, reportName);

   • Normally, the report prefix has patter: _MSCRM
   • To know exactly the report name and prefix, you can open your report in the reporting server
   • 
   • 
4. parameters: is the input parameter of the report, it has type ParameterValue[]
   • 
   • You can create report parameters like this

   • List parameterValuesList = new List();
     parameterValuesList.Add(
         new ParameterValue()
         {
             Name = "EventId",
             Value = "32dc46c5-dd00-48f7-9f3b-bfd152676b11"
         }
     );
     return parameterValuesList.ToArray();

5. formatType: the output format. For example PDF, Word

The code above will return a byte[], you can also convert to base64 string if needed

string base64 = System.Convert.ToBase64String(reportContentAsByteArray);

Conclusion

1. We can programmatically render and download reports to adapt for some complex automated process
2. This approach is only supported for CRM on-premise because you don't have access to reporting service in CRM online
3. For CRM online you can convert SSRS report to PowerBi then export a Power BI report with Power Automate

Dynamics 365 Programmatically export PDF from SSRS report