I was helping Bob Dorr out with a report he was creating. He was trying to get some images and a column to hide under certain conditions. He added the expression based on data using the Iif() function. His complaint was that the column and/or image was still showing and wasn’t hidden.
For my example, I’ll use the ExecutionLog and the Format value to display an image (Excel) if the format is EXCEL. The expression in question that we were using was something similar to the following.
=iif("EXCEL" = Fields!Format.Value, true, false)
This resulted in no image showing.
What happened is that without looking, we went on the premise that the expression was for the Show question and not the Hide. Meaning we formatted the expression thinking that the resulting true or false was to answer the question “Do we show it?”. It’s actually the opposite. It answers the questions “Do we hide it?”.
We have actually seen this crop up from customers quite a bit. When he asked me what was going on, the thought that popped in my head was “think negative” that reminded me that I should look at it based on hidden rather than show.
So, if we change the expression to the following
=iif("EXCEL" = Fields!Format.Value, false, true)
we see the following outcome
Writing up this blog post, I actual spotted something I hadn’t seen before. The dialog box actually states to equate for Hidden rather than Show.
That’s honestly the first time I actually saw that. Normally I just blew right past it, which I think most people do based on the cases that I’ve had relating to this.
The moral of the story is that when writing an expression for Visibility, make sure to keep in mind that it should equate to “Do we hide it?”.
Adam W. Saxton | Microsoft SQL Server Escalation Services