SSRS: Report Builder: Conditional Formatting in a calculated Text Box

Question

I know you can apply conditional formatting to a field in report builder: http://technet.microsoft.com/en-us/library/dd220466%28v=sql.100%29.aspx

The example on the page above gives this example setting a expression in the property box Color:

=IIF(Fields!Profit.Value < 0, "Red", "Black")

That is a good example if you know the name of the field. However I would like to apply conditional formatting within a field that is derived and I am not sure what the name of the field is.

For example in my SSRS report I have a matrix with two text boxes that supply two numbers. I then put a calculation into the third text box which references the first two:

Within Report Builder my calculation just appears as expr or expression. How do I reference the calculated text box within the IF statement for conditional formatting?

I have tried using things like calculated fields instead of a text box but the problem is that the Matrix wants to sum the percentage and so a calculated text box works better than a calculated field.

Answer 1

Depending on scope you can reference the textbox by using ReportItems, so if the textbox that contains your calculation had a name of "Percentage" then you could reference it in another expression as follows:

=ReportItems!Percentage.Value

If you are wanting to do conditional formatting on the textbox that contains the calculation you can also use Me.Value, though I have had odd results with this sometimes.

Additionally you could repeat the expression you are using in your calculation to provide conditional formatting, for example if you wanted to change the text color for the textbox that contains the percentage you could use the expression:

=IIF((Fields.Numerator.Value / Fields.Denominator.Value) > 0.5, "Green", "Red")

Answer 2

If you are inside a group you maintain the scope.

You have two options... You can just use the same calculations from the other field in the expression for the color (like previous answers).

The best solution in this case is to try to make the calculation on SQL. I recommend this due to performance issues. In general que calculation will be much faster using the SQL engine due to parsing.

Try to keep all calculation in SQL.

Answer 3

I think first you have to write your third textbox expression in dataSet Add "Calculated Field"

then use those calculated field within IF Condition.