SSRS 2012 Conditional Formatting Color Issues When Exporting to Excel

Question

Hi all,

We recently upgraded to SQL 2012 from SQL2008 R2. I'm having a strange issue with SSRS2012.

One of the report has conditional formattings on cell background colors. Everything works fine when render the report in browser or preview mode. However, the conditional formatting stop working when export the report to Excel (no issues if export to pdf). All cells are high-lightened with pre-defined color although it should only high-lighten those cells that fulfil the condition.

Has someone encountered this issue before ? Thanks for any help.

Answer 1

Is the EnableFormatConditionsCalculation set to True in your excel file?

Arjan

Answer 2

Hi ZZ02,

I have test the scenario in my testing environment, however, everything goes well when exporting to Excel format. The issue might be related to the expression for the conditional background color. To make further analysis, please post the expression as well as the report design structure.

Additionally, in SQL Server 2012 Reporting Services, the Excel rendering extension renders a report to the native format of Microsoft Excel 2007-2010, the format of which is ExcelOpenXML. By default, the previous version of the Excel rendering extension, compatible with Microsoft Excel 2003, is disabled. At this time, I suggest that you enable the Excel 2003 rendering extension by modifying the RSReportServer.config file. After that, the Excel 2003 rendering format will be available on report manager (not available in Report Designer). So, please export the report from report manager and check the result again.

For more information about enable the Excel 2003 rendering extension, please see:
http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/0713de27-dcc0-4e51-81ac-5272647d171f

Regards,
Mike Yin

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here. 

---

Mike Yin

TechNet Community Support

Answer 3

Hi,

We had the exact same issue and it's actually quite easy to fix it. Like Mike explained, this is by design with Report Server 2012 SharePoint Integrated mode.

The thing is, modifying the "RSReportServer.config" file won't fix the issue at all since it is now completely integrated as a service application in SPS2010.

In order to make visible the "Excel 2003" renderer, you need to use PowerShell cmdlets :

$ssrs = Get-SPRSServiceApplication
Set-SPRSExtension -Identity $ssrs.id -Name EXCEL -ExtensionAttributes "<Visible>true</Visible>"

And voilà !

Regards,
Wes

PS: You can check the state before and after the change using this cmdlet :

Get-SPRSExtension -Identity $ssrs.id -ExtensionType Render -Name EXCEL