Tutorial: Changing the Visibility Objects in Reporting Services

In the last tutorial in this series of Reporting Services tutorials we added conditional formatting to the report. Now that we’ve created our first report and have added a bit of flare to it, let’s go ahead and make it even cleaner. In this very quick demonstration, we’ll go ahead and change the visibility of rows or columns based on given conditions.

Other articles in this series (in order):

• Tutorial: Designing Your First Report in SQL Server Reporting Services
• Tutorial: Adding Conditional Formatting into Reporting Services

In our example, we’re going to continue with our SQL Server job monitor report. This tutorial will filter out jobs that are not enabled. In our scenario we don’t care about jobs that are disabled and we’re assuming they were disabled with good reason. To do this, follow these steps:

1. Select one of the fields in the report in the Visual Studio report design interface. Once you select the field, you’ll see a grid surrounding the row. Left-click on the grey column to the left of the data as shown in the below figure. By left-clicking on this row, you can now set the properties for the entire row and not a single column in the table.
2. The property you want to set in the right-pane is the Hidden property under the Visibility group. If you don’t see the Properties window on the right as seen in the above screenshot, select Properties Window under View. The default visibility property is True. To change this to our dynamic property, click the drop-down box and select <Expression…>.
3. You should now be in the Edit Expression dialog box as shown in the below screenshot. In the Expression box to the right you can type the following syntax to change the visibility (make sure you remove what was there before):
   
   =iif (Fields!enabled.Value = 0 OR Fields!enabled.Value IS Nothing, True, False)
   
   Essentially, this will change the Hidden property to True if the value of the enabled column is 0 or NULL. Otherwise, the Hidden property will be set to false.
   
4. Click OK and preview the report. If you have any jobs that are disabled, they will not show in the report any more. 

If you want to change the properties of an individual group, column or header you can follow the same logic. Even graphics can be hidden or shown with this technique. In those cases you would just select the individual object and go to the Hidden property again. That’s all there is to it! Sorry, I couldn’t stretch it out further. Currently, I’m taking this series from common newsgroup posts. If you have any areas that you’d like to see documented, please click on Opinion below to post a message to me.