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


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


    Thursday, March 28, 2013 3:02 PM


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.

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


    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


    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:-   (Service Pack 1) (SQLServer2012SP1-KB2674319-x64-ENU.exe   or    SQLServer2012SP1-KB2674319-x86-ENU.exe based on the SSRS bitness level)   (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
  • For people who can't do the update (no server downtime, don't have access/rights etc etc) this is a problem related to leaving the default cell color as 'No Color' or 'Transparent'. 

    If you set the default cell color to an actual color, even simply white, this bug will not occur. 

    I found this because I created a striped report, and the whole column wasn't highlighted - only if the next row was the default color (white because of the background, but 'Transparent' in the cell). In testing I removed the striping, which produced the bug as shown above, until I set the 'fail' color to white in the conditionally highlighted columns - this exported properly. 

    I'm guessing the bug is something like the excel renderer simply using the default color for cells set to 'Transparent', but when it comes across a fill condition that is met it sets the default color for that column to whatever color the condition says, and never sets it back.  

    Thursday, October 02, 2014 11:25 PM
  • A very big thank to Ajaxender. His solution works !
    Wednesday, April 22, 2015 12:37 PM
  • Hello Friends,

    I am facing the same issue in my report which is on 2012 SQL server when I am trying to render it to 2013 excel. Unexpected colors are coming in the excel for empty cells.

    My report is a matrix, so number of columns will change based on my data and i am getting different colors for empty columns. I have installed the suggested patch also but i am still getting the same issue. and also I have added White instead of No color, still the same issue.

    Any more suggestions on this please?? 

    Thanks & Regards,


    Tuesday, September 08, 2015 10:05 AM