CODEWITHSUNDEEP
sql-servervb.netreporting-services
user677526
user677526

Reputation:

SSRS: String concatenation with nullable strings

SSRS hate me. Right now, the feeling is mutual.

I'm attempting to work with strings that may or may not be null. I was using VB's IIF statement; after doing some searching on the errors I was receiving, I discovered that the IIF statement doesn't short-circuit the way you'd expect most if statements to do. I fixed the issue by changing the IIF statements to If. Thought things were good.

Until I ran into the problem referenced here.

So I'm stuck. I've attempted to place everything in a report code block, thinking that I could isolate myself from the problems I'm running into, like so:

Public Function DisplayUserName(ByVal UserName As String, ByVal FirstName As  String, ByVal MiddleName As String, ByVal LastName As String) As String
    'SSRS sucks. Specifically, with empty strings and if statements that aren't IIF. Hence the code block.
    'See https://stackoverflow.com/q/27418185/677526. Unfortunately IIF can't work here.
    Dim result As New System.Text.StringBuilder()

    If Not System.String.IsNullOrWhitespace(UserName) Then
        result.Append(UserName)
        result.Append(VbCrLf)
    End If

    If Not System.String.IsNullOrWhitespace(FirstName) Then
        result.Append(FirstName)
        result.Append(" ")
    End If

    If Not System.String.IsNullOrWhitespace(MiddleName) Then
        result.Append(GetChar(MiddleName, 1))
        result.Append(". ")
    End If

    If Not System.String.IsNullOrWhitespace(LastName) Then
        result.Append(LastName)
    End If

    Return result.ToString()
End Function

...but now I'm running into issues with IsNullOrWhitespace not being a member of System.String.

I'm almost at a loss here, as I'm fighting my tools. Has anyone run into this before when trying to concatenate a group of strings that can be null? How did you solve the issue? Of note, I can do this in our SQL query (and I'm almost about to give up and say whatever) but string concatenation seems to slow the query down somewhat.

Upvotes: 2

Views: 1670

Answers (2)

Hiruy
Hiruy

Reputation: 11

It is common to come with issues related to String concatenation with Null values in SSRS. Specially after UAT sessions as a result of having empty cells in reports while there is data in the database. There are different approaches to tackle this issue. I will list the approaches, Pick my favorite and State my reasoning.

Approach 1: Using 

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

setting. which controls whether concatenation results are treated as null or empty string values. By changing the setting to OFF

SET CONCAT_NULL_YIELDS_NULL OFF;

You can concatenate a string value with a null or empty value and return a value other than null if there is a string value in any of the fields you are using to concatenate.

Approach 2: Using Conditional Statements, we can use IF conditions or Case Statements to check if the value of a field to be used in the concatenation query is Null or not. Hence, we can also assign a string value to that particular cell to be concatenated if the condition turns out to be true.

Approach 3: Using system functions such as ISNULL() and COALESCE() .

ISNULL ( check_expression , replacement_value )
COALESCE ( expression [ ,...n ] )

MY PICK COALESCE() JUSTIFICATION AS FOLLOWS
1- Even though changing the setting is the easies and the most cost effective approach. In most companies it is not up to the developer to change settings or the changes you made in your tsql objects might even be reversed by a sysadmin with higher privileges. 2- even If you get the approval Microsoft have announced that this feature will be set to 'ON' only in future upgrades, as a result it is not advisable to relay on this setting. check the link https://msdn.microsoft.com/en-us/library/ms176056.aspx 3-why write a custom code when a system function can do the job 4- I prefer wrapping each field with COALESCE() than ISNULL()because it is flexible to add n number of fields to compare.

example 

SELECT 
EgField1,
EgField2,
COALESCE(Person.FName,'FNameNotAvialable') +    
COALESCE(Person.LName,'LNameNotAvialable') AS [FULL NAME]
FROM Person.Person

Upvotes: 1

parfilko
parfilko

Reputation: 1454

set proper version for .net in project - for earlier version it is not supported.

.NET Framework Supported in: 4.5.2, 4.5.1, 4.5, 4

.NET Framework Client Profile Supported in: 4

Portable Class Library Supported in: Portable Class Library

.NET for Windows Store apps Supported in: Windows 8

.NET for Windows Phone apps Supported in: Windows Phone 8.1, Windows Phone 8, Silverlight 8.1

Upvotes: 1

Related Questions