SSRS Hide results table if empty

: Joel Lipman; SQL Server Reporting Services; 29 March 2011; Hits: 38673

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

Bring up the "Tablix Properties" of the dataset
Look for the section "No Rows"
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:

copy raw

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

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

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:

copy raw

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

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

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.

Bring up the tablix properties (or the textbox you want to hide)
Select "Visibility" in the left margin
Type the following expression (where "Equipment" is the name of the dataset)
copyrawstyler
```
=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:

Undo all your hard work on visibility expressions (set Visibility to "Show")
Change the data value to an expression like
copyrawstyler
```
=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:

Hide the tablix based on the visibility expression as before
copyrawstyler
```
=IIF(Sum(Fields!Counter.Value, "Equipment")
```
1. =IIF(Sum(Fields!Counter.Value, "Equipment")
so don't undo all your hard work :c)
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:
copyrawstyler
```
=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