Hi All,
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.
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.
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.
1 comment:
What about rendering performance as compared to the old XLS renderer?
Post a Comment