Using JSON as a data source for a SQL Server Reporting Services report

By:   |   Comments (4)   |   Related: > Reporting Services Data Sources


Can SQL Server Reporting Services (SSRS) use JSON as a data source for a report?


Well the answer to this question is sort of yes and sort of no. Probably not what you expected from a tip on MSSQLTips. Of course we are all for showing you possible options to make the best of SQL Server resources. Like many things in SQL Server, various alternatives exist to complete a task. For SSRS 2016, no direct connection to JSON exists as a data source. However, we can use an alternative method to utilize JSON as a data source in SSRS. For a more detailed introduction to JSON, I recommend looking at the following MSSQLTips:

JSON or Java Script Object Notation basically gives you the opportunity to move data from one place to another in a similar fashion to XML. As you begin to work with JSON, you will first realize that even though it is somewhat like XML, there are still some very big differences in the way that the data is actually housed and tagged. In essence, JSON is focused on providing a flexible, yet organized method of exchanging data. Like XML, it is self describing and uses a tags.

JSON in Power BI

As our JSON data source, we are going to use a JSON file sourced from the NASDAQ stock exchange, which is shown below. This file is an example of NASDAQ's data on Demand API file sets and can be downloaded at: The below file shows trade prices and times.

json trade file

So if we have some data in JSON format and SSRS does not currently support JSON as a data source, how do we get the data into SSRS? Fortunately, SQL Server 2016 (only) now provides support to query JSON data sets via the OpenJSON function. In effect we are querying the data set via this function which is our link to the JSON file. The OpenJSON function allows us to set, parse, and query the various levels found within a JSON file. For our example file above, the Symbol tag is the highest level of our file. Next we have SummarizedTrades tag. This tag contains several sub objects related to the price of the stock at a specific time.

We can use the below code to import and parse out the JSON text. The code does a couple of things. First, we import the file using OPENROWSET and Bulk Import. We could have just as easily read the JSON text from a field in the database. Next, we use the OpenJSON function to extract the data we would like to use in our report. To complete that task we must tell the function at which level we should start parsing; the line item,$[0].SummarizedTrades, starts the parsing with the first key (base 0) in SummarizedTrades Object.

FROM OPENROWSET (BULK 'C:\Users\SCOTT\Downloads\GetSummarizedTrades.json', SINGLE_CLOB) as jsonfile
CROSS APPLY OPENJSON(BulkColumn,'$[0].SummarizedTrades')
   Time DATETIME  '$.Time',
   FIRST FLOAT '$.First',
   LAST FLOAT  '$.Last',
   HIGH FLOAT  '$.High',
   LOW FLOAT  '$.Low',
   Volume INT  '$.Volume'
   ) AS Trade; 

The With clause tells the OpenJSON function where and which fields to extract. In our example, we start at the TranasactionSummary tag and then retrieve the Time, First, Last, High, Low, and Volume values. The $. in the With clauses tells OpenJSON to begin extracting at starting point noted in the second argument of the OpenJSON function, $.SummarizedTrades for our example. It works in a fashion similar to navigating the directories within the command prompt in DOS. Running this query, the OpenJSON function provides us with a nice tabular data set that can now be the basis for our report, as seen below.

query results

Now we can head over to Visual Studio 2015 with the SSDT-SQL Server Data Tools ( ) and begin to design our report.

Upon opening Visual Studio 2015, create a new SSRS project.

New SSRS Project

Next Add a new report.

New Report in SSRS

Our next step is to add a data source; since we are pulling this data through an OpenRowSet, we can use just about any data source.

Add Data Source in SSRS

Finally we add a new report data set (of course for both the data source and the data set you could use a shared source / data set if you desired - ).

Add data set in SSRS

For the dataset details, we add the previously shown query to the Query text area.

CreateDataset in SSRS

As shown below, we are now ready to create our report design. I added a simple table to the design grid, and then dragged over the time, first, last, high, low, and volume fields.

Report Design in SSDT

The final report shows the NASDAQ Trading Volume Summary, all based on JSON data.

Final Report in SSDT


Although direct support for JSON is not available in SSRS, we are able to circumvent this issue by using the OpenJSON functionality available in SQL 2016. Using this function as part of a query in SSRS gives us the ability to parse the JSON text into a tabular format which is then easily consumed by SSRS.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Scott Murray 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

Comments For This Article

Sunday, April 5, 2020 - 7:07:10 PM - divya Back To Top (85289)

Hi Scott,

I am also getting the same issue as below and i am trying to create SSRS report in ssrsdatatools 2017. will the openjson function will support in SSRS2017?


Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Friday, January 20, 2017 - 5:10:41 AM - Michel Manias Back To Top (45438)

Thanks a lot Scott, on a local drive it's working fine 

Thursday, January 19, 2017 - 9:32:50 AM - Scott Murray Back To Top (45420)

Michel... I tried your code on my local db and only changing the file locations to c:\users\scott.....

and it ran fine.


Maybe try a local file.  Also are you using SQL 2016?.

Thursday, January 19, 2017 - 8:28:32 AM - Michel Manias Back To Top (45419)

 Hi Scott,
Thanks for sharing this feature to use JSON with SQL2016. Unfortunately, I'm getting the following error message

Msg 319, Level 15, State 1, Line 13

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.











FROM OPENROWSET (BULK '\\XCHANGE\IS\Manias\GetSummarizedTrades.json', SINGLE_CLOB) as jsonfile

CROSS APPLY OPENJSON(BulkColumn,'$[0].SummarizedTrades')


   Time DATETIME  '$.Time',

   FIRST FLOAT '$.First',

   LAST FLOAT  '$.Last',

   HIGH FLOAT  '$.High',

   LOW FLOAT  '$.Low',

   Volume INT  '$.Volume',

   VWAP float '$.VWAP',

   TWAP float '$.TWAP',

   Trades int '$.Trades'




 Any Idea, what I should do to solve it



get free sql tips
agree to terms