Thursday, April 4, 2013
SSRS 2012 Excel Enhancement Details
In release of SQL Server Denali CTP3. There has been lots of interesting and new features to test out and this has become the proverbial carrot for me to get out of my sluggishness. This article is about one of those new features in SSRS Denali CTP3 – the feature upgrades in exporting a SSRS report to Excel.
Before I start listing down the new features, I would like to show you guys a screenshot of the new developer environment.
Yes, the old BIDS has given way to a new mean Visual Studio 2010 environment and I totally dig the blue background. Isn’t it cool?
Now before I get distracted with the other features, it is high time I say the upgrade is – SSRS renders a report to the native format of Microsoft Excel 2007-2010. The format is Office Open XML. The content type of files generated by this renderer is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and the file extension of files is .xlsx. The benefits of this over the previous .xls format (Excel 2003) are listed below:-
1) Max columns per worksheet increased from 256 to 16,384
If you are exporting a report with more than 256 columns in a worksheet to the normal .xls format in Denali or in SSRS versions prior to Denali, you will get the following error message
You will no longer see this error message if you are exporting to the .xlsx format.
2) Max rows per worksheet increased from 65,536 to 1,048,576
Similarly, if you are trying to export a report with more than 65,536 rows in a worksheet to the normal .xls format, you would be greeted with the following error message.
However, this will no longer be an issue when you export to the new .xlsx format.
3) No of colours allowed in a worksheet increased from 56 to approx. 16 million
I am sure this is a very welcome move for many. Prior to Denali, you couldn’t get more than 56 colours in a worksheet but with the new .xlsx format, this is very much possible. For eg, consider the following colour matrix that I made in BIDS.
Now when I export it to .xls (Excel 2003) format, I get the following output in excel
We can see that the entire last row is blacked out due to the colour limit. However, if we export it to the .xlsx format, we get the proper output.
Even though the excel export worked as expected in the case of pre-defined background colours for each cell, it did not show up the correct results when I used custom code to show the background colours for the members in a group.
For eg, the following output came up properly in .xls format
But when the same report was exported to .xlsx format, it started showing the last colour (which is Green) for every cell.
This has been raised as a bug in Connect. Vote for it if you want it to be rectified fast.
4) ZIP compression
The .xlsx format has ZIP compression, hence the size of the files generated would be lesser. For eg, I generated a report with 250 rows and 250 columns in both .xlsx and .xls formats. The .xlsx format was just 25KB while the .xls format was 35KB in size. This difference may become more evident and important in the case of large reports.
That’s all from me this time folks. Hopefully, I will be back with some more of the new features.