By: Scott Murray | Updated: 2018-07-10 | Comments (17) | 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.
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.
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 .
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=*.
We can also combine multiple arguments using an “&” between each one: http://localhost/Reports_SSRS/api/v2.0/Reports?$select=*&$top=2 .
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') .
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.
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.
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.
Next, we execute the POST in Fiddler as shown below.
The end result is the addition of the new folder.
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.
Now we have a description for our folder, and of course you could adjust many of the other properties.
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.
Reviewing the report server, we see the noted folder is now removed.
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
- Check out the following resources:
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-07-10
