SQL Server Reporting Services Rest API


By:   |   Updated: 2018-07-10   |   Comments (11)   |   Related: > Reporting Services Administration


Problem

What functionality is available in the SQL Server Reporting Services REST API and how would I use it?

Solution

With the introduction of the most recent release of SQL 2017, a new REST API was introduced for accessing certain meta data details concerning SQL Server Reporting Services (SSRS). The SQL 2017 version uses the REST API (RESTful API or representational state transfer) as a replacement for the SOAP access that previously supplied data on many of the catalog and other details surrounding a SSRS catalog. Some of the data points that can be retrieved via the REST API calls can also potentially be retrieved via direct queries against the SSRS database where much of the background data is stored for SSRS. For instance, the queries outlined in this tip for the most part still work well at querying various details surround SSRS and the reports deployed to the SSRS website.  Even so, the direct query method requires direct access to database which many users will not have. Therefore, the REST API alternative provides similar access to this data but uses the web service to serve up the data.

SQL Server Reporting Services Rest API

The first step in being able to use the SSRS REST API is to download SQL Server Reporting Services 2017. Be sure you are getting the latest edition and remember, now, SSRS is a separate stand along install. Once SSRS is installed and running, we can now begin to interrogate reporting services using the API.

Any REST API utilizes a set of basic commands:

  • GET
  • PUT
  • POST
  • DELETE
  • PATCH

While we will not do a full dive into all the constraints and rules around REST APIs, certain restrictions are provided by the above methods for interacting with web services via HTTP. In essence these items provide the basic framework for interacting with the data from an URL service request. The most common format for housing the data as it travels back and forth is via a JSON file while the HTTP protocol provides the framework for providing statuses of our method requests (the infamous 404 error is one of those statuses). Finally, it should be noted that the GET method, noted above, is the default method.

