The XML data source in SSRS can be very powerful. Not only for querying web services but also for showing feed data on our reports. In this post I will show you how we can show the contents of my favourite blog site SQLBlog.com in an SSRS report.
First we need to get the feed URL. In this case it is: http://sqlblog.com/blogs/MainFeed.aspx. Once we have this, we can use it in a new XML data source in SSRS just as it is:
Then, we can use it in a data set in our report. The little tricky part here is that SSRS expects us to send a query to the data source. We do not need to do this for an RSS feed and instead we can leave the query blank.
However, this results in an empty fields list and hitting Refresh Fields does not help. To overcome this problem we need to know what the fields are at the first place. We can open Query Designer and click on the Execute button (!). The result is a number of rows corresponding to the feed items and columns containing various types of information about the items.
In the SQLBlog case, we get one line for each category the author has placed the feed in, while in other cases we may get one line per feed item or another variation. It is always necessary to inspect the contents and ensure we understand what we get from the data source in order to deal with the feed effectively later on because the feed contents vary. Many columns are irrelevant and we need to select only the ones we need. From the sample feed I wanted to show just the title, link, pubDate, creator and the description on my report. Since the field list was blank I added these fields manually as Query Fields.
Then, I placed these fields in a table to check the result.
I could see all items as expected, but the formatting was off. Additionally, there were multiple items repeating because of the multiple categories for each article. With a bit of work I managed to group/format/lay out the items appropriately, add report actions for the links and generally transform the table in a nice report, which I could use as my default feed reader.
And the rendered report:
By the way, the second item is very interesting – Alberto Ferrari has been just awarded the Microsoft MVP award for 2011!
One problem that you may find is the default authentication SSRS uses for an XML data source – Do not use credentials. This will cause a problem once the report is deployed, so I would recommend this should be changed to something else (in example, Windows Authentication).
There we go – we have a fully functional blog reader. We can enhance this by providing a list of authors (like a table of contents with linked reports), report parameters filtering the table, and other nice to have things. Of course, there are many feed readers out there any you may wonder why you would build your own in SSRS. Well, think about not only RSS but also other content which comes through XML – in example web service content which we can query through SOAP, twitter feeds, etc. Additionally, incorporating such content within a SSRS dashboard or report could come in handy if we get our hands on a feed showing financial information. One other source for content could be Microsoft’s Azure Data Marketplace – after all the data there comes in feeds and it could be handy to show it directly on our reports with a little bit of formatting.
Download the sample report here: