Mon, 05/23/2022 - 05:19 By Nguyen Van Hao Reviewed By Lloyd Sebag
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"

Dynamics 365 Programmatically export PDF from SSRS report

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

Dynamics 365 Programmatically export PDF from SSRS report

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

Dynamics 365 Programmatically export PDF from SSRS report

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

Dynamics 365 Programmatically export PDF from SSRS report

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

Dynamics 365 Programmatically export PDF from SSRS report

Click on Advanced button

Dynamics 365 Programmatically export PDF from SSRS report

 

Click on Add Web Reference

Dynamics 365 Programmatically export PDF from SSRS report

 

Enter the reporting service url into the URL box

Dynamics 365 Programmatically export PDF from SSRS report

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

Dynamics 365 Programmatically export PDF from SSRS report

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

Dynamics 365 Programmatically export PDF from SSRS report

 

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 = "<DeviceInfo>" +
                        "<Toolbar>False</Toolbar>" +
                        "<ActionScript>False</ActionScript>" +
                        "<JavaScript>False</JavaScript>" +
                        "<OutlookCompat>True</OutlookCompat>" +
                        "<HTMLFragment>True</HTMLFragment>" +
                        "<OnlyVisibleStyles>True</OnlyVisibleStyles>" +
                        "</DeviceInfo>";
    // 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: <CRM Organization name>_MSCRM
    • To know exactly the report name and prefix, you can open your report in the reporting server
    • Dynamics 365 Programmatically export PDF from SSRS report
    • Dynamics 365 Programmatically export PDF from SSRS report
  4. parameters: is the input parameter of the report, it has type ParameterValue[]
    • SSRS report
    • You can create report parameters like this
    • List<ParameterValue> parameterValuesList = new List<ParameterValue>();
      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

Add new comment

Image CAPTCHA
Enter the characters shown in the image.