Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

SQL Server Reporting Services Rest API


By:   |   Read Comments (1)   |   Related Tips: > 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 Update:


next webcast button


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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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


Learn more about SQL Server tools