Dynamic Visibility

Dynamic visibility is an advanced reporting feature in SSRS that allows you to control the visibility of report items based on runtime conditions. With dynamic visibility, you can show or hide report items such as charts, tables, and fields based on user interactions or other conditions.

Syntax

The syntax for dynamic visibility in SSRS is as follows:

=IIF(condition, trueValue, falseValue) 

Example

Consider the following sample report with a table containing sales data:

To add dynamic visibility to this report, we can add a new column to the table displaying the percentage of sales contributed by each product. We can then hide this column and show it only when the user hovers over the product name.

To do this, we can follow these steps:

1. Right-click the column group to which the percentage column belongs and select "Add Column" -> "Inside Group - Right" to add a new column to the right of the product name column.

2. In the new column, enter the following expression in the header cell:

= "Percentage of Total Sales: " & FormatPercent(Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, "ProductDataset"))

This expression calculates the percentage of sales contributed by each product and formats the result as a percentage.

3. Select the header cell of the percentage column and go to the "Properties" pane. Set the "Visibility" property to "Hidden".

4. Select the cell containing the product name in the same row as the percentage column and go to the "Properties" pane. Under "Interactive Sorting", select "Enable interactive sorting on this text box" and choose the "Percentage" header column as the sorting field.

5. In the "Visibility" property of the percentage column, enter the following expression:

=IIF(ReportItems!ProductTextBox.IsSorted And ReportItems!ProductTextBox.SortExpression = "Percentage", false, true)

This expression checks whether the product name column is currently sorted by the percentage column and hides the percentage column if it is not. When the user hovers over the product name, the table is sorted by the percentage column and the percentage column is displayed.

Explanation

In the example above, we used the IIF function to determine when to hide or display the percentage column based on the user's interactions with the report. The first argument of the IIF function is the condition to check, which in this case is whether the product name column is currently sorted by the percentage column. If this is true, the second argument of the IIF function is returned, which is false, indicating that the column should be shown. If the condition is false, the third argument of the IIF function is returned, which is true, indicating that the column should be hidden.

Use

Dynamic visibility is useful when you want to control the visibility of report items based on user interactions or other conditions. With dynamic visibility, you can provide a more intuitive and user-friendly experience for your report viewers.

Important Points

• The IIF function is used to determine when to show or hide report items based on runtime conditions.
• Dynamic visibility can be used to show or hide various report items such as charts, tables, and fields.
• The conditions for dynamic visibility can be based on user interactions or other runtime conditions.

Summary

Dynamic visibility is an advanced reporting feature in SSRS that allows you to control the visibility of report items based on runtime conditions. The IIF function is used to determine when to show or hide report items based on conditions defined in the report. Dynamic visibility is useful when you want to provide a more intuitive and user-friendly experience for your report viewers.