SSRS : Suppress blank when the sub report returns no data

In SQL Server Reporting Services 2008, we often come across scenarios where the sub report has no data.

Suppose you create a main report and the sub report inserted into the main report has no data for the parameters passed. When you run this report, you get a blank space instead where the sub report needs to appear. In cases, where you have multiple sub reports the blank space is more. This is not acceptable always.

I found several queries related to this on several forums. Even I faced a similar issue and didn't find an answer anywhere. This solution would help you reduce the space drastically.


1. In the Main report, insert a Tablix.
2. Enter the details for the main report.
3. Create a row to add the sub report.
4. Inside the row, insert a rectangle.
5. Inside the rectangle, insert your sub reports. Placing them one after the other.
In case, you have too many sub reports and you do not want to suppress the
sub report when it returns no data, in that case, I would suggest you place all
the sub reports in a single rectangle.

If you have very few sub reports or your sub reports are not placed one after the
other, in that case, you can place one sub report inside one rectangle, as per
your convenience and requirement.

6. After placing the sub reports and setting the properties as per your
requirements, set the visibility property to hide when there is no data and under
the size property of the sub report, set the Height to "0in".

For each sub report, set the following properties.
Right click on the sub report, select "Sub report properties"
Under visibility tab,
a. Set the Show/hide expressions as
=IIF(RowNumber(Nothing)>0,False,True)

Under the properties panel
b. Set the "height" under the Size property to 0

7. Move all sub reports close to each other. Ensure they don't overlap each other.

8. For the rectangle,
Right click, select rectangle properties,
Under Visibility,
a. Set the Show/hide expressions as
=IIF(RowNumber(Nothing)>0,False,True)
Under properties panel,
b. Set the "height" under the Size property to 0.

9 For the row set the visibility,
a. Set the Show/hide expressions as
=IIF(RowNumber(Nothing)>0,False,True)

Run the report, this reduces the blank space for the sub reports that return empty.

However, there are restrictions under this solution. This solution reduces the space an empty sub report occupies. This is helpful when the number of sub reports are less or when the sub reports placed one after the other are less.

1. When there are few sub reports placed adjacent to each other, the space is
negligible and this solution works.

2. Scenarios when there are numerous sub reports but the sub reports are not placed
adjacent to each other, in such cases you would not come across the space
issue with this solution. Even in this case the space per sub report is less
hence total space is less.

3. In case where the number of sub reports are large and they are placed one after
the other this solution will reduce the space but wouldn't give you "no blank"
space resolution. If too many sub reports placed next to each other the
accumulated space might be visible.


Please revert if you have questions.

Thanks,
Ashwini Rupert