Row visibility expression using ReportItem collection not working

Question

This is major edit from the previous version of this question, which would hold little of value for future readers anyway.

I have a report where I want to hide certain rows depending on the data in certain fields, but also be able to override this based on a parameter value.

So for my visibility (Hidden), I am using the following expression:

=iif(CDbl(ReportItems!tbOEM1.Value) >= 20 Or CDbl(ReportItems!tbOEM2.Value) >= 20 Or CDbl(ReportItems!tbOEM3.Value) >= 20 Or Parameters!OEM20.Value.Equals(False)
,    False
,   True
)

Which I think should mean, if any of the 3 tbOEM fields are >= 20, then display the row. But if the user has selected False for the "OEM20" parameter (Boolean), display all the rows regardless of the values.

However, when I run the report and choose True for the OEM20 parameter, No rows get displayed, even though I know that there are rows that have OEM values over 20.

To investigate, I added a background color expression to each of the tbOEM reportitems. Here is the background color expression for tbOEM1:

=iif(CDbl(ReportItems!tbOEM1.Value) >= 20
,   "Red"
,   "White"
)

When I run the report with False for the OEM20 parameter, I see all rows returned, and no fields are colored Red, even the ones that should be because they are tbOEM fields with a value >=20.

So I wonder if this is an order of execution issue where the value of the ReportItem is not yet known when setting visibility and background color? Is this known and documented anywhere? I googled and couldn't find anything.

Or am I doing something else wrong that is fixable?

I already know that a workaround is to use the datafield calculations that populate the tbOEM fields in the expressions, rather than the ReportItems collection. So you needn't bother to tell me about this option.

Answer 1

Here's some things to try. Putting this as an answer as it will be too big for a comment.

I'm still thinking something weird is happening with type comparison/conversion.

Maybe the tbOEM textboxes are strings with non-numeric characters (even just a space) so the CDbl conversion is failing, and thus failing the CDbl(ReportItems!tbOEM1.Value) >= 20 test even though it looks like it is above 20 when displayed as a string. Given your background colour expression is also failing, this seems to be the case. Let's use the Val expression for the comparison instead:

Val(ReportItems!tbOEM1.Value) >= 20

Val doesn't give errors when converting to a numeric value, it just does the best it can and returns whatever number it finds. Convert the field into the numeric value for display purposes as well as for the background colour expression so we see what the expression sees, not just the displayed text. On your tbOEM1 textbox use these expressions for the following properties:

Value

=Val(Fields!OEM1.Value)

Background.Color

=iif(Val(ReportItems!tbOEM1.Value) >= 20, "Red", "White")

If this still fails (for example, the textbox no longer displays a value) then let's trim it first to get rid of any spaces: =Val(Trim(Fields!OEM1.Value))

If the OEM1 field is a decimal value use 20.0 rather than 20 so it is comparing to a Double rather than an Integer in case something funky is happening with the implicit type conversion in the comparison.

Okay, now hopefully the textboxes are displaying the correct number and the background is being coloured correctly. Now we just need to fix the Visibility.Hidden expression for the row. I wouldn't use Value.Equals here as the parameter is already a boolean so we can just use it directly:

=iif(Val(ReportItems!tbOEM1.Value) >= 20 Or Val(ReportItems!tbOEM2.Value) >= 20 Or Val(ReportItems!tbOEM3.Value) >= 20 Or Not Parameters!OEM20.Value
    , False
    , True
)

This needs to be applied to the entire row by selecting the handle for the row on the left of the tablix, rather than applied to the textboxes directly.

Of course, add the Trim function if the experiment above required it.

Good luck! I hope this helps.