Differences between SSRS 2008 R2 and 2012 for Excel render format
This blog shows how to conditionally hide, display or format parts of a report according to its render format (ie whether you're viewing it in Excel or not). The method used is slightly different between 2008 R2 and 2012.

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:

A report in SSRS

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:

The 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:

The rows to 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:

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:

Right mouse option for rows

Bizarrely, you can't change the property of multiple objects in SSRS by right-clicking.

 

You can now start creating an expression:

Expression for render format

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

This blog has 0 threads Add post