Enough on the theory, let us try some examples. We will start with just using the Chrome web browser to execute our HTTP request; we will later use the Fiddler Free Version (https://www.telerik.com/fiddler) as it provides the ability to execute the various HTTP commands using a nice interface and also provides richer details about the server responses.

For our first example, we will execute a simple GET request which will return a list of Reports, and their related elements, on our local SSRS server. Remember the GET method is the default method, so we will not specify it in our commands.

Our first call will be to simply return all the report information that are on the report server. The command uses the report server URL and then references the API, version 2.0, and then references the schema to return. Thus, for our local report server the command would look like: http://localhost/Reports_SSRS/api/v2.0/Reports.

  • http://localhost/Reports_SSRS - This portion is the URL to my local report server.
  • /api/v2.0 - This portion reference the SSRS API, version 2.0, which SQL 2017 uses.
  • /Reports - This portion of the command instructs the request to return data for the Reports Schema (we will discuss the available schemas later).

As illustrated below, we execute the GET method against the SSRS REST API Reports schema which in turn returns a list of all the reports along with multiple properties about each of those reports.

Get Command

Next, we can add arguments to our request to limit or filter the returned values. The first such command sets the number of records to be returned to just a specified top number, n: http://localhost/Reports_SSRS/api/v2.0/Reports?$top=3.  We use 3 in the below command to return the top 3 reports. We should note that the below results are all in json form.

Get Top 3

Subsequently, we can limit not only the number of records returned, but also select the actual fields or attributes to be returned. In the below example, we are requesting just the report name and report path / location: http://localhost/Reports_SSRS/api/v2.0/Reports?$select=Name,Path .

Get Fields

Incidentally, we could also use a select=*, just like a SQL query (with an equal sign in between): http://localhost/Reports_SSRS/api/v2.0/Reports?$select=*.

Get Select *

We can also combine multiple arguments using an “&” between each one: http://localhost/Reports_SSRS/api/v2.0/Reports?$select=*&$top=2 .

Get Multiple Arguements

As shown below, filter criteria can also be used to filter what values are returned. We are filtering in the below example using “contains” within the description field, specifically looking for the word “map” http://localhost/Reports_SSRS/api/v2.0/Reports?$filter=contains(Description,'map') .

Get Filter

What happens if we enter an incorrect value for the URL or the arguments? A HTTP error will be generated as shown below. Generally, we want a 200 status to be returned, but you will only see this status if you are using a tool to review the http traffic which we discuss below.

Get

As an alternative method of completing the API calls is to use a tool such as Fiddler, cURL, or PostMan. Furthermore, browser add-ins such as Reslet are available for detailed HTTP testing. In the below illustrations, we display the calls from Fiddler, the request status, and finally results of the HTTP method call.

Up to this point we have focused on the GET Command and the Reports schema. However, the list of schema values that can be requested is quite large and includes:

  • Datasets
  • Reports
  • Mobile Reports
  • Folders
  • KPIs
  • Favorite Reports
  • Linked Reports
  • Subscription

Within each of these schemas there are many sub elements or properties that can be requested, updated, or deleted. For instance, within the Reports schema, you can request details around comments, subscriptions, parameter definitions, and data sources. In the below example, a request is made from the Reports schema to pull the Reports data source details; the basic syntax is: /Reports({Id})/DataSources. The Id is the GUID for the report.

Get Datasource with Fiddler

Now we will move to using the POST method to add a new folder on the report server. The POST command syntax is similar to the GET command, but we must specify the folder name and path in order for the folder to be created. The basic command is: http://localhost/Reports_SSRS/api/v2.0/Folders and the properties specified are in the format of: {"Name":"Test Folder Add","Path":"/Test Folder Add"}. 

Here is a picture of my report server home page before adding the folder.

Report Server before

Next, we execute the POST in Fiddler as shown below.

Post Folder

The end result is the addition of the new folder.

SSRS After

We could adjust the adjust the description for the folder by calling the PUT method. We need to specify the Folder GUID ID (you can run a GET request to get the folder GUID) and the Properties element in the request.  The command takes the following format: http://localhost/Reports_SSRS/api/v2.0/Folders(d3b75510-fcd4-499e-81fa-deed5b416f4f)/Properties while the request body details take this format: [{"Name":"Description","Value":"Projects just for Scotts Tips"}].  Executing this method is shown below.

PUT Description

Now we have a description for our folder, and of course you could adjust many of the other properties.

New folder description

We can also delete this folder using the DELETE method. It requires that we know the ID for the folder that we would like to delete; we can again use GET method to obtain that ID.  The DELETE request, http://localhost/Reports_SSRS/api/v2.0/Folders(c10008d0-edc6-40ff-a53c-e8c577847788), is executed below.

Delete Method

Reviewing the report server, we see the noted folder is now removed.

Delete Method result

These same or similar commands can be called for most of the other schema objects.  The full schema and related properties / arguments is available on SwagerHub at https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0 . As you can see in the above examples, the new SSRS Version 2.0 REST API provides significant access to the meta data stored for most of the objects in SSRS.

Next Steps


Last Updated: 2018-07-10


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Comments For This Article




Wednesday, July 15, 2020 - 6:07:11 AM - Velmani Back To Top (86138)

Hi Scott.

The need is to trigger a report as soon as we enter user id in to the table.As of now we scheduled a subscription to verify if the user id is loaded into table and then pass that user id as a parameter to run the report. Is there a way to trigger a report as soon as user id is populated in table without using triggers..does REST API helps in it?


Wednesday, July 08, 2020 - 10:05:17 AM - Scott Murray Back To Top (86107)

Velman,

Why not just call it directly using the URL.


Wednesday, July 08, 2020 - 5:45:45 AM - VELMANI Back To Top (86102)

Thanks for the article. Can we execute the report by calling the API? I need to execute the report passing a parameter. Can that be done using API to trigger the report?


Wednesday, July 08, 2020 - 3:45:50 AM - VELMANI Back To Top (86098)

Please let me know if we can execute the reports through API. I need to pass parameters which i used to pass in reports and need to execute the reports through Rest API. I could see we can use REST API to get the metadata. Is there an availability to pull the report itself through API call? 


Friday, June 26, 2020 - 4:13:22 PM - Tommy Back To Top (86057)

Yes, works fine for the Administrator user but I need restricted users to access the api and view just the folders and reports they have access to....?


Friday, June 26, 2020 - 7:47:28 AM - Scott Murray Back To Top (86054)

I would first try running that in Admin Mode.  That error normally means the connected user does not have the appropriate role for connecting.


Thursday, June 25, 2020 - 7:23:04 PM - Tommy Back To Top (86047)

My report server has many folders categorized by department. I want to connect to the API as a normal user to retrieve just the reports I have access to.. But get access denied? Is this possible?

https://mydevreportserver/reports/api/v2.0/reports

I get this...

"The permissions granted to user 'mydevreportserver\\ExampleSSRSUser' are insufficient for performing this operation."

Friday, October 26, 2018 - 10:03:37 AM - Scott Brian Murray Back To Top (78064)

 Mimique,

I am not sure why you would be getting the 500 error.  Would need to see what else was being posted.


Thursday, October 25, 2018 - 10:13:11 PM - Mimique Back To Top (78061)

Header "Content-Type: application/json" is required when adding a new folder using POST method. With the header, the request doesn't work for me, would returns 500.


Friday, October 05, 2018 - 3:15:36 PM - Arun Back To Top (77870)

Thanks Scott for this nice article. Can we embed the SSRS report into the MVC application as how we embed Power BI report in mvc appl. We are using SSRS2017 and we have alreday embeded Power Bi reports to pur Web App. Trying to embed SSRS(not through calling teh direct Report URL and embeding on teh IFrame)Looking same way like how we embed Power BI.


Tuesday, July 10, 2018 - 6:56:47 PM - Oscar Back To Top (76606)

The SSRS was always running on a browser, by creating this API make it more robust and you could create a web page  to query this metadata and create your own reports inventory that display in your own way, very clear and detail information Scott,

thanks



download





Recommended Reading

SQL Server Reporting Services (SSRS) Log Files for Troubleshooting

Find Tables, Columns or Stored Procedures used in a SQL Server Reporting Services Report

PowerShell Commands for SQL Server Reporting Services

SSRS ReportServer Database Overview and Queries

Move SQL Reporting Services to Another Server








get free sql tips
agree to terms