How to read REST API in SSRS Reports (JSON / XML / Call SOAP Web Service)

Posted on by
Contents hide
1 Introduction (Read REST / SOAP API in SSRS)
2 Requirements:
3 Getting Started
3.1 Configure Data Source in Gateway (ZappySys JSON / REST API Driver Example)
3.1.1 Configure Data Gateway User / Port
3.1.2 Configure Data Source
3.1.3 Test SQL Query / Preview Data
3.2 Configure SSRS Report Data source (Use ODBC Connection)
3.3 Create a new SSRS Report
4 Working with parameters in SSRS (Dynamic SQL Query)
5 Working with multi-value parameters in SSRS (Dynamic SQL Query)
6 Working with SSRS dataset filters (Client-side filtering)
7 Working with date parameters in SSRS Reports
8 Pass parameters in a Report URL
9 Read JSON files in SSRS Reports
10 Reading XML Files in SSRS Reports
11 REST API Security – Working with OAuth (Facebook API example)
12 POST Parameters to SSRS Report (avoid GET for long string in URL)
13 Deploying SSRS Report (for ODBC Connection)
13.1 Things to know about SSRS 2017 or Higher / Report Builder
14 Calling SOAP XML Web Service in SSRS (SQL Reporting Services)
14.1 What is SOAP Web Service?
14.2 What is WSDL
14.3 Example SQL Query for SOAP API call using ZappySys XML Driver
14.4 Video Tutorial - Introduction to SOAP Web Service and SoapUI tool
14.5 Using SoapUI to test SOAP API call / Create Request Body XML
14.6 Create DSN using ZappySys XML Driver to call SOAP API
14.7 Preview SOAP API Response / Generate SQL Code for SOAP API Call
14.8 Preview Data
14.9 Generate Code Option
15 SOAP / REST API pagination in SSRS Reports
15.1 Paginate by Response Attribute
15.2 Paginate by URL Parameter (Loop until certain StatusCode)
15.3 Paginate by URL Path (Loop until no record)
15.4 Paginate by Header Link (RFC 5988)
16 SOAP / REST API Error Handling in SSRS Reports
16.1 METHOD 1 - Using Error Handling Options
16.1.1 When to use?
16.1.2 Scenario 1
16.1.3 Scenario 2
16.2 METHOD 2 - Using Connection [Retry Settings]
17 Security / Connection Types (Options for HTTP, OAuth, SOAP) in SSRS Reports
17.1 HTTP Connection
17.2 OAuth
18 Other Consideration for Calling Web API in SSRS Reports
18.1 API Limit / Throttling
18.2 2D Array Transformation
19 Working with pagination to read REST API using SSRS
20 Reading API data from SQL Server Stored Procedure
21 Handling errors to read REST API using SSRS
22 Troubleshooting SSRS Issues
22.1 Report Builder Hangs / ODBC Connection errors in SSRS 2017 or Higher
23 Conclusion
24 References
24.1 More from ZappySys Blog

Introduction (Read REST / SOAP API in SSRS)

JSON to SQL ServerIn this tutorial, you will learn how to read REST API in SSRS (SQL Server Reporting Services). Traditionally to read data from XML / JSON Files or RESTful web service in SSRS, some sort of ETL approach was needed (i.e Load data from JSON / XML to SQL server first and then read from SQL Server to SSRS).

This article will focus on very unique and simple approach, we will use  ZappySys drivers for JSON / XML / REST API to read REST API or JSON Files in SSRS reports. ZappySys JSON driver allows you to query any API source or JSON / XML files using familiar SQL query language.

Example purpose, we will only show JSON file format but the same approach can be used for reading from XML files or Calling XML SOAP Web Services in SSRS. We will also learn how to connect using OAuth to Facebook. We can also connect to Google, Azure, Facebook, Twitter, DropBox, Github, Instagram and thousands of other sources using same techniques.

Finally, we will also learn how to handle errors, pagination to handle several pages and how to read an XML file in Reporting Services

Requirements:

  1. Make sure you have SSRS designer installed (You can download SSDT BI for Visual Studio 2015).
  2. In addition, we will need ZappySys ODBC PowerPack Installed.

Getting Started

