BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 17 June 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using RenderFormat in SSRS 2008 R2 versus 2012
I got caught out by this teaching our Advanced SSRS course today, so thought I'd write a quick blog about it.
The problem: hiding a report's title in Excel
Suppose that you have a report like this:
The report includes two title lines, which you don't want to show in Excel when it is exported.
When you export this to Excel, you don't want to see the title, so that you can treat the report as an Excel table:
What you want to be exported to Excel.
You can achieve this by setting the Hidden property of these two rows to be an expression which depends on the render format (ie whether you're viewing the data in Excel, Word, PDF or on-screen, to name a few possibilities). However, the syntax is different for SSRS 2008 and SSRS 2012.
Note that even with the rows hidden you still get two blank rows appearing at the top of the Excel spreadsheet. Not everything in life is perfect!
The solution: hide the rows when rendering to Excel
Here's how to solve the problem above. First, select the rows you want to conditionally hide:
Select the title rows you want to hide if you're viewing the report in Excel.
Now go to the Properties window and set an expression for the Hidden property:
Choose to set an expression for this property of the selected title rows.
It's worth mentioning that you can't do this with the right mouse button:
Bizarrely, you can't change the property of multiple objects in SSRS by right-clicking.
You can now start creating an expression:
You can insert the render format name from the Built-in Fields collection. Here we're testing whether the report is being viewed in Excel.
Now to the point of this blog. What you type in next depends on which version of SSRS you're using. Here are the possibilities:
| Version of SSRS | Render format name to use |
|---|---|
| 2008 R2 | EXCEL |
| 2012 | EXCELOPENXML |
So here's what the expression will show in SSRS 2012, for example:
=IIF(Globals!RenderFormat.Name="EXCELOPENXML",True,False)
Other differences between Excel rendering in 2008 R2 and 2012
In SSRS 2012 the Excel 2003 file format is deprecated, and reports to Excel always use Excel 2007 format instead. This gives rise to the following changes:
| Feature | SSRS 2008 R2 | SSRS 2012 |
|---|---|---|
| Font typeface | Arial | Calibri |
| Font size | 10pt | 11pt |
| Default row height | 12.75pt | 15pt |
There are lots of other differences - for the full story, see this Microsoft web page (or have a look at our SSRS training courses or Excel courses).
Great blog. However, I think the following area is inaccurate (at least using BIDS for SSRS 2008 R2).
"Note that even with the rows hidden you still get two blank rows appearing at the top of the Excel spreadsheet. Not everything in life is perfect!"
Actually, the rows will not appear on the Excel spreadsheet if you set the Hidden property by right-clicking the row and selecting Row Visibility. The method you described in the blog sets the Hidden property of the individual textboxes.
"Bizarrely, you can't change the property of multiple objects in SSRS by right-clicking."
Yep. What a pain! However, right-clicking and setting visibility individually is your only option if you want a row, column, or group to completely go away. Also of note: if you hide a row with an expression, when you select the entire row, the Hidden property will still appear to be False in the properties window. This is because the properties window is showing all of the common properties of the selected textboxes. Thus, you can't tell which rows, columns, or groups have visibility expressions at a glance.