none
SSRS 2012 Conditional Formatting Color Issues When Exporting to Excel RRS feed

  • 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.


    • Edited by ZZ02 Tuesday, October 9, 2012 7:43 AM
    Tuesday, October 9, 2012 7:42 AM

Answers

  • 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

    Thursday, October 11, 2012 6:35 AM
    Moderator

All replies

  • Is the EnableFormatConditionsCalculation set to True in your excel file?

    Arjan

    Tuesday, October 9, 2012 8:05 AM
  • 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

    Thursday, October 11, 2012 6:35 AM
    Moderator
  • 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

    Wednesday, October 31, 2012 7:56 PM
  • I see this post is a bit old, but I was not satisfied with the answers and figured out how to fix this easily on my own.  Thought I would share.  In SQL 2008 and older, you could set your condition so that if it wasn't met the background was "Transparent", which result in white cells.  In 2012 I found that you need to set the cell background color to "White" or the previous cell containing an actual color will continue on until the next different colored cell. 

    This is an obvious bug in the export process, whereas it builds the XML incorrectly, that can be easily worked around by defining a default color rather than transparent.

    • Proposed as answer by Larnu Tuesday, April 22, 2014 4:12 PM
    Thursday, February 6, 2014 9:12 PM
  • Kev1969, THANK YOU! Your solution solved my problem which started after migrating from 2005 to 2012. 
    • Edited by KScottPDX Monday, June 16, 2014 10:00 PM
    Monday, June 16, 2014 9:59 PM
  • Kev1969 thanks guy! Instead of 'white' I was using 'nothing'. Your solution works
    Wednesday, July 16, 2014 7:35 AM
  • I have this expression in the BackgroundColor property - however we are still experiencing the issue.  Anyone have any other ideas on a work around?

     =Switch(Parameters!P_HideColorKey.Value = "True", "White", Fields!IsCustomer2.Value = "Yes", "#c7e794", Fields!IsCustomer3.Value = "Yes", "LightBlue")

    Wednesday, August 20, 2014 3:41 PM