XML Document Sourced SSRS Report Using HTTP URL

Introduction

In my Business Intelligence development experience, most of my SQL Server Reporting Services (SSRS) Reports have largely been based off relational data sources such Microsoft SQL Server and Oracle database. However, in some instances whereby an SSRS report is based off 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 Hypertext Transfer Protocol (HTTP) protocol-based URL.

The XML document that we will be using is that of the select SIFISO blogs which is 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:

Walkthrough

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 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 uses a sitemap to crawl and rank a given website – thus an updated and refreshed sitemap can increase the site 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 configure 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:

Define your series group as shown below:

Let’s define our measure as follows:

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:

Conclusion

That’s all folks.

I hope you were able to learn something from this post.

Until next time, cheers.CodeProject