You can access JSON / XML data in SSRS three different ways. We will only focus on one method in this article but please check other articles by clicking below links.

  1. ZappySys Data Gateway – This method uses Microsoft SQL Server Connection Type option to connect to ZappySys Data Gateway. Check this
  2. Microsoft SQL Server (Linked Server) – This connection type which connects to SQL Server Linked Server. Check this
  3. ODBC (Use ZappySys ODBC Driver) – You can use ODBC connection option in Report when you define data source (NOTE: There are some known issues with this method so we will not discuss)
NOTE: If you are using Method#3 (ODBC Connection) in SSRS 2017 or higher then you may have to do small changes listed under Troubleshooting section in this article. You may consider Method#2 or #3 if Direct ODBC Connection is not working for you.

Now lets look at steps to call REST API in SSRS (SQL Server reporting Services).

Configure Data Source in Gateway (ZappySys JSON / REST API Driver Example)

Now let's look at how to configure ZappySys Data Gateway. This feature acts as a bridge between Client App and ZappySys Drivers. Using data gateway you can use ZappySys Drivers inside applications / operating systems where ZappySys drivers may not be available directly for some reason (e.g. You don't have access to Server for Installation or System does not support ODBC drivers like JAVA programs). Click here to read more on various use cases of Data Gateway.

Configure Data Gateway User / Port

Now let's look at steps to configure Data Gateway after installation. We will also create a sample data source for ODATA API (i.e. JSON based REST API Service).
  1. Assuming you have installed ZappySys ODBC PowerPack using default options (Which also enables Data Gateway Service)
  2. Search "Gateway" in your start menu and click ZappySys Data Gateway
    Open ZappySys Data Gateway

    Open ZappySys Data Gateway

  3. First make sure Gateway Service is running (Verify Start icon is disabled)
  4. Also verify Port on General Tab
    Port Number setting on ZappySys Data Gateway

    Port Number setting on ZappySys Data Gateway

  5. Now go to Users tab. Click Add icon to add a new user. Check Is admin to give access to all data sources you add in future. If you don't check admin then you have to manually configure user permission for each data source.
    Add Data Gateway User

    Add Data Gateway User

 

Configure Data Source

  1. After user is added, go to Data Sources tab. Click Add icon to create new data source. Select appropriate driver based on your API / File format. You can choose Generic ODBC option to read data from ODBC DSN or use Native Driver option. 
    NOTE: Whenever possible use native driver option for better performance / security and ease of use.
    Add Gateway Data Source (Native JSON Driver)

    Add Gateway Data Source (Native JSON Driver)

  2. Click on "Edit" under Data source and configure as per your need (e.g. Url, Connection, Request Method, Content Type, Body, Pagination etc.). For this demo we are going to pick simple JSON REST API which doesn't need any authentication.  Enter following URL. 
    https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json
  3. You can also view response structure and select default hierarchy (i.e. Filter) like below (Select Array Icon) for data extraction.
    Configure JSON API Data source

    Configure JSON API Data source

Test SQL Query / Preview Data

  1. Now go to Preview Tab. You can click Preview button to execute default query OR Select Table name from dropdown to generate SQL with column names.
    JSON / REST API Driver Query Preview / Query Examples (Read REST API or JSON Files)

    JSON / REST API Driver Query Preview / Query Examples (Read REST API or JSON Files)

  2. You can also click Query Builder to generate SQL using different options in WITH clause. ANy setting you specify in WITH clause will override UI settings we applied in previous steps.
    Using SQL Query Builder (For Files or REST / SOAP API - JSON / XML / CSV Format)

    Using SQL Query Builder (For Files or REST / SOAP API - JSON / XML / CSV Format)

  3. There is another useful option for code generation. Select your Language and quickly copy code snippet. See below Example of XML Driver Query to call SOAP API.
    Generate Example Code for ZappySys Driver

    Generate Example Code for ZappySys Driver

  4. Click OK to Close Data Source UI
  5. Once data source is tested and configured you can click Save button in the Gateway UI toolbar and click Yes for Restart Service.
 

Configure SSRS Report Data source (Use ODBC Connection)

