How to use JSON data in SSRS?

In this article, we will explore the concept of using JSON data in SQL Server Reporting Services (SSRS). This usage concept will include a different approach than the usual methodologies because we will take advantage of SQL Server R service support.

What is JSON?

JSON is an abbreviation for JavaScriptObjectNotation. The main purpose of JSON data was to provide data transfer between server and web applications for JavaScript but today’s JSON is the most popular data interchange format. JSON offers two advantages to us. These are; light-weight text based format easily readable by humans. Currently, JSON is not only used in JavaScript applications, it is also used in all popular software programing languages like JavaScript, C++, C#, Perl, Java, Python, Php etc.

What is the JSON data structure?

The JSON object structure is based on a set of key-value pairs. A JSON object structure starts with left brace “{“and ends with right brace “}”. A Key defines the property of key-value pair and value stores the data of key-value pair. Keys and values are separated with colon sign “:”. Keys must be string and values can be any data type of JSON data type (string, number, object, array, boolean or null). Key-value pairs are separated with a comma. The following example shows a very simple form of JSON data.

{
“Id”: “22”,
“first_name”: “Tom”,
“e_mail”: “tom@bymail.com”
}

How to get JSON data with the SQL Server R Service?

SQL Server 2016 and higher versions are capable of storing and parsing JSON data. Also, another capability of SQL Server is to convert JSON data into tables. SQL Server offers the table-valued function OPENJSON, this function helps to parse and query JSON data types.

After these brief explanations about JSON, let’s focus on our main topic. SSRS does not provide any organic JSON data source, so we need to find an alternative solution to get and use JSON data in SSRS. Generally, reporting development needs to source JSON data through a web site. The main solution of this issue is to use OLE Automation procedures. This article Consuming JSON Formatted API Data in 2016 provides all details about how to get JSON data over a web site using OLE Automation. An alternative of this solution is to use R language support of SQL Server. Now we will focus about this solution approach. If you have some interest in the R language you can imagine that it can handle this issue. We can find several packages for R script which helps us to get and parse JSON data. Jsonlite is the one of the package which helps to get and parse JSON data in R script. We need to complete the following steps for this alternative solution.

Enable external scripts in SQL Server.
Install Jsonlite package for SQL Server R Service.
Get JSON data from a website and transform this JSON data to table with help of R.
Use this transformed data in SSRS.

The following query will enable external scripts in SQL Server.

EXEC SP_CONFIGURE 'external scripts enabled',1

RECONFIGURE

We will run the below query which helps to check configuration R script.

EXEC sp_execute_external_script

@language = N'R'

,@script = N'print("R script enable")'

Now we will check the installation of Jsonlite package. When you try to execute the following query and if you experience an error such as the image below, it means that you have to install Jsonlite package to SQL Server R service.

EXEC sp_execute_external_script

@language = N'R'

, @script = N' library(jsonlite)'

The following steps help us to install Jsonlite package.

Open the SQL Server R Services installation path

You can find it in this folder
“C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin”

Run the console with administrator rights

Type install.packages(“jsonlite”) in R console and then press the enter

We need to install curl package because it is the general network (http/ftp/…) client interface.

Type install.packages(“curl”) in R console and then press the enter

Now, we will get JSON data from a website. In the following demonstration, we will use JSONPlaceholder website for JSON data samples. This website offers free and online JSON data for testing. We will use posts data and this JSON data structure includes “userId”, “id”, “title” and “body” keys.

In this step, we will get JSON data from the website and convert it to table with help of R in SQL Server. We will explain the R code first line of code initialize the package of Jsonlite and in the second line use fromJSON function. This function helps us to get and parse JSON data and then we assign this data to mydata variable and this variable is output of our R script. Finally, we will parse the JSON data to table WITH RESULT SETS command.

EXEC sp_execute_external_script

@language = N'R'

,@script = N'library(jsonlite)

mydata <- fromJSON("https://jsonplaceholder.typicode.com/posts" target="_blank" rel="nofollow")'

, @output_data_1_name = N'mydata'

WITH RESULT SETS ((userId int ,Id int , title varchar(max),body varchar(max)))

Using JSON data in SSRS

In this section, we will quickly use the R external script in SQL Server Report Builder and visualize the JSON data in a sample SSRS report.

Launch the SSRS Report Builder
Right click Data Sources and select Add Data Source

Select Use a connection embedded in my report and then give a name to data source and then click Build

In the Connection Properties screen, fill the server name and select the database. In this demonstration we don’t need to get any data from any database for this reason we can select tempdb
Click Test Connection and ensure your connection settings and then click OK

Right click Datasets folder icon and select Add Dataset
Chose Use a dataset embedded in my report and then select the data source which we created in the previous step

In this step we will create a stored procedure in tempdb and then we will use this stored procedure in the SSRS report builder to populate the data

Use tempdb

CREATE or ALTER PROC GetJsonWeb

EXEC sp_execute_external_script

@language = N'R'

,@script = N'library(jsonlite)

mydata <- fromJSON("https://jsonplaceholder.typicode.com/posts" target="_blank" rel="nofollow")'

, @output_data_1_name = N'mydata'

WITH RESULT SETS ((userId int ,Id int , title varchar(max),body varchar(max)))

Paste the following query to Query Designer panel and click the (!) exclamation sign

You can see the converted JSON data table columns under the Datasets folder
Click Insert tab and select Insert table and then drop the table component into the SSRS report design panel

Click Run button. Our SSRS report will look like the below image

If you want, you can deploy your report to the SQL Server Report Server and you can use your report in Reporting Server web portal.

Conclusion:

In this article, we looked at how to get JSON data from any website with help of the SQL Server R Service. If your JSON is in basic format and you don’t want to do much effort, you can use this alternative approach. At the same time, you can use this method in SSRS.

FAQs

Can we convert table data to JSON in R?

Yes, we can use the toJSON function in R

What is the advantage of JSON over XML?

JSON has light-weight format and this advantage make it faster than XML.

Which data types are supported by JSON?

Integer, float or double
Boolean
Array
Null

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

What is JSON?

What is the JSON data structure?

How to get JSON data with the SQL Server R Service?

Using JSON data in SSRS

Conclusion:

FAQs

Related posts: