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

It is now possible for some cases to generate reports with SSRS without actually having a SSRS server running. Find out how to here:

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

10 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
lalit May 17, 2022 - 7:13 am

how can I set the request timeout?

Reply
admin August 2, 2022 - 7:03 pm

Hi lalit, sorry for the extremely late reply. I believe the timeout is set in the report/reportserver itself. The default is 1800 seconds. If that isn’t enough, you can edit is by checking the following article:
https://helpdesk.kaseya.com/hc/en-gb/articles/229028988-Modifying-the-Execution-TimeOut-Setting-in-Microsoft-Reporting-Services-SSRS-

Reply
Adilson da Costa March 14, 2023 - 9:54 am

My error:
One or more errors occurred. (System.Web.Services.Protocols.SoapException: O servidor não reconheceu o valor do cabeçalho HTTP SOAPAction: http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices/LoadReport.
em System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest()
em System.Web.Services.Protocols.SoapServerProtocol.Initialize()
em System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing))

Reply
admin May 30, 2023 - 3:22 pm

Does your SSRS report work when you render it from the SSRS server itself instead from code? This looks like something internal is going wrong!

Reply
Nazeeb May 15, 2023 - 9:01 am

Hi, is there any option to download the large dataset as Excel. currently, its downloading as .xls which has a row limitation. is there any way to overcome that limitation?

Reply
admin May 30, 2023 - 3:22 pm

Instead of using
var response = await rsExec.RenderAsync(new RenderRequest(null, null, "EXCEL", deviceInfo));
You can use
var response = await rsExec.RenderAsync(new RenderRequest(null, null, "EXCELOPENXML", deviceInfo));
to get an xlsx

Reply
Edil June 14, 2023 - 2:47 pm

I get an error that says “Entry point was not found”, any idea what I did wrong?

Reply
admin June 19, 2023 - 8:25 am

Are you sure that the URL you are using is correct? If you try the SSRS endpoint URI in your browser, it should show you a prompt for a username and password. Does that show up for you? If not, the URL might be incorrect.

Reply

Leave a Comment