SSRS Tip: Hide a tablix if no rows returned

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

8 thoughts on “SSRS Tip: Hide a tablix if no rows returned”

  1. Jen, another thing you might try is using the No Rows property of the table. You can simply put in some text like “No Rows Found” and if the data set does not return any data this message will display. I suppose there are reasons for both uses but I find this is one I turn to quite often.

  2. Hi, We are facing a problem using hidden expression for a tablix control with Fileds!EmpID = 1 having no rows returns from the dataset, its throwing an error in this case saying expression evaluates to null. Please help us in fixing this issue.

    NOTE : Only problem comes in SSRS 2008.

  3. Nice article. I learn something more challenging on various blogs everyday. It is going to always be stimulating to see content from different writers and practice a little something from their shop. I?d prefer to utilize some together with the content on my blog whether you re mind. Natually I?ll supply you with a link on your web website. Thanks for sharing. {

Leave a Reply

Your email address will not be published. Required fields are marked *