ssrs expression if field is not null

"SSRS: Handling Non-Null Fields"

By Anthony Walker
AD

When working with SQL Server Reporting Services (SSRS), there are times when you might want to display a field's value only if it's not null. This can be achieved using expressions in SSRS. Understanding how to use these expressions can greatly enhance the flexibility and professionalism of your reports.

In this guide, we will delve into the world of SSRS expressions, focusing specifically on how to display a field value conditionally, i.e., only if it's not null.

Working with SSRS Expressions

SSRS expressions provide a powerful tool to manipulate data within your reports. They allow you to perform calculations, format dates, and make decisions based on data values.

AD
S.s.r
S.s.r
a man in a white shirt and striped tie looking at the camera with an intense look on his face
a man in a white shirt and striped tie looking at the camera with an intense look on his face
a man smiling and wearing a black shirt
a man smiling and wearing a black shirt

To use an expression in SSRS, you need to click on the field or cell where you want to insert the expression. Then, right-click and select "Expression..." from the context menu. This will open the Expression Editor where you can input your expression.

Understanding the IIf Function

The IIf function is a built-in SSRS function that allows you to evaluate a condition and return one of two values. Its syntax is: IIf(condition, truepart, falsepart).

a man in a blue shirt and black blazer looking at the camera with an intense look on his face
a man in a blue shirt and black blazer looking at the camera with an intense look on his face
an anime character holding his finger up in the air
an anime character holding his finger up in the air
an anime character with white hair and red eyes looks at the camera while standing in front of a dark background
an anime character with white hair and red eyes looks at the camera while standing in front of a dark background

The IIf function evaluates the condition first. If the condition is true, it returns the result of evaluating the truepart. If the condition is false, it returns the result of evaluating the falsepart.

Using IIf to Check for Null Values

To check if a field is null and only display its value if it's not, you can use the IIf function in conjunction with the IsNothing function. The IsNothing function returns True if a field is null and False if it has a value.

AD

Here's an example of how you might use these functions together: IIf(Not IsNothing(Fields!FieldName.Value), Fields!FieldName.Value, "Not Available").

a close up of a person smiling and making a face with his mouth open in front of him
a close up of a person smiling and making a face with his mouth open in front of him
a man looking up at the sky with his eyes wide open
a man looking up at the sky with his eyes wide open
a man smiling and wearing a jean jacket in front of a wall with people on it
a man smiling and wearing a jean jacket in front of a wall with people on it
a close up of a person wearing a white shirt and looking at the camera with a serious look on his face
a close up of a person wearing a white shirt and looking at the camera with a serious look on his face
a man standing in front of a poster
a man standing in front of a poster
two young men standing next to each other
two young men standing next to each other

In this example, if the FieldName field is not null, it will display its value. If it is null, it will display "Not Available".

Displaying Alternate Text for Null Values

Sometimes, you might want to display a custom message or a link when a field is null. This can be done by using different approaches depending on your specific needs.

AD

For instance, you could set the field to display a static text like "N/A" when it's null, or use the built-in EmptyFields function to place a URL or a link instead of the null value.

a man in a blue t - shirt looking at the camera with an intense look on his face
a man in a blue t - shirt looking at the camera with an intense look on his face

Displaying a Static Text for Null Values

To display a static text for null values, you can use the Dans function. Dans(function, fieldname, "alternate text") will display the field's value unless it's null. In that case, it will display the "alternate text".

For example: Dans("Fields!FieldName.Value", "N/A") will display the FieldName's value unless it's null, in which case it will display "N/A".

AD

Displaying a URL for Null Values

The EmptyFields function can be used to conditionally display a URL in place of a null value. For example, EmptyFields("Fields!FieldName.Value", "@URL") will display the FieldName's value unless it's null, in which case it will display the URL specified by the "@URL" parameter.

By using these techniques, you can create more informative and user-friendly reports, ensuring that null values don't disrupt your report's layout or reader's understanding. Of course, these are just a few examples of the many expressions and functions available in SSRS. Continued learning and experimentation will help you unlock the full power of SSRS expressions for your reporting needs.

AD