Changing color of body report, depending on value in table

Question

I have report in SQL Server 2008.

Report has a parameter, parameter values:

organization1
organization2
organization3
organization4

To display a list of parameter with values, I used "Available Values" "Get values from a query".

If value of organization1 parameter in "Value" column is greater than 20, then report body and background of tables should be in red color.
If selected parameter organization1 background of tables and report body should be in red color.
If selected parameter organization2 background of tables and report body should not be in red color.
If selected parameter organization3 background of tables and report body should be in red color.
If selected parameter organization4 background of tables and report body should not be in red color.

The expression below does not work for selected parameter.

=IIF(Fields!AVID.Value, "summ_work">20 and
First(Parameters!ReportParameter1.Value, "test")="Organization1","Red","Transparent")

How can I fix it?

score 0 · Answer 1 · answered Jun 04 '17 at 17:13

You're going to need something like this for the BackGround expression in the report.

=Switch(Sum(Fields!value.Value, "DataSet1") > 20 And Parameters!Organization.Value = "Organization1", "Red",
    Parameters!Organization.Value = "Organization1", "Red",
    Parameters!Organization.Value = "Organization2", "White",
    Parameters!Organization.Value = "Organization3", "Red",
    Parameters!Organization.Value = "Organization4", "White",
    True, "Yellow")

That last line of the Switch is a catch all that will turn the background Yellow. Do what you want with that, or remove it entirely if you don't think the situation will happen where someone passes a parameter value other than what you have in the list.

I went with White as the "should not be in red" color, change that to something else if you like.

Changing color of body report, depending on value in table

1 Answers1