SSRS and REST API

Question

Hi,

I have been searching for a while now but I can't seem to find much information on this, and I was hoping someone could point me in the right direction.

Current scenario: data source uses the built-in SQL Server data processing extension to pull data from the sql database (direct connection to the database)

Desired scenario: data source calls an API endpoint that will in return provide the data to be used. Therefore, the data source would make an HTTP request passing a set of parameters. There is no direct connection to the database from the client, and the actual data would be protected behind a firewall (along with the API, which connects to the database to retrieve the data, which is then served to SSRS through the call)

Is this possible to accomplish?

Thank you in advance!

Best,
Izzy B.

Answer 1

Hi @Izzy Babcock ,
I'm sorry I don't know much about Develop with the REST APIs for Reporting Services. Microsoft SQL Server 2017 Reporting Services or higher version supports Representational State Transfer (REST) APIs. You can refer to: Develop with the REST APIs for Reporting Services.
Here are some examples of Rest ApI: https://github.com/Microsoft/Reporting-Services.
Hope this helps.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello,

I have been able to accomplish building RESTful APIs and consuming them with SSRS. I did search for this a couple of years back and wasn't able to find much on it either. I am assuming that you already have a set of end points that you will be using for your report. So just like stored procedures end points have URL parameters. Here are the steps:

1. On your report, create a Data Source and give it a desired name. I'll call it "Datasource_sample"
   a. Under Embedded Connection select "XML" for Type
   b. Under connection String hard code the end point and enter it as follows https://somehost/myendpoint.xml?Parameter1=thevalueIamPassing. Don't use the xpressions yet.
   c. Under the Credentials tab (directly below the General tab), configure the appropriate authentication. For my organization we use windows authentication. The owner of the API might take the credentials to authorize the user.
   d. Click Ok and the Data source will be created.
2. Create a Dataset and give it a desired name. I'll call it "Dataset_sample"
   a. Make sure that "Use dataset embedded in my report." is selected
   b. Under Data Source: select the Data source you just created. In my case it will be "Datasource_sample"
   c. Under Query Type, select Text
   d. Under Text type <Query></Query>
   e. Click on the [Refresh fields] button. This will extract all the data fields from the end point.
   f. Click Ok and the dataset will be ready for your report consumption.
3. You can now reference the Dataset and fields on your report. Go ahead and add a few fields to the report and run the report to make sure that your report is pulling data without issues.
4. After you have validated that your report is able to pull the data from the end point, now we are ready to make the Data Source dynamic. Earlier on we had hardcoded the parameter value of the end point to make sure that we can pull the data and fields. To do, go under the Data Source properties and click on the [Fx] button to create expressions for the end point. This will allow us to map report parameters to URL parameters. I am not going to cover here how to build SSRS expressions since I am assuming you already know how to do that. Once you have built the expression, you should be able to run the report using the report parameters that were mapped to the URL parameters.
5. If by any chance your end point fields need to be refreshed, you will need to:
   a. Backup the connection string under notepad or word
   b. Remove the connection string from the Datasource
   c. Hardcode the connection string using the end point with hard coded values and click OK.
   d. Open the dataset properties and click on the [Refresh fields] button. This will update the fields according to the end point.
   e. Click Ok
   f. Go back to Datasource properties and place back the expression that we had backed up on step 1. You may want to add any new parameters or remove parameters if those have changed.

If you have any more questions please email me at ******@framewaresolutions.com. I had to discover all of this on my own but now we have hundreds of reports consuming APIs. Please keep in mind that APIs might be a good option when reports don't fetch too much data as XML is very verbose. You will have to test that on your own and see if the response times are fast enough or meet your report SLA's. I have had some of our developers wanting to output reports that had 10s of thousands of rows and it would take several minutes to return the data, so we'd have to turn over to other solutions that would support JSON instead of XML, like Power BI for example. Anyway, just something to think about.