Problem
Can SQL Server Reporting Services (SSRS) use JSON as a data source for a report?
Solution
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: http://www.nasdaqdod.com/Samples.aspx. The below file shows trade prices and times.
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.
SELECT TRADE.Time, TRADE.FIRST, TRADE.LAST, TRADE.HIGH, TRADE.LOW, TRADE.VOLUME FROM OPENROWSET (BULK 'C:\Users\SCOTT\Downloads\GetSummarizedTrades.json', SINGLE_CLOB) as jsonfile CROSS APPLY OPENJSON(BulkColumn,'$[0].SummarizedTrades') WITH( 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.
Now we can head over to Visual Studio 2015 with the SSDT-SQL Server Data Tools ( https://msdn.microsoft.com/en-us/mt186501.aspx ) and begin to design our report.
Upon opening Visual Studio 2015, create a new SSRS project.
Next Add a new report.
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.
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 – https://www.mssqltips.com/tutorial/create-a-shared-data-source/ ).
For the dataset details, we add the previously shown query to the Query text area.
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.
The final report shows the NASDAQ Trading Volume Summary, all based on JSON data.
Conclusion
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
- Continue your learning with these tips:
