SSRS & Excel; Visualising Large Datasets

What does Microsoft Reporting Services give you, that Excel doesn’t? Excel offers accessibility of data, and is, in my experience, the most popular BI tool that I encounter. On the other hand, Reporting Services is less accessible for people who don’t have strong Excel skills, but offers you a great deal of fine-grained control over the reporting output.

Let’s take an example, created in SSRS, which shows a line graph with 500+ rows. In case you are interested, this line graph shows the results of an analysis of the breakdown of samples of olive oil, the thesis from the team at Iowa State University being that olive oil differs in its constituents depending on the geographical part of Italy where the olives come from. 


We can see here, from this sample of nearly 5000 rows, that the olives from the South Apulia area are higher in oleic acid; the rows are distinguished by their colour e.g. ‘South – South Apulia’ rows are marked by brown. SSRS was just made for dealing with large data sets, and turning them into visualisations.

Here is a subset of the total data, shown in Excel 2010, as follows:


Excel 2010, in its plainest “non-PowerPivot” form, struggled to show the 500+ different lines on the line graph. We know that PowerPivot is able to deal with millions of rows of data, but it wasn’t used here; instead, I stayed with the scenario of being a user, who only has Excel installed.

Thus, it was more difficult to get Excel to display hundreds and hundreds of lines of data as in a ‘stress test’ scenario. 

The graph looks different since it is simply a subset of the total 500+ rows of data in the original sample. 

We still get an idea of the overall pattern, but the detailed, clean look of the SSRS report is better for displaying the whole story, in addition to displaying all of the data. The finer control, afforded by SSRS, was a powerful tool which helped to produce a clean, visual report which displayed all of the data from a larger dataset.

But that’s ok: SSRS and PowerPivot, mobilised by SQL Server and Sharepoint, are there for the purposes of helping users to mobilise large datasets, and help users to make sense of their data. Excel, on its own, offers great accessibility; and who doesn’t love Excel?

Add to Technorati Favorites

Leave a Reply