How to generate an SSRS report from a .NET Core application

by admin

SSRS is a powerful reporting tool that can be used to create extensive paginated reports. SSRS is also great to design invoices, small reports and more. Often you want to generate and download various documents from your .NET Core application. Using SSRS to build and deploy these, saves a lot of time. This article will guide you through the process of connecting your .NET Core application with your SSRS reports.

You can use this article if you:

  • Do not want to use the report viewer in your .NET Core application
  • Want to generate a PDF, Word or Excel file from an SSRS report with (optional) parameters
  • You use a Windows or Service account to access the SSRS report(s)
  • Use Visual studio 2019

Step 1

Right click on the project in Visual Studio and add a new Connected Service.

Click on the Microsoft WCF Web Service Reference Provider

Step 2

  • Enter your SSRS endpoint URI followed by the SSRS name (default is reportserver) + /ReportExecution2005.asmx?wsdl
  • Click on the GO button and wait for VS to resolve
  • You can rename your Service Reference in the “Namespace” input box, change it to ReportExecutionService
  • Press the Finish button

Step 3

Add the following two NuGet packages to your project:

  1. System.ServiceModel.Primitives
  2. System.ServiceModel.Http

Step 4

Add a SSRS folder to your project root folder, and create the following two classes:

ReportingServicesEndpointBehavior.cs

    internal class ReportingServicesEndpointBehavior : IEndpointBehavior
    {
        public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters) { }

        public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
        {
            clientRuntime.ClientMessageInspectors.Add(new ReportingServicesExecutionInspector());
        }

        public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher) { }

        public void Validate(ServiceEndpoint endpoint) { }
    }

    internal class ReportingServicesExecutionInspector : IClientMessageInspector
    {
        private MessageHeaders headers;

        public void AfterReceiveReply(ref Message reply, object correlationState)
        {
            var index = reply.Headers.FindHeader("ExecutionHeader", "http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices");
            if (index >= 0 && headers == null)
            {
                headers = new MessageHeaders(MessageVersion.Soap11);
                headers.CopyHeaderFrom(reply, reply.Headers.FindHeader("ExecutionHeader", "http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"));
            }
        }

        public object BeforeSendRequest(ref Message request, IClientChannel channel)
        {
            if (headers != null)
                request.Headers.CopyHeadersFrom(headers);

            return Guid.NewGuid(); //https://msdn.microsoft.com/en-us/library/system.servicemodel.dispatcher.iclientmessageinspector.beforesendrequest(v=vs.110).aspx#Anchor_0
        }
    }

The ReportingServicesEndpointBehavior class is needed because the SSRS connection works with a session. You need to have a session identifier to bind all calls to the same session, otherwise you get all kind of errors.

SSRSDownloader.cs

    public static class SSRSDownloader
    {
        const string SSRSUsername = "SampleUsername";
        const string SSRSPassword = "SamplePassword";
        const string SSRSReportExecutionUrl = "https://192.168.110.2/reportserver/ReportExecution2005.asmx?wsdl";
        const string SSRSFolderPath = "DemoReports";
        const string ReportName = "Report1";

        public async static Task<byte[]> GenerateSSRSReport(int demoParam)
        {
            var binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly);
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
            binding.MaxReceivedMessageSize = 10485760; //10MB limit

            //Create the execution service SOAP Client
            var rsExec = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(SSRSReportExecutionUrl));

            //Setup access credentials.
            var clientCredentials = new NetworkCredential(SSRSUsername, SSRSPassword, ".");
            if (rsExec.ClientCredentials != null)
            {
                rsExec.ClientCredentials.Windows.AllowedImpersonationLevel =
                    System.Security.Principal.TokenImpersonationLevel.Impersonation;
                rsExec.ClientCredentials.Windows.ClientCredential = clientCredentials;
            }

            //This handles the problem of "Missing session identifier"
            rsExec.Endpoint.EndpointBehaviors.Add(new ReportingServicesEndpointBehavior());

            await rsExec.LoadReportAsync(null, "/" + SSRSFolderPath + "/" + ReportName, null);

            //TODO: determine parameters
            //Set the parameters asked for by the report
            ReportExecutionService.ParameterValue[] reportParam = new ReportExecutionService.ParameterValue[1];

            reportParam[0] = new ReportExecutionService.ParameterValue();
            reportParam[0].Name = "demoParam";
            reportParam[0].Value = demoParam.ToString();

            await rsExec.SetExecutionParametersAsync(null, null, reportParam, "en-us");

            //run the report
            const string deviceInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
            var response = await rsExec.RenderAsync(new RenderRequest(null, null, "EXCEL", deviceInfo));

            //spit out the result
            var byteResults = response.Result;

            return byteResults;
        }
    }

This is the actual downloader class, the example above is very straight forward. It downloads a report called Report1 as PDF, with one parameter “demoParam”.

The available report formats are:

  • PDF
  • EXCEL
  • WORD
  • POWERPOINT
  • HTML

Step 5

You can generate the SSRS report and retrieve the filebytes with:

        public async Task GetSSRSFileBytes()
        {
            var ssrsFileBytes = await SSRSDownloader.GenerateSSRSReport(1);
        }

Then, save it the filesystem, return it to the user, or whatever you want!

Related Posts

2 comments

Venkata Kishore Kumar Golla April 29, 2021 - 6:48 pm

The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘NTLM’

Reply
admin May 4, 2021 - 6:18 pm

Hi,

Sorry for the late reply!

You might need to enable NTLM on the server you are running SSRS at, you can do so by going to :
Administrative tools —>Local Security Policy—>Local Policy –>Security options —>Lan Manager Authentication Level Need to choose —Send LM & NTLM Responses,

or try adding the following two lines to the code:
rsExec.ClientCredentials.Windows.AllowNtlm = true;
rsExec.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

Reply

Leave a Comment