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
MidnightDBA.com/Jen

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

  1. Keith Mescha

    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. Divya

    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. celine small boston bag

    I’ve recently started a site, the info you provide on this site has helped me tremendously. Thank you for all of your time & work. “Marriage love, honor, and negotiate.” by Joe Moore.

Comments are closed.