SSRS Hide results table if empty

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

Situation:
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:
copyraw
SELECT 
	DISTINCT 
      re.[RoomId]
      ,re.[SiteId]
      ,re.[EquipId]
      ,eq.[Description]
FROM 
	[ROOMEQUIPMENT] re
INNER JOIN
	[EQUIPMENT] eq 
ON 
	eq.EquipId=re.EquipId
WHERE 
	re.RoomId=@GivenRoom
AND
	re.SetId=@setId
  1.  SELECT 
  2.      DISTINCT 
  3.        re.[RoomId] 
  4.        ,re.[SiteId] 
  5.        ,re.[EquipId] 
  6.        ,eq.[Description] 
  7.  FROM 
  8.      [ROOMEQUIPMENT] re 
  9.  INNER JOIN 
  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:
copyraw
SELECT 
	DISTINCT 
      re.[RoomId]
      ,re.[SiteId]
      ,re.[EquipId]
      ,eq.[Description]
      ,1 Counter  
FROM 
	[ROOMEQUIPMENT] re
INNER JOIN
	[EQUIPMENT] eq 
ON 
	eq.EquipId=re.EquipId
WHERE 
	re.RoomId=@GivenRoom
AND
	re.SetId=@setId
  1.  SELECT 
  2.      DISTINCT 
  3.        re.[RoomId] 
  4.        ,re.[SiteId] 
  5.        ,re.[EquipId] 
  6.        ,eq.[Description] 
  7.        ,1 Counter 
  8.  FROM 
  9.      [ROOMEQUIPMENT] re 
  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)
    copyraw
    =IIF(Sum(Fields!Counter.Value, "Equipment")
    1.  =IIF(Sum(Fields!Counter.Value, "Equipment") 

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
    copyraw
    =IIF(Sum(Fields!Counter.Value, "Equipment")=0, "None specified", Fields!Description.Value)
    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
    copyraw
    =IIF(Sum(Fields!Counter.Value, "Equipment")
    1.  =IIF(Sum(Fields!Counter.Value, "Equipment") 
    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:
    copyraw
    =IIF(Sum(Fields!Counter.Value, "Equipment")>0, True, False)
    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
Category: SQL Server Reporting Services :: Article: 334

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.