If field is NOT null, then hide an object (SSRS Expression)

ONLINECAST

2 min read

·

07-10-2024

5

0

Share

Hiding Objects in SSRS Reports Based on Field Values: A Simple Guide

Problem: You're working on an SSRS report and need to conditionally hide an object based on the value of a field. Specifically, you want to hide the object if a field is not null.

Rephrased: Imagine you're building a report that shows customer details. You only want to display a "Last Order Date" field if the customer has actually placed an order. If they haven't placed an order, this field should be hidden.

Scenario and Code:

Let's say you have a report with a textbox named txtLastOrderDate that displays the customer's last order date. You want to hide this textbox if the OrderDate field is empty (NULL).

Here's the original code you might try:

=IIF(IsNothing(Fields!OrderDate.Value), True, False)

Explanation:

The IIF function in this code snippet is an SSRS expression that evaluates a condition and returns a value based on the outcome.

• IsNothing(Fields!OrderDate.Value) checks if the OrderDate field is null.
• If it is null, the expression returns True, indicating the object should be hidden.
• If the OrderDate field has a value, the expression returns False, meaning the object should be visible.

Why this code doesn't work:

The issue with this code is that SSRS interprets the IIF function differently than you might expect. When evaluating the condition, it checks if the OrderDate field is NULL before any data is pulled from the dataset. This means the expression always evaluates to True, and the txtLastOrderDate textbox will always be hidden, even if the field has a value in the data.

The Solution:

The correct way to hide the textbox based on a non-null field is to use the NOT operator in conjunction with IsNothing. Here's the updated code:

=NOT(IsNothing(Fields!OrderDate.Value))

Explanation:

• IsNothing(Fields!OrderDate.Value) checks if the OrderDate field is null.
• NOT reverses the result of the IsNothing check. If OrderDate is NULL, NOT returns False (hidden).
• If OrderDate is not NULL, NOT returns True (visible).

Steps to Apply the Solution:

1. Open the Report Properties: Right-click on the report and select "Report Properties."
2. Navigate to Visibility: Go to the "Visibility" tab.
3. Select "Hidden" and the expression: Choose the "Hidden" option and enter the corrected expression in the "Show or hide based on an expression" field: =NOT(IsNothing(Fields!OrderDate.Value))
4. Save the Changes: Click "OK" to save your changes.

Additional Tips:

• Understanding IIF: While the IIF function isn't appropriate in this scenario, it is still useful in SSRS reports. Use it to display different values or formatting based on field values.
• Testing Your Expression: Always test your expressions thoroughly. Preview your report and verify that the object is hidden when the field is null, and visible when it's not.

Conclusion:

By understanding the proper use of IsNothing and the NOT operator, you can successfully hide objects in SSRS reports based on field values. This technique allows you to create more dynamic and user-friendly reports that present information based on specific conditions.