SQL Server Reporting Services 2008 Palette Bug
I've spent the greater part of my morning investigating an issue with SQL Server Reporting Services 2008.
Specifically, I have created a report which uses colors as physical cues on a timeline. If criterion X is met then the cell is filled in green, if criterion Y then it is filled in blue. A combination of both X and Y will result in an orange field...quite simple....
I have similar reports written in SSRS 2005 that are in production and used on a day-to-day basis. Recently we have adopted SSRS 2008 and the first of its kind that I have published using the newer version.
Everything works as desired and I can export to Excel without issue. The report renders correctly and everything works as desired...until I attempt to copy the worksheet (right-click->move or copy) to another workbook. The worksheet is copied, but the colors change drastically so that the colors (white, orange, green and blue) are completely different in the newer version.
In my report I use the hexadecimal representation of standard excel colors (link) so there is no problem translating the colors themselves. The problem, it turns out, is that each report generated has a different color palette in excel.
Notice the differences between the following palettes:
The above palettes are taken from two excel spreadsheets generated with different criteria from the same report. Any attempt to combine these sheets into a single workbook fails as palettes overwrite the whole
For once there appears to be no easy solution. I rewrote the report in SSRS 2005 and, lo and behold, this issue does not occur. This issue has been reported on the Microsoft Connect site (link) and I can only hope there is a resolution in the near future.