How to export SSRS 2017 report using REST API

Question

I've setup SSRS 2017.I need to export an SSRS report using their new REST API, I've been looking through the API specification here but I can't find a method in swagger specification to render/export that report using an HTTP client. This Question maybe asking for same that i'm looking for.

Stultify

Answer 1

So far I know Currently, there is no method available in RestAPI provided by Microsoft available here

There is an alternative to generate report by utilizing Url Access (SSRS)

URL access to the report server in SQL Server Reporting Services (SSRS) enables you to send commands to a report server through a URL request.

URL Access Syntax

URL requests can contain multiple parameters that are listed in any order. Parameters are separated by an ampersand (&) and name/value pairs are separated by an equal sign (=).

Export a Report Using URL Access

You can optionally specify the format in which to render a report by using the rs:Format URL parameter. The HTML4.0 and HTM5 formats (rendering extension) will render in the browser and for other formats, the browser will prompt to save the report output to a local file.

For example, to get a PDF copy of a report directly from a native mode report server:

http://myrshost/ReportServer?/myreport&rs:Format=PDF  

You can also provide parameters to this using syntax

http://myrshost/ReportServer?/activeusers&UserStatus=true&rs:Format=PDF

For more details, you can visit

https://learn.microsoft.com/en-us/sql/reporting-services/export-a-report-using-url-access

Nearby

that's what I thought REST API doesn't have a method to render the file, anyways thanks for this. – Stultify

A secure API like with PowerBI on Azure (over OAuth) would be great for SSRS – Librettist

I dug through the documentation and also Swagger and couldn't find a way to export a report from the REST api. Which is insane, I would think one of the top features requesting in a reporting tool would be, ya know, the ability to generate the reports. – Shuttlecock

Answer 2

Selenium's .Net DLLs can be used to invoke the report, including setting any report parameters.

The trick is that the ReportViewerControl emits the parameters and the "View Report" button within an IFRAME. The "ChroPath" Chromium extension can be used to help get full XPath paths to elements in a page, but that path may not actually work with the Selenium APIs.

But if you "set the focus" into the IFRAME:

# from my Powershell script that does this...

#first, switch to the reportviewercontrol IFRAME:

[void] $seldriver.SwitchTo().frame(0)

...then you should be able to access any of the HTML elements within the IFRAME with relative XPath queries ala

$xpath = "//*[@id='ReportViewerControl_ctl04_ctl03_txtValue']"

To run the report, you'd invoke the Click event on the "View Report" button.

The big caveat is that the HTML elements emitted by the ReportViewerControl have pretty dry, serially generated names and IDs. But with Selenium, you can try to access them in a variety of ways.

For Powershell users, the "Selenium" powershell module is useful. But you'll need to dip into the .DLLs for things like switching to the ReportViewerControl IFRAME.

Another thing I found I needed to do is throw in liberal sleep N #n seconds commands to let parts of the report finish rendering and get pulled into their containing variables.

At least that's what I have done in my Powershell script.

I am not sure that the Selenium APIs really throw any events for page rendering, even though invoking a report seems like it'd be inherently an asynchronous action. The Selenium API does in theory support setting explicit timeouts (as well as implicit timeouts), etc.

So, in my case, I want to invoke all the reports on my SSRS server to see if they actually run. Selenium seems like a good way to "interact" with the reports in a web browser.

Lynnettelynnworth

Answer 3

This appears to be somewhat out of date. You can download a report which is in .rdl format directly from the API:

Check out: https://app.swaggerhub.com/apis/danmonteiro/SSRS-ageradora/2.0#/Reports/GetReportContent

Here's an example url that sends back the file:

http://10.0.15.78/reports/api/v2.0/Reports(8bf3f3c9-8f72-4c1a-b9bb-54b8ff1d6729)/Content/$value

I just tried this against report server in the browser and it immediately asked where I wanted to save the file.

Psia