Once you are done configuring ODBC DSN for REST API URL. The next step will be to create a new SSRS project.

  1. In SSRS, in a new Reporting Service Project, add a new Data Source:
    Add REST data source in SSRS
  2. Also, specify a name for the data source and in type select Microsoft SQL Server. Press Build to select the Data gateway connection information as below.
    Connect to JSON / REST API / SOAP in SSRS (Using ZappySys Data Gateway)

    Connect to JSON / REST API / SOAP in SSRS (Using ZappySys Data Gateway)

  3. Click OK to save our data source. We will use this Data source in next section.

Create a new SSRS Report

Once your DSN is setup we can create report. There are two ways you can create reports.

  1. Create SSRS Reports using Report Builder App
  2. Create SSRS Reports using Visual Studio (SSDT) – Download SSDT from Here

In this section we will see second method (Visual Studio Method). Now let’s create a new SSRS report which uses the API data source we created in the previous section.

  1. Open Existing SSRS Project or Create a new Reporting Services Project by clicking File > New. When Dialog Opens expand Business Intelligence category and select Reporting Services Project
  2. When Solution opens Right-click on the Reports node and click Add New Report (Wizard mode)
    Add report
  3. Press the query builder button
    Create query from REST API
  4. Additionally, press the Add table icon and go to Views. Select value(DATA) and press add:REST API view SSRS
  5. Press next in the following option until the completing wizard and specify a name for the report and check the option to Preview a report:
  6. If everything is OK, you will be able to see the report and export the REST API data to Word, Excel, PowerPoint, etc:Export REST to other formats

Export JSON and REST API to word, Excel

Working with parameters in SSRS (Dynamic SQL Query)

So far we have seen how to pull data from Table exposed by our ODBC DSN for API Service. In the real world, you may have some sort of parameters passed to your API Service (e.g. CustomerID or Date Parameters).

