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

Comments

I'm using 2019 SSRS and the latest C# in VS. I had to change somethings in the code.

I want to say: Special thank you!!! This article helped me. God bless you

Thu, 10/06/2022 - 08:59
Ermenegildo Cumbe (not verified)

Hello,
I'm facing an error when trying to pass the parameter
Cannot read the next data row for the dataset . ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException:
Microsoft.Crm.CrmException: An unexpected error occurred.
System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Invalid XML.

The report is being generated from CRM normally
could you please assist

Fri, 03/17/2023 - 12:45
Marina (not verified)

Hi Marina,

I'm not sure this issue cause by the passing of the parameters.

You can double-check this by passing the parameters to a simple blank report, then display these parameters in the report. By this way, you can make sure the report received the expected params.

 

Regards,

Hao

Fri, 03/24/2023 - 04:15

In reply to by Marina (not verified)

If I have 1 parameter and want to run the code for the report as a custom workflow, how can I get the id of the current record to pass as a parameter?

Mon, 07/31/2023 - 21:45
Luis (not verified)

Dear Luis,
I assume that you have a report that has 1 parameter = the GUID of the current record and you want the pass the GUID of the current record of the running workflow to this param.
If this is what you want to do, you can use custom workflow activity (https://learn.microsoft.com/en-us/power-apps/developer/data-platform/wo…). After deploying the custom workflow activity, you can add it as a step (workflow activity) to your workflow.
You can get the GUID of the current record of the workflow by this way:
var workflowContext = context.GetExtension();
var serviceFactory = context.GetExtension();
var trace = context.GetExtension();
var orgService = serviceFactory.CreateOrganizationService(workflowContext.UserId);
Guid primaryEntityId = workflowContext.PrimaryEntityId;
Guid runningUserId = workflowContext.InitiatingUserId;
The primaryEntityId is the GUID of the current record.
Then, you can run the code generate the report PDF inside the custom workflow activity.
Hope this helps you.

Fri, 08/04/2023 - 05:53
Hao (not verified)

In reply to by Luis (not verified)

Add new comment

Image CAPTCHA
Enter the characters shown in the image.