Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2017-01-19   |   Comments (3)   |   Related: > Reporting Services Data Sources

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.

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.

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.

query results

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.

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 - https://www.mssqltips.com/sqlservertutorial/233/create-a-shared-data-source/ ).

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

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


Last Updated: 2017-01-19


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

 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.

SELECT

TRADE.Time,

TRADE.FIRST,

TRADE.LAST,

TRADE.HIGH,

TRADE.LOW,

TRADE.Volume,

TRADE.VWAP,

TRADE.TWAP ,

TRADE.Trades

FROM OPENROWSET (BULK '\\XCHANGE\IS\Manias\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',

   VWAP float '$.VWAP',

   TWAP float '$.TWAP',

   Trades int '$.Trades'

   ) AS TRADE

 

GO

 Any Idea, what I should do to solve it

Regards

Michel


Learn more about SQL Server tools