So let’s configure our report to use parameters.

  1. We can work with parameters in SSRS. You can Add a Parameter in Parameters with the add parameter option:
    Add parameter in SSRS
  2. We will create a parameter named CustomerID and it will be used to assign a customerID parameter:Parameter name
  3. Create a data source and a dataset, drag and drop your columns to the report:Drag and drop columns SSRS
  4. Right-click your DataSet (which was generated by Report Wizard) and select DataSet properties:Dataset properties
  5. Go to query and select Fx to create a dynamic SQL Query for our data source (Use expression / parameters in SQL):
    Just keep in mind that, if you use dynamic SQL for the data source (i.e. Using Expression in SQL) then Fields of that data source won’t refresh automatically when you save the UI. Workaround for this issue simple. Just use a static query for the first time to generate desired fields for the dataset. Once data source fields are generated you can add expression in your SQL Query.
    Static Query:
    SELECT col1, col2 FROM rows WITH (SRC=’http://abc.com/invoice?year=2017′)
    Dynamic Query:
    =”select col1, col2 from rows WITH (SRC=’http://abc.com/invoice?year='” + Parameters!Year.Value + “‘)”
    Query expression in SRS
  6. On the expression dialog box, Enter SQL Query like below
  7. If you don’t want to use URL from DSN then you can supply API URL right inside your SQL Query like below.
  8. You can now write a name as a parameter and receive the results:
    parameters with REST API using SSRS

Working with multi-value parameters in SSRS (Dynamic SQL Query)

If you have a parameter which is multi-valued and you want to use it to filter data then follow the above section and in the expression dialog box use JOIN function to join the values, e.g.:

=SELECT CustomerID, CompanyName, ContactName FROM value where CustomerID IN ('" & JOIN(Parameters!CustomerID.Value, "','") & "')"

Working with SSRS dataset filters (Client-side filtering)

Sometimes its necessary to do filtering on data once it’s retrieved from the server. Dataset Filter is SSRS feature which does data filtering on the client side. To filter the report previously created, right click on the dataset and go to Filters. Press Add.

In this example, we will show how to show a customer with the customerID equal to ALFKI (or click on fx icon to use the expression for dynamic filter:
Add filter for the data set

Add filter SSRS REST API 

Working with date parameters in SSRS Reports

Now lets look at how to handle date parameters and format them correctly. Most JSON /XML REST API handle dates in ISO format (i.e. yyyy-MM-ddTHH:mm:ss.fffZ). You can convert or format text parameters to date using the CDate function. The following example converts the parameter mydate from text to a date in SSRS:

Next, we will show how to convert the date format to the format yyyy-MM-ddT00:00:00:

Also, we will work with a REST API URL with data and retrieve the results:

Furthermore, in the ODBC Administrator add a connection to the previous URL (Or use URL inside SQL query WITH caluse as per previous section example):

ODBC connection to REST API

URL with dates in REST API

The following example will convert the parameter to the same format as the URL and compare the OrderDate to the specified format:

 

Pass parameters in a Report URL

In addition, when the report is published, you can specify a parameter in a URL. The following example shows how to pass a date in a parameter named mydate the value 1997-10-13:

 

Read JSON files in SSRS Reports

Also, it is possible to connect to a JSON file instead of REST API. The steps are the same that the ones explained previously with the difference that we need to specify the path when we create the ODBC connection. The following example with show how to specify the path of a local JSON file in the connection:

JSON file in SSRS

SSRS access to a local JSON file.

Reading XML Files in SSRS Reports

The next example will show how to read an XML file using SSRS.

The XML name is ZS.xml and it contains the following content:

This example will read an XML file named ZS.xml. First, in ODBC Administrator add the ZappySys ODBC XML Driver:

XML ODBC in SSRS

ODBC SSRS FOR XML

In data source, specify the path of the file:

ODBC to XML

XML configuration

Once created the connection, create your report in SSDT in the query, add the following query:

SELECT author, genre, title, price, publish_date
FROM DATA.”store.book”

Create query to XML in SSRS

Create a query to XML in SSRS

Finally, you can visualize your report in SSRS:

SSRS reading XML

SSRS reading XML

REST API Security – Working with OAuth (Facebook API example)

If you API requires credentials then no worries, ZappySys Drivers come with many security standards support. Most common credential types are Basic Auth and  OAuth. These are open standard to access Web Services. In this new example, we will show how to read Facebook information in SSRS (Call Facebook Graph API).

For demo we will read the number of friends on Facebook:

Friends on Facebook

Friends on Facebook

First, we will open the ODBC Data Source:

ODBC Data Source

ODBC Data Source

In Authentication type, select OAuth. We will use the following link to get the friends information on Facebook:

This link retrieves information of my friends. For more information about the Facebook graph API, check our references.

 

Facebook connection properties

Secondly, go to OAuth settings and select Facebook as the OAuth Provider. You will need a Client Id and a Client Secret. To get a client id and a client secret, you will need to create a Facebook application here. For more information about creating a Facebook application, check this link.

Scopes will use user_friends to get Facebook friends information.  For a list of scopes, please check here.

OAuth settins

OAuth settings

Once that the connection is created, you need to create an SSRS report using the ODBC connection just created. In the query, we will create the following query:

 

SSRS query to Facebook

SSRS query to Facebook

If everything is OK, you will be able to see the number of Facebook friends:

Facebook friends in SSRS

Facebook friends in SSRS

POST Parameters to SSRS Report (avoid GET for long string in URL)

You can call reports from browser using following way (Pass parameters in URL). Below Method by default sends GET requests to Report Server and render report in HTML format in your browser.

However there is a problem if you have a very long string as parameter value. In that case use FORM POST method described in this article.

You can try yourself. Just create an html file on your machine and paste below code. Run it in a browser (If you have UAC ON then First launch Browser as Admin and then paste full path of your HTML file). Click on a button displayed in that page to mimic POST.

TestPost.html

You can use expression like below in your Report to confirm Data Length of posted Parameter

Also you can use below expression to make your query dynamic as below

 

 

Deploying SSRS Report (for ODBC Connection)

NOTE: In this section we will make sure few thing for ODBC Connection Type. If you used Data Gateway approach then you may skip this section.

Once you done with design and testing of reports, our next step would be deploy reports to SSRS Server. This section describes things to make sure when you deploy reports.

Find DSN Name and Full ConnectionString of original DSN

  1. First thing to make sure you install ODBC PowerPack on the report server too (Unless you are using SQL Server Connection Type to connect to Gateway and you dont plan to run it on SSRS Server)
  2. Once ODBC PowerPack is installed on the server next step is to setup DSN Used by Reports.  SSRS Service is 64 bit so DSN must be 64 bit and must be named Exact same way just like how you used during Design Phase on Client PC where reports are designed.
  3. To Setup DSN exact same way try this step. First go to Client PC where reports are designed. Open ODBC DSN which you used for Report. Copy two things 1) Name of your DSN, and Find Copy Connection String Button to copy full connection string of your DSN.

Create new DSN(s) on SSRS Server using original settings

  1. Now Remote Desktop (rdp) to SSRS Server, search of odbc 64 bit. Open it and go to System Tab.
    Create System ODBC DSN (64-bit)

    Create System ODBC DSN (64-bit)

  2. Click Add to create new DSN just like we did very beginning of this article. Select Driver you wish to use.
  3. Once Driver UI opens up change default DSN name to exact same name like we gave it for SSRS Report on client PC.
  4. find Load Connection String Button and paste connection string we obtained in previous step.
    ZappySys ODBC Driver - Load Connection String Example

    ZappySys ODBC Driver – Load Connection String Example

  5. Click Test connection see everything good. Click OK to save DSN
  6. Repeat these steps for each DSN used in SSRS Report.

Things to know about SSRS 2017 or Higher / Report Builder

We had few users who reported issue with SSRS 2017 after deployment (Everything worked fine during design time). You may get below error when try to preview report from server URL. You may sometimes notice that after saving report in Report Builder if you try to preview Report Builder hangs and you have to restart SSRS service. If you notice any such issue you may have to apply following fix..

Error:

In such case check steps listed in Troubleshooting Section later this article.

 

Calling SOAP XML Web Service in SSRS (SQL Reporting Services)

So far we have looked at examples to consume data using JSON driver. Now let’s look at an example, to call XML SOAP Web Service in SSRS.

To call SOAP API you need to know Request XML Body Structure. If you are not sure how to create SOAP Request body then no worries. Check this article to learn how to generate SOAP Request body using the Free tool SoapUI. Basically, you have to use SoapUI to generate Request XML and after that, you can replace parameters as needed in the generated body.

What is SOAP Web Service?

If you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept about SOAP Web service standard from this link There are two important aspects in SOAP Web service.
  1. Getting WSDL file or URL
  2. Knowing exact Web Service URL

What is WSDL

In very simple term WSDL (often pronounced as whiz-dull) is nothing but a document which describes Service metadata (e.g. Functions you can call, Request parameters, response structure etc). Some service simply give you WSDL as xml file you can download on local machine and then analyze or sometimes you may get direct URL (e.g. http://api.mycompany.com/hr-soap-service/?wsdl )

Example SQL Query for SOAP API call using ZappySys XML Driver

Here is an example SQL query you can write to call SOAP API. If you not sure about many details then check next few sections on how to use XML Driver User Interface to build desired SQL query to POST data to XML SOAP Web Service without any coding. 
SELECT * FROM $
WITH(
	 Src='http://www.holidaywebservice.com/HolidayService_v2/HolidayService2.asmx'
	,DataConnectionType='HTTP'
	,CredentialType='Basic' --OR SoapWss
	,SoapWssPasswordType='PasswordText'
	,UserName='myuser'
	,Password='pass$$w123'
	,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'
	,ElementsToTreatAsArray='HolidayCode'	
	,RequestMethod='POST'	
	,Header='Content-Type: text/xml;charset=UTF-8 || SOAPAction: "http://www.holidaywebservice.com/HolidayService_v2/GetHolidaysAvailable"'
	,RequestData='
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:hol="http://www.holidaywebservice.com/HolidayService_v2/">
   <soapenv:Header/>
   <soapenv:Body>
      <hol:GetHolidaysAvailable>
         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->
         <hol:countryCode>UnitedStates</hol:countryCode>
      </hol:GetHolidaysAvailable>
   </soapenv:Body>
</soapenv:Envelope>'
)
Now let's look at steps to create SQL query to call SOAP API. Later we will see how to generate code for your desired programming language (e.g. C# or SQL Server)

Video Tutorial - Introduction to SOAP Web Service and SoapUI tool

Before we dive into details about calling SOAP API using ZappySys XML Driver, lets first understand what is SOAP API and how to create SOAP requests using SoapUI tool. You will learn more about this process in the later section. The video contains some fragment about using SOAP API in SSIS but just ignore that part because we will be calling Soap API using ZappySys ODBC Driver rather than SSIS Components.  

Using SoapUI to test SOAP API call / Create Request Body XML

Assuming you have downloaded and installed SoapUI from here, now we are ready to use WSDL for your SOAP Web Service Calls. If you do not have WSDL file or URL handy then contact your API provider (sometimes you just have to add ?wsdl at the end of your Service URL to get WSDL so try that. Example: http://mycompany/myservice?wsdl ). If you don't know what is WSDL then in short, WSDL is Web service Description Language (i.e. XML file which describes your SOAP Service). WSDL helps to craft SOAP API request Body for ZappySys XML Driver. So Let's get started.
  1. Open SoapUI and click SOAP button to create new SOAP Project
  2. Enter WSDL URL or File Path of WSDLFor example WSDL for our sample service can be accessed via this URL 
    http://www.dneonline.com/calculator.asmx?wsdl
    Create new SOAP API Project in SoapUI tool for SOAP API Testing
    Create new SOAP API Project in SoapUI tool for SOAP API Testing
  3. Once WSDL is loaded you will see possible operations you can call for your SOAP Web Service.
  4. If your web service requires credentials then you have to configure it. There are two common credential types for public services (SOAP WSS or BASIC )
    1. To use SOAP WSS Credentials select request node and enter UserId, Password, and WSS-PasswordType (PasswordText or PasswordHash)
      Configure SOAP WSS Credentials for SoapUI (SOAP API Testing Tool)
      Configure SOAP WSS Credentials for SoapUI (SOAP API Testing Tool)
    2. To use BASIC Auth Credentials select request node and double-click it. At the bottom click on Auth (Basic) and From Authorization dropdown click Add New and Select Basic.Configure Basic Authorization for SoapUI (SOAP API Testing Tool)
      Configure Basic Authorization for SoapUI (SOAP API Testing Tool)
  5. Now you can test your request first Double-click on the request node to open request editor.
  6. Change necessary parameters, remove optional or unwanted parameters. If you want to regenerate request you can click on Recreate default request toolbar icon. Create SOAP Request XML (With Optional Parameters)
    Create SOAP Request XML (With Optional Parameters)
  7. Once your SOAP Request XML is ready, Click the Play button in the toolbar to execute SOAP API Request and Response will appear in Right side panel. Test SOAP API using SoapUI Tool (Change Default XML Body / Parameters, Execute and See Response) Test SOAP API using SoapUI Tool (Change Default XML Body / Parameters, Execute and See Response)

Create DSN using ZappySys XML Driver to call SOAP API

Once you have tested your SOAP API in SoapUI tool, we are ready to use ZappySys XML driver to call SOAP API in your preferred BI tool or Programming language.
  1. First open ODBC Data Sources (search ODBC in your start menu or go under ZappySys > ODBC PowerPack > ODBC 64 bit)
  2. Goto System DSN Tab (or User DSN which is not used by Service account)
  3. Click Add and Select ZappySys XML Driver ZappySys ODBC Driver for XML / SOAP API ZappySys ODBC Driver for XML / SOAP API
  4. Configure API URL, Request Method and Request Body as below ZappySys XML Driver - Calling SOAP API - Configure URL, Method, Body ZappySys XML Driver - Calling SOAP API - Configure URL, Method, Body
  5. (This step is Optional) If your SOAP API requires credentials then Select Connection Type to HTTP and configure as below. ZappySys XML Driver - Configure SOAP WSS Credentials or Basic Authorization (Userid, Password)
    ZappySys XML Driver - Configure SOAP WSS Credentials or Basic Authorization (Userid, Password)
  6. Configure-Request Headers as below (You can get it from Request > Raw tab from SoapUI after you test the request by clicking the Play button) Configure SOAP API Request Headers - ZappySys XML Driver Configure SOAP API Request Headers - ZappySys XML Driver
  7. Once credentials entered you can select Filter to extract data from the desired node. Make sure to select array node (see special icon) or select the node which contains all necessary columns if you don't have array node. Select Filter - Extract data from nested XML / SOAP API Response (Denormalize Hierarchy) Select Filter - Extract data from nested XML / SOAP API Response (Denormalize Hierarchy)
  8. If prompted select yes to treat selected node as Array (This is helpful when you expect one or more record for selected node) Treat selected node as XML Array Option for SOAP API Response XML Treat selected node as XML Array Option for SOAP API Response XML

Preview SOAP API Response / Generate SQL Code for SOAP API Call

Once you configure settings for XML Driver now you can preview data or generate example code for desired language (e.g. C#, Python, Java, SQL Server). Go to Preview tab and you will see default query generated based on settings you entered in previous sections. Attributes listed in WITH clause are optional. If you omit attribute in WITH clause it will use it from Properties tab.

Preview Data

Preview SOAP API Response in ZappySys XML Driver Preview SOAP API Response in ZappySys XML Driver

Generate Code Option

Generate Example Code for ZappySys Driver
Generate Example Code for ZappySys Driver

SOAP / REST API pagination in SSRS Reports

Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that ZappySys ODBC Driver includes many options to cover virtually any pagination method.
Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):

Paginate by Response Attribute

This example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page. 
SELECT * FROM $
WITH(
SRC=@'https://zappysys.com/downloads/files/test/pagination_nextlink_inarray_1.json'
,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']
)

Paginate by URL Parameter (Loop until certain StatusCode)

This example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found). 
SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'
,PagingMode='ByUrlParameter'
,PagingByUrlAttributeName='page'
,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'
,PagingByUrlCheckResponseStatusCode=401
,IncrementBy=1
)

Paginate by URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found). 
SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/cust-<%page%>.xml'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

Paginate by Header Link (RFC 5988)

API like GitHub / Wordpress use Next link in Headers (RFC 5988) 
SELECT * FROM $
LIMIT 25
WITH(
	 Src='https://wordpress.org/news/wp-json/wp/v2/categories?per_page=10'
	,PagingMode='ByResponseHeaderRfc5988'
	,WaitTimeMs='200' --//wait 200 ms after each request
)
 

SOAP / REST API Error Handling in SSRS Reports

Sometimes errors occur... they just do and there is nothing you can do! Or can you? Actually, in ODBC PowerPack you can handle them in two ways.

METHOD 1 - Using Error Handling Options

When to use?

You may want to use them when your source is a resource located on the Internet; e.g. a file on a website, a file on an FTP server or just a plain API HTTP response. By default, when a remote server returns an error, data retrieval is stopped, an error is raised and no data is given back to you. This might not be always desirable.

Scenario 1

Imagine a scenario, that there is a web server which each day at 12 AM releases a new JSON file with that day's date as filename, e.g. http://www.some-server.com/data/2018-06-20.json. And, of course, you want to download it and use it daily in your Power BI report. But you have a problem: Power BI report data sources are refreshed each hour and you may get HTTP 404 status code (no file was found) when a file is not released yet. Which consequentially means other data sources won't be updated as well and you will see old and cached data on the report. That's where you could use Continue on any error or Continue when Url is invalid or missing (404 Errors) to avoid an error being raised and let other data sources to be updated.

Scenario 2

Another scenario is when you expect a web server to raise some kind of HTTP error when accessing a URL. You don't want ODBC Data Source to raise an error but instead, you want to get response data. That's where you can use Continue on any error or alike together with  Get response data on error to continue on an error and get the data:

METHOD 2 - Using Connection [Retry Settings]

Another scenario you may run into is a buggy web server. You ask it to give you some file or data and it, like a snotty kid, just doesn't give it to you! You have to ask twice or thrice before it does its job. If that's the case, you have to retry HTTP requests using Connection:

Security / Connection Types (Options for HTTP, OAuth, SOAP) in SSRS Reports

If you need to authenticate or authorize your user to access a web resource, you will need to use one of the Connections:
  • HTTP
  • OAuth
ZappySys XML Driver - HTTP and OAuth Connection Types

HTTP Connection

Use HTTP Connection for simple Windows, Basic, NTLM or Kerberos authentication. Just fill in a username and a password and you are good to go!
You can also use HTTP Connection for more sophisticated authentication like:
  • SOAP WSS (when accessing a SOAP WebService)
  • Static Token / API Key (when need to pass an API key in HTTP header)
  • Dynamic Token (same as Static Token method except that each time you need to log in and retrieve a fresh API key)
  • JWT Token (As per RFC 7519)

OAuth

If you are trying to access REST API resource, it is a huge chance, you will need to use OAuth Connection. Read this article to understand how OAuth authentication and authorization works and how to use it (article originally was written for SSIS PowerPack, but the concepts and UI stay the same): https://zappysys.com/blog/rest-api-authentication-with-oauth-2-0-using-ssis

Other Consideration for Calling Web API in SSRS Reports

There are few settings you can coder while calling Web API

API Limit / Throttling

While calling public API or other external web services one important aspect you have to check,  how many requests are allowed by your API. Especially when you use API pagination options to pull many records you have to slow down based on API limits. For example, your API may allow you only 5 requests per second. Use Throttling Tab on Driver UI to set delay after each request.

2D Array Transformation

If you are using JSON or XML API Driver then possible you may have to transform your data using 2D array transformation feature. Check this link for more information.  

Working with pagination to read REST API using SSRS

Another important aspect of any API call is handling pagination. When you have many records to read from API, most likely your API won’t send you data in one response. You have to loop through all pages of data until the last page is detected. ZappySys driver comes with many options to configure virtually any pagination strategy.

Here is the screenshot of Pagination options included in ZappySys JSON / XML API Driver:

Pagination to read REST API using SSRS

Moreover, you can configure the maximum page number and the response length. It is also possible to set the minimum response length.

In addition, you can also configure the page number increment and pagination mode. You can also specify conditions to detect the last page and more.

Reading API data from SQL Server Stored Procedure

If you want to read data from SQL Server Stored procedure rather than using Data gateway or ODBC connection then check this post.

Handling errors to read REST API using SSRS

The ZappySys ODBC PowerPack allows handling errors. For example, if the page is not found (error 404), the driver can continue working. You can also continue on any error. Continue on status code, stop the execution on error or more options:

SSRS - REST API Error Handling Options

SSRS – REST API Error Handling Options

Troubleshooting SSRS Issues

If you experience problems accessing data from ODBC data source, you may try to set up a data source in ZappySys Data Gateway and access it instead. Check how to troubleshoot errors when SSRS crashes to find out how to accomplish that.

Report Builder Hangs / ODBC Connection errors in SSRS 2017 or Higher 

Sometimes you may notice that after saving Report to Server in Report Builder when you preview it hangs or you may get below error. This issue may occur If you are using SSRS 2017 or higher and report is using ODBC Connection Type. If your preview hangs or get below error then you have to try few steps listed below to avoid such error. This error may occur in other version too but from our experience it mostly happens in newer SSRS version.

Steps to fix this error.

  1. First we have to Gac Newtonsoft.json.dll. For this search for “Powershell ISE” and right click and launch as Admin
  2. Run below command to register Dll
  3. Now go to SSRS Install folder typically found here
  4. Find rssrvpolicy.config . Backup the file before making any changes below. Right click on the file and edit it.
  5. n the above config file we need to add extract CodeGroup nodes for each ZappySys Driver dll Path which you like to use in SSRS Reports. For demo purpose we have added XML and JSON driver paths you can do more as needed. Basically in SSRS 2012 this may not be needed but in newer version of SSRS (i.e. 2017 and higher) Custom Drivers or Assemblies need Grant to execute under SSRS service. Here is the code you have to add in config file.
  6. Here is Full rsssrvpolicy.config code in case you like to review. Your file may slightly different so dont just copy full content as is.
     
  7. Thats it now Restart SSRS Service after above change and you should be fine. If you still face the issue we suggest you use Data Gateway approach.

Conclusion

To conclude, we can say that it is now easy to get JSON and REST API data in SSRS. We only need to download the ZappySys ODBC PowerPack and we will be able to access the data.

Furthermore, you learned how to work with parameters (Dynamic Queries), with dates and pass parameters to URL.

References

Finally, for more information about how to read REST API using SSRS you can check the following links:
⦁ Category Archives: ODBC PowerPack
⦁ Develop with the REST APIs for Reporting Services
⦁ Facebook Graph API

Keywords: SSRS JSON data source, SSRS REST API data source, SSRS SOAP Web Service data source, Call REST API in SSRS, Load JSON into SSRS, Read REST API in SSRS, Read XML SOAP Web Service in SSRS, Read JSON file in SSRS,
Read XML file in SSRS

More from ZappySys Blog

Posted in JSON File / REST API Driver, ODBC PowerPack, Reporting - Microsoft SSRS, REST API and tagged , , , , , .