Reporting Services Row Background Color

At times we will need to vary the colors of our rows based on the data or readability. This can be accomplished by editing the row background color property. Below I’ve created a report that will highlight any rows that have a ship country of France or a contact name of Yang Wang.

To accomplish this simply select the detail row in the layout view.

In the properties window select “Expression” from the dropdown listed next to BackgroundColor.

An expression window will open. Put your IIF statement here.

You can also use the FontStyle row property to change a row style to italics.

Using SET FMTONLY OFF in the Query string of Report Server

By default FMTONLY is set to ON in Report Server. When using a SQL statement containing temp tables an error message commonly occurs. You may get an message like "There is an error in the query. Invalid object name '#myTempTable'".



To fix this I’ve used the command SET FMTONLY OFF at the top of my SQL statement. This allows the SQL statement to return rows with data. The SET FMTONLY ON will only return column information.





Use the following SQL statement, changing the FMTONLY from ON to OFF, in Query Analyzer to see how this works.

USE AdventureWorks;
GO
SET FMTONLY ON;
GO
SELECT *
FROM HumanResources.Employee;
GO
SET FMTONLY OFF;
GO