none
SSRS 2012 background color format by expression issue when exporting to Excel.

    Question

  • We are using SSRS 2012. We have a report that conditionally formats a background color for some cells. The report renders properly in a browser and in Excel 2003 format. In Excel format all cells after the first one that meets the condition are highlighted, even if only one cell should.

    The sample expression that triggers this condition looks like this:

    =IIF(Fields!VIOL_NOTE.Value="Internal","Green","No Color")

    All cells after the first one that meets the condition Fields!VIOL_NOTE.Value="Internal" have a green background.

    Excel 2003 (proper) results:

    Excel (improper) results:

    Zenon



    Thursday, March 28, 2013 3:02 PM

Answers

All replies

  • Hi Zenon,

    Based on your description, I try to reproduce the issue in my test environment, however it is work well. After export the report to Excel and Excel 2003, I can get exact same result when open the Excel file in Excel 2010.

    Did you open the repot in Excel 2003 or Excel 2007-2010? Please post more deatils, so we can help to work out this issue.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Friday, March 29, 2013 3:24 AM
    Moderator
  • Fanny,

    I have posted two pictures in my initial post. The first one is a result of rendering my report in Excel 2003 (this is the rendering mode provided by SSRS2012, resulting in generation of .xls file) . The results look good when opening with Microsoft Excel 2007 (a client application). The second picture represents the rendering in Excel (the rendering mode provided by SSRS 2012, resulting in generation of xlsx file). The results look incorrect when opening in Excel 2007 (a client application). After the first cell that is highlighted because it meets a condition - all other cells are highlighted regardless of the condition met.

    Thank you for help,

    Zenon

    Monday, April 01, 2013 1:16 PM
  • Workaround found:

    According to Microsoft support, the problem comes with the usage of the "No Color" phrase within a condition statement. If it is changed with any other color, that is considered valid, then the Excel rendering works.

    Instead of

    =IIF(Fields!VIOL_NOTE.Value="Internal","Green","No Color")

    The statement

    =IIF(Fields!VIOL_NOTE.Value="Internal","Green","White")

    should be used. This is a bit strange, since the default color of a cell in Report designer is "No Color", but the results are acceptable. Microsoft promised to look at it further.

    • Marked as answer by Zenon Staniek Thursday, April 04, 2013 2:04 PM
    • Unmarked as answer by Zenon Staniek Monday, April 08, 2013 1:52 PM
    Thursday, April 04, 2013 2:04 PM
  • I got the following response from Microsoft:

    I did further research on the issue we were working on and this seems to be an anomaly in the product which seems to be fixed in SQL server 2012 Service Pack1 CU3.

    Following is the link to download the service pack and cumulative update:-

    http://www.microsoft.com/en-us/download/details.aspx?id=35575   (Service Pack 1) (SQLServer2012SP1-KB2674319-x64-ENU.exe   or    SQLServer2012SP1-KB2674319-x86-ENU.exe based on the SSRS bitness level)

    http://support.microsoft.com/kb/2812412   (Cumulative Update 3 for Service pack 1)


    Monday, April 08, 2013 1:54 PM
  • This solution solved the problem. Thank you!
    Friday, July 26, 2013 6:25 AM
  • Same issue. Thanks you for the solution
    Thursday, August 29, 2013 1:55 PM