Wednesday, October 22, 2014
   
Text Size
Login

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:

  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:

  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)

    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

Comments   

Sarah
# Sarah Mon, 12th December 2011
Great article!! Worked perfectly.
Like | Dislike | +1 Reply | Reply with quote | Quote
Penny
# Penny Thu, 9th February 2012
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?
Like | Dislike | 0 Reply | Reply with quote | Quote
# Webmaster Thu, 9th February 2012
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!

Joe
Like | Dislike | 0 Reply | Reply with quote | Quote
Rob
# Rob Mon, 3rd September 2012
Sorry I don't understand. Where are you using MDX?
Like | Dislike | +1 Reply | Reply with quote | Quote
# Webmaster Mon, 3rd September 2012
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.
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Convert to Proper Case in T-SQL

    • Tue 07-Oct-14
      Really comprehensive function. Nice work! Dave.
      starsky51
  • Joes Quicklist Weblinks (JQW)

    • Wed 08-Oct-14
      Thanks jazzmang! This module is due for an update so I'll review this issue in the next version.
      Webmaster  
    • Tue 07-Oct-14
      I've set "Display Images" under Module Settings but the modules continues to display a hard coded CSS ...
      jazzmang
  • Joes Word Cloud (JWC)

    • Thu 16-Oct-14
      hello i use your tag cloud component in my joomla 2.5 website. i'm french and there is probleme with ...
      plykite2010  
    • Tue 07-Oct-14
      Hello, i have Joomla 2.5.27 and Module JWC v3.2.2 installed. In Configuration i fill in how many Word ...
      Maic