Home » sqlserverpedia-syndication »SSC »SSRS »Tips » Currently Reading:

SSRS Tip: Hide a tablix if no rows returned

January 19, 2012 sqlserverpedia-syndication, SSC, SSRS, Tips 6 Comments

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

Currently there are "6 comments" on this Article:

  1. Keith Mescha says:

    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. Thanks Jen, good stuff.

    Thomas

  3. E says:

    You could also use the following:

    =CountRows() = 0

  4. Natalia says:

    (CountRows() = 0) works best for filtered data sets.
    Many thanks!

  5. Divya says:

    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.

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

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/