Last updated on August 6th, 2022 at 04:02 pm
Hi All! I’m sure that each and every one of you have used a Tablix at one point or another when creating SQL Server Reporting Services (SSRS) reports. All rows, by default, are displayed in a Tablix, but in some instances, you may want to hide unused rows and allow the end user to expand them as needed. In my opinion, hiding unused rows gives a report a cleaner look. How can you display all rows with data and only hide unused rows in a Tablix? This was the question I was trying to answer. In the end, I determined that the best way to do this was to use an expression!
Background
Who wants to show rows of empty data? No one! What do you do? You must change the default behavior of the Visibility option. Changing the default hides all rows and only allows them to be expanded when a user clicks on the plus “+” sign.
This is exactly what we did for the Computer Collection Details report which is found in Enhansoft Reporting. When the report was initially created all rows were hidden by default. The user would typically expand the rows associated with a specific collection’s maintenance window because all they really cared about were collections with maintenance windows.
The above screenshot is how the report looked when it was first created. It was setup with the toggle column on the collection name. I highlighted two text boxes where you can see that there are two collections with values of 5 and 7 for maintenance windows. In order to see the extra rows, you must click on the “plus” icon (green arrows).
It was our Chief Architect Garth Jones’ idea to change the functionality of this report. Now, only the rows with maintenance windows are expanded by default, and all other “extra” rows without maintenance windows are hidden.
Below, I show you how I made his idea a reality.
How to Hide Unused Rows in a Tablix
I started by opening the report in Visual Studio (see the image above). From the Design page tab (#1), I selected the column where the existing toggle attribute was enabled (#2). Next, I selected the row group from the Row Groups section. In my case (#3), it is: (table1_Group2_Category).
Note: Although I’m displaying the steps within Visual Studio, similar steps can also be performed within Report Builder too.
Now, on the right side of the screen, under the Properties tab, scroll down to the Visibility section. Look for the option Hidden, as shown above in the image. True is currently selected because the line is highlighted in blue.
Remember how I said earlier that the best way to change the default was to use an expression? In order to add the expression, you must click on the downward arrow and choose <Expression…>. Below is the expression I used to hide all collections without a maintenance window.
Expression
=iif(Fields!SWinName.Value=“No Maintenance Window“, True,False)
In a nutshell, this expression is looking at each collection’s maintenance window details for the string “No Maintenance Window”. If it finds the string, it sets the results to True. If it doesn’t find the string, it sets the results to False. In other words, true hides the row and false displays the row.
How do you add the expression? First, paste it into the Set expression for: Hidden text area, and then edit it (see the steps below) as needed.
Select the appropriate column from the dataset by starting in the Category area and selecting Datasets (#1). This lists the datasets in the Item section. Next, choose the appropriate item (dataset) (#2) which then displays the values in the Values area. Double-click on the column (#3) which you intend to use in the expression (#4). Once you complete editing the expression, click on the OK button (#5).
After my updates, this is how the final version of this report looks. Notice how the two rows which have maintenance window count values other than 0 are expanded by default?
Conclusion
With this expression, I was able to accomplish my task of updating a report’s design. This expression is an effective means to showcase rows which have useful data. You can see this data immediately, when either previewing a report or when drilling through from a different report. Thanks to Garth, who came up with this idea, I was able to make it possible. I look forward to implementing a similar concept in other report sets too. Hope y’all find this useful when building your own reports. If you have any questions or need assistance with how to hide unused rows in a Tablix, you can reach out to me @SuaresLeonard or to @GarthMJ. All the best!