In my Business Intelligence development experience, most of my SQL Server Reporting Services (SSRS) Reports have largely been based on relational data sources such as Microsoft SQL Server and Oracle database. However, in some instances whereby an SSRS report is based on an Operational Data Source (ODS) or Online transaction processing (OLTP) system, Extensible Markup Language (XML) could be a designated data source. There are numerous ways of source XML as a data source which includes embedded XML data, and Web service endpoints that return XML data. However, in this post, I will take you through an example of sourcing an XML document via a Hypertext Transfer Protocol (HTTP) protocol-based URL.
The XML document that we will be using is that of the select SIFISO blogs which are located here.
The aforementioned document looks as shown below:
Source Code Changesets
The changesets associated with the source code for this article can be found on GitHub and CodePlex as shown below, respectively:
Okay, let’s get started.
Create a new SSRS report and configure your (shared) report data source as follows:
You don’t have to edit the credentials tab.
Let’s move on to the shared data set. I’ve decided to retrieve all top levels of data from our data source hence the shared data set query is defined as shown below:
Whilst on the shared data set – click on the fields tab such that the below is shown:
That concludes our configuration of the XML data set.
Now we move on to visualizing the XML data and for that, I decided to use an SSRS chart control. We understand that search engines use a sitemap to crawl and rank a given website – thus an updated and refreshed sitemap can increase the site’s ranking on search engines. Therefore, a possible business case from our XML data could be: “how often is the sitemap refreshed?”.
We go on solving this by adding a new report item and configuring the report data to look as follows:
Add a chart control into the body of your report and configure the chart properties as follows:
I have chosen a Bar chart type but feel free to choose a different chart type else:
The design view of your report should look as follows (provided you used the same chart type as I did):
The preview/run time view should be able to address the business case and thus look as follows:
That’s all folks.
I hope you were able to learn something from this post.
Until next time, cheers.