
SQL Server Reporting Services (SSRS) remains a widely used reporting platform in organizations that run Microsoft SQL Server. Businesses rely on it for invoices, financial statements, operational dashboards, and compliance reports. Modern applications, however, often need to generate those reports programmatically and deliver them through a web or mobile interface rather than through the SSRS portal. Calling SSRS from a Web API allows you to render reports on demand, pass parameters dynamically, and return files such as PDFs or Excel spreadsheets directly to users.
Custom Reporting Solutions by AAMAX.CO
Integrating reporting engines into modern applications requires both back-end expertise and a thoughtful user experience. AAMAX.CO is a full service digital marketing company providing web development, digital marketing, and SEO services worldwide. Their developers build secure APIs and intuitive dashboards that bring business data to life. Through their web application development services, organizations can modernize legacy reporting workflows and deliver insights through fast, user-friendly portals.
Options for Calling SSRS Programmatically
There are three main ways to access SSRS from code:
- URL Access: Request a report through a specially formatted URL that includes rendering format and parameters. This is the simplest approach.
- SOAP API (ReportExecution2005): A comprehensive web service that supports rendering, parameter discovery, and execution sessions.
- REST API: Available in SSRS 2017 and later, it provides access to catalog items and management operations, though rendering is still commonly performed via URL access.
For most Web API scenarios that simply need to render a report to a file, URL access combined with Windows authentication is the most practical solution.
Step 1: Prepare the Report on the Report Server
Deploy your report to the SSRS server and confirm it runs correctly in the web portal. Note the report's folder path and the names of its parameters. Ensure the account your Web API will use has at least Browser role permissions on the report folder.
Step 2: Construct the Report URL
An SSRS URL access request follows this pattern:
https://reportserver/ReportServer?/Finance/Invoice&rs:Format=PDF&InvoiceId=1042The path after the question mark identifies the report, rs:Format specifies the output format, and additional name-value pairs supply report parameters. Supported formats include PDF, EXCELOPENXML, WORDOPENXML, CSV, XML, and IMAGE.
Step 3: Configure Authentication
SSRS typically uses Windows authentication. In your ASP.NET Web API, create an HttpClient using an HttpClientHandler configured with network credentials for a dedicated service account. Store the username and password in a secure configuration source such as Azure Key Vault, user secrets in development, or encrypted application settings. Avoid running with overly privileged accounts.
Step 4: Create the Web API Endpoint
Create a controller action that accepts the parameters your report needs, validates them, builds the SSRS URL, and calls it with HttpClient. For example, an endpoint like GET /api/reports/invoice/1042 would build the URL for the invoice report, request the PDF, and stream the result back. Use IHttpClientFactory in ASP.NET Core to manage client lifetimes efficiently.
Step 5: Return the File to the Client
Read the SSRS response as a stream or byte array and return it using the File result helper with the appropriate content type, such as application/pdf, and a download filename. Streaming large reports instead of loading them fully into memory improves scalability.
Step 6: Validate and Encode Parameters
Never pass user input directly into the report URL without validation. Check that parameters are the expected types and that users are authorized to access the requested data. URL-encode all parameter values to prevent malformed requests. Authorization checks are especially important, since a user should not be able to view another customer's invoice simply by changing an ID.
Step 7: Using the SOAP API for Advanced Scenarios
If you need to discover report parameters dynamically, manage execution sessions, or render pages individually, use the ReportExecution2005 SOAP endpoint. In .NET, add a connected service reference to the WSDL, call LoadReport to start an execution, SetExecutionParameters to supply values, and Render to produce the output bytes. This approach offers more control at the cost of additional complexity.
Step 8: Handle Errors Gracefully
SSRS returns HTTP errors when reports fail, parameters are invalid, or permissions are missing. Catch these responses, log the details including the report path and parameters (excluding sensitive data), and return meaningful error messages to the client. Set reasonable timeouts, as complex reports can take time to render.
Step 9: Improve Performance
Large reports can be resource-intensive. Consider enabling report caching or snapshots in SSRS for frequently requested reports with static data. For long-running reports, generate them asynchronously using a background job and notify users when the file is ready. Optimizing the underlying SQL queries and datasets often yields the biggest performance gains.
Step 10: Secure the Entire Flow
Expose the Web API only over HTTPS, require authentication for report endpoints, and keep the SSRS server on an internal network rather than exposing it to the internet. The Web API acts as a secure gateway, enforcing business rules and access control before any report is generated.
Conclusion
Calling an SSRS report from a Web API lets you deliver professional reports through modern applications. Use URL access for straightforward rendering, the SOAP API for advanced control, and always authenticate securely, validate parameters, and enforce authorization. With caching, error handling, and thoughtful architecture, your Web API can turn SSRS into a powerful, on-demand reporting engine for users anywhere.