Did you know you could hide a row on a SSRS report if all the fields on that row are blank?
I found a lot of documentation on how to hide a text box, or even a row based a condition or calculation, such as blank, zero or null values. However, I didn’t see much on hiding the row if the fields are all blank.
Here’s How
To be clear I am talking about a row in a table in a SSRS report, but this could also apply to a Matrix. Each row in a table is comprised of one or more text boxes, depending on the number of columns. If you have five columns, there will be five text boxes for the row. Each text box can contain its own data values, which may be data from the source or an expression that is being evaluated.
Any text box can be hidden based on an expression. But, even if all text boxes in the row are hidden, the row itself will not hide automatically. The row can be hidden based on an expression, but what if we want the row to hide if all the text boxes in the row are blank?
The secret to this is to evaluate the contents of each text box in the row. The contents of a text box can be evaluated through the ReportItems collection. You will need to know the name of each text box in your row, and since SSRS is case sensitive, provide the proper case when you reference the text box.
For example if you have Textbox1 in your row, you would access its contents by ReportItems!Textbox1.Value. SSRS seems to add text boxes in an order of its own choosing so make no assumptions.
Hiding a row can be done in an expression for the Row Visibility property. Select the entire row and right click to access Row Visibility. In this window, you can “Show or hide based on an expression”. Clicking the fx button opens the expression window.
Next, you may need to use the TRIM function to eliminate any blank spaces occupying the text box. For each text box in your row, start an expression that may end up looking like the one below.
This expression uses an IIF statement to compare each trimmed text box to an empty text string (“”) and then uses the AND clause to combine them all together into one single expression. The TRUE result means hide the row as all the text boxes are blank. If any text box has a value, the result will be FALSE and the row will not be hidden.
Crestwood has a team of SSRS reporting experts to help you. Just send us an email at Support@Crestwood.com.
