Update August 2011
So I don't know how many people were just saying why don't you do the following:

  1. Bring up the "Tablix Properties" of the dataset
  2. Look for the section "No Rows"
  3. Put in a value for "NoRowsMessage".
This solution hides the entire Tablix which kinda defeats the purpose of having anything there. I want my end-users to have faith in my report. The report just returning blank can also mean "I don't know". I want the report to either return the correct value or say "No data found". My end-users would otherwise ask is the data value reliable if it's blank?

The long way of doing this
I would have called this article "Iteration within SQL Server Reporting Services Business Intelligence Development Studio (BIDS) version 2008 through combining Transact-SQL and MDX expressions" but boy what a mouthful, and it's not really iterating anymore. So it's "SSRS Hide results table if empty" though I will add that if you wanted to put a message instead of hiding the table then following the below will also let you do this (requirements: common sense or the IQ of a duck).

It's what I've been searching for for the past hour and although you may think I'm just adding to the cyberspace pile of useless info, at least I'm not just copying and pasting from other sites to add content to my own. And I'm not just adding content, the way I'm doing the below is nothing similar to what I googled (probably a bad sign but time is ticking and no one has forever).

I have a report which I've already completed and pretty chuffed with. It shows the details on a user-specified room taken from a room bookings system. Below the details, we've connected it to the assets database and want to list the equipment and features found inside the room.

Problem #1:
More of an aesthetic problem than functional but when the room didn't contain equipment (or the admins had not added the equipment info on the system), it just showed the heading "Equipment".

Iteration in T-SQL?
Unlike good old MySQL where you can do iteration within the query (SELECT @ncount:=@ncount+1), we're stuck with T-SQL. Ask Google how to iterate in T-SQL and you're confined to functions, temporary tables or little subroutines which just overcomplicates basic simple select queries... However set a counter to 1 for each and then get BIDS to do the sum of this using an MDX expression.

T-SQL Expression:
Consider the following expression as the one we use to get the equipment information:
  1.  SELECT  
  2.  DISTINCT  
  3.        re.[RoomId] 
  4.        ,re.[SiteId] 
  5.        ,re.[EquipId] 
  6.        ,eq.[Description] 
  7.  FROM  
  10.  [EQUIPMENT] eq  
  11.  ON  
  12.  eq.EquipId=re.EquipId 
  13.  WHERE  
  14.  re.RoomId=@GivenRoom 
  15.  AND 
  16.  re.SetId=@setId 
This seems to have problems because when there are no matching rows (so zero results), BIDS doesn't seem to know what to do with this. We could add a row with a SELECT UNION statement but I found this can be resolved by adding a counter, so the above becomes:
  1.  SELECT  
  2.  DISTINCT  
  3.        re.[RoomId] 
  4.        ,re.[SiteId] 
  5.        ,re.[EquipId] 
  6.        ,eq.[Description] 
  7.        ,1 Counter   
  8.  FROM  
  10.  INNER JOIN 
  11.  [EQUIPMENT] eq  
  12.  ON  
  13.  eq.EquipId=re.EquipId 
  14.  WHERE  
  15.  re.RoomId=@GivenRoom 
  16.  AND 
  17.  re.SetId=@setId 
Reminder: The counter = 1 means that if I call on the sum of these, I can check if zero rows were returned because Sum(Fields!Counter.Value, "Equipment") should come back as zero.

MDX Expression:
One final and a little confusing tweak to make it all work! Where "Equipment" is the name of the dataset.
  1. Bring up the tablix properties (or the textbox you want to hide)
  2. Select "Visibility" in the left margin
  3. Type the following expression (where "Equipment" is the name of the dataset)
    1.  =IIF(Sum(Fields!Counter.Value, "Equipment")<1, True, False) 

Because it's sort of a global value, I can assign the same MDX expression to anything else that needs to be affected. So for me, on the actual heading "Equipment", I did the above and it FINALLY disappeared!

Displaying a message instead:
My end-users were a little confused. At least the reports of "it says 'equipment' but theres nothing underneath it" had stopped. They then asked "can we have a message instead?"... ... ...

Method #1 To put the message in the resulting tablix:
  1. Undo all your hard work on visibility expressions (set Visibility to "Show")
  2. Change the data value to an expression like
    1.  =IIF(Sum(Fields!Counter.Value, "Equipment")=0, "None specified", Fields!Description.Value) 
    where "equipment" is the name of your dataset and Fields!Description.Value is where the usual results are stored.
Method #2 Replace the table with a textbox:
  1. Hide the tablix based on the visibility expression as before
    1.  =IIF(Sum(Fields!Counter.Value, "Equipment")<1, True, False) 
    so don't undo all your hard work :c)
  2. Put a textbox behind the table (right-click on it, layout > send to back), the expression for visibility on the textbox should be something like:
    1.  =IIF(Sum(Fields!Counter.Value, "Equipment")>0, True, False) 
If you're bugged by the warning "Overlapping report items are not supported in all renderers" then I'd suggest you use method #1.

Issues I ran into:
I replaced the empty dataset with the message "None" using Method #2 above. I noticed when a textbox is hidden, the next textbox moves to fill up the space... There are some suggestions on the net but they didn't work or didn't apply for me. I had to insert a rectangle that separates the textboxes (so fills up the empty space with an object)...

The visibility expressions for both hiding and displaying either the textbox or the tablix has to be applied to both the textbox and tablix (as tablix doesn't show if resultset is empty by default anyway but still occupies a blank space which moves your textbox down...)

Searches that got me nowhere
  • ssrs display message if dataset rows is zero
  • bids show message for an empty dataset


0 Joel L Monday, 3rd September 2012, 12:24 pm
Hi Rob,

These MDX expressions are in the Tablix Properties, anywhere there's a "fx" button.

In this particular case: open the tablix properties, click on visibility, then click on the "fx" button to put in the expression.
+1 Rob Monday, 3rd September 2012, 5:10 am
Sorry I don't understand. Where are you using MDX?
0 Joel L Thursday, 9th February 2012, 10:50 pm
Hi Penny,

I found that if you use expressions on the height of the data rows, you can solve the displacements. On some reports, however, I had to split the tablix into 2 for aesthetic reasons.

Hope that helps!

0 Penny Thursday, 9th February 2012, 11:57 am
In RS2005 the hidden property hid the table and removed the white space, however in SSRS 2008 although the tablix is invisible, the space is left there. How can I get the tablix to take up no space?
+1 Sarah Monday, 12th December 2011, 6:26 pm
Great article!! Worked perfectly.

Add comment