Skip to content

Month: November 2011

Reporting Services Reports With Sparklines Running Very Slow

I recently encountered a fairly significant performance issue with SQL Server Reporting Services 2008 R2 and Reporting Services Azure. After having built out an electoral report that broke down election results poll by poll, I used what I think is a fantastic new feature in Reporting Services 2008 R2, sparklines.

A sparkline is essentially a mini-graph – a visual representation of a single row of data. In my case, it is the results, by party (as indicated by colour) for a given poll. It looks like this:


Once I got this report looking the way that I wanted it to, I deployed it to Azure Reporting Services. However, when I ran the report, it took an incredibly long time to load (3 minutes and 30 seconds). I immediately blamed Azure RS for this, as it’s still a preview edition. However, further testing revealed the same behaviour on an on-premise deployment. The puzzling thing was that it rendered very quickly using the preview in BIDS or in Report Builder.

Adding to the mystery is the fact that the report renders fairly quickly when called from the Reporting Service web service to create a PDF file, or even an HTML file. I managed to discover this when I decided to pre-create a number of these reports in PDF format to reducre the load time for users (more on how I did this in an upcoming post).

The oddest part is precisely where the performance problem shows up. When the report is run using a browser, the browser thread’s CPU utilization goes up to 100% of available resources (a 2 core machine CPU runs up to 50%). This is happening on the client side.

On a hunch, I tried removing the sparklines. Presto, the load time dropped to 20 seconds. So I don’t get to use my sparklines in the live report which is unfortunate, but at least I found the culprit. What I wish I knew was why this was happening, or if there’s something I’m simply doing wrong.

If anyone has any ideas, I’m all ears.


File Formats for the SQL Server 2012 (Denali) Reporting Services Web Services Renderer

One of the major new features in SQL Server Reporting Services (SSRS) 2012 is the ability to render Excel files and Word files in the XML based 2007 file format (.xlsx and .docx). Previous versions of SSRS rendered Excel and Word files into the old 2003 formats (.xls and .doc) Creating one of these output files can be done through the reporting UI by using the Export button. The new formats are used automatically, and you need to perform server configuration in order to use the older formats. 

However, if you are using the Reporting Services web service, nothing changes. The Render method of the ReportExecution2005 web service would appear something like below:

report = rs.Render("EXCEL", deviceinfo, _
extension, mimeType, encoding, warnings, streamIDs)

The first argument is the file format to render into. With SSRS, the above code will render into the 2003 file format (.xls). The web service defaults to the old format.

I was unable to find any documentation at all on the correct argument to get the new file formats, so I poked around a little with my debugger and the ListRenderingExtensions method. As a result, I thought I’d share what I think is the comprehensive list of rendering formats for Reporting Services in SQL Server 2012. These are taken straight from the code.




XML file with report data


Null Renderer


CSV (comma delimited)


Data Feed




Remote GDI+ file


HTML 4.0


MHTML (web archive)


Excel 2003




RPL Renderer


TIFF file


Word 2003




Therefore, if I want to render to the description “Excel” the argument needs to be “EXCELOPENXML”, and to render to the description “Excel 2003” the argument is “EXCEL”. That’s not at all confusing.

In any event, hopefully this helps someone (likely me in the future..)