Tuesday, October 09, 2012 7:42 AM
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.
- Edited by ZZ02 Tuesday, October 09, 2012 7:43 AM
Tuesday, October 09, 2012 8:05 AM
Is the EnableFormatConditionsCalculation set to True in your excel file?
Thursday, October 11, 2012 6:35 AMModerator
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:
TechNet Community Support
- Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Monday, October 22, 2012 5:25 PM
Wednesday, October 31, 2012 7:56 PM
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à !
PS: You can check the state before and after the change using this cmdlet :
Get-SPRSExtension -Identity $ssrs.id -ExtensionType Render -Name EXCEL