- Category: MS Reporting Services
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".
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.
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.
Consider the following expression as the one we use to get the equipment information:
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=@setIdThis 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:
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=@setIdReminder: 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.
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)
=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:
- Undo all your hard work on visibility expressions (set Visibility to "Show")
- Change the data value to an expression like
=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.
- Hide the tablix based on the visibility expression as before
=IIF(Sum(Fields!Counter.Value, "Equipment")<1, True, False)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:
=IIF(Sum(Fields!Counter.Value, "Equipment")>0, True, False)
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