Export to Excel upgrades in SSRS Denali CTP3

I don’t know whether it is the same with you guys but for me, it is a bit hard to get on track and start work after a long vacation. Usually, the initial few days are spent idle before the computer and if I don’t make an effort to control it, my moods become crankier than the schoolboy who doesn’t want to go to school. However this time has been a bit easier for two reasons: (a) My wife who is a constant source of encouragement (benefits of being newly wed! ) and (b) 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. BUG ALERT 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. https://connect.microsoft.com/SQLServer/feedback/details/683691/ssrs-denali-export-to-excel-colors-not-being-displayed-correctly 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.