I have a tablix in an SSRS report, populated by a query that might or might not return any rows.  What I want to do is make that tablix invisible if there are no rows returned.

First off, to set conditional visibility. 

1. Right-click the tablix and select Properties. 
2. Click Visibility.
3. Select Show or hide based on an expression.
4. Click the Function button.

The formula you place in here should evaluate to TRUE (meaning, hide the tablix) or FALSE.  Examples of True/False expressions here include: 

= 1=1 ‘(true)

= 2=1 ‘(false)

 =Globals!ExecutionTime > “1/1/1998″ ‘(true)

But we want something a little more specialized – something that checks if any rows were returned. But there’s no ReturnedRows function in VB, so we have to attach this another way.  The question you want to ask is, does the first row from this data set actually have data?

Start with the first row part:

1. Under Category, click DataSets.
2. Under Item, click the data set you want.
3. Under Values, click a field that really shouldn’t be NULL (if the data set DID return data).

For example, I use First(ID). This gives you the formula =First(Fields!ID.Value, “DataSet1″), which clearly doesn’t evaluate to True or False. All you have to do now is slap the IsNothing function around it, and you’re golden!

=IsNothing(First(Fields!ID.Value, “DataSet1″))

If you like alternative wording in code, here’s another way to say the same thing:

=IIF(First(Fields!ID.Value, “DataSet1″) Is Nothing, True, False) 

This performs the exact same test; it’s just more spelled out via the IIf and explicit True and False return values. This wording gives you a bit more flexibility for other uses; for the specific problem at hand, I’ll personally use the tighter IsNothing formula.

Wait a minute…

Why couldn’t we have simply used Fields!ID.Value, instead of First(Fields!ID.Value, “DataSet1″)?  Well, IsNothing won’t work against an expected rowset, even if the rowset returned no rows; that’s a different kind of object. But it will work against a single value.

For more on SSRS expressions, see MSDN’s Expression Examples (Report Builder 3.0 and SSRS) article.

Happy days,

Jen McCown
MidnightDBA.com/Jen