Is there a COALESCE() function in SSRS? Please help.

Question

Post reply

Is there a COALESCE() function in SSRS? Please help.

marksquall

Ten Centuries

Points: 1093
More actions
September 28, 2015 at 9:25 am

#308220

Dear SQLcentral.com members and administrators:
Hello, and a pleasant day. I hope everyone is in good health upon reading this forum.
My RDL uses a stored procedure in getting data from the database, and typically I have a concatenated columns for a specified home address:
RTRIM(COALESCE(BLDGNum,'')) + RTRIM(COALESCE(Street,'')) + RTRIM(COALESCE(City,'')) AS [Home Address],
After a long while, my supervisor said that I should do the text formatting right in the RDL. So I found the RTRIM() in the Common Function->Text in the Category Box in Expression, but I cannot find the COALESCE().
Is there a COALESCE() function, or any counterpart of it in SSRS?
Thank you and Godspeed.
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm not sure if it's included in a recent version, but usually this is what it works.

=iif(isNothing(Fields!FV1.Value), Fields!FV2.Value, Fields!FV1.Value)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

marksquall Ten Centuries Points: 1093 More actions · Answer 2

Dear Luis:

Hello. I just want to say thank you for the example. I think this will work. 😀

Have a nice day ahead and Godspeed.

Very truly Yours,

Mark Squall

________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

dbasri Valued Member Points: 57 More actions · Answer 3

You can create your own COALESCE function and add it to any report.

1. Right click a blank part of the report designer page, then select Report Properties.

2. Select the Code section.

3. Add this function

Function Coalesce(fieldValue As Object, defaultValue As Decimal) As Decimal

If IsDBNull(fieldValue) OrElse IsNothing(fieldValue) Then

Coalesce = defaultValue

Else

Coalesce = CDec(fieldValue)

End If

End Function

4. To use create an Expression as: =Code.Coalesce( [field, expression or function], default)

Example: =Format(Code.Coalesce(Sum(Fields!m1.Value) + Sum(Fields!m2.Value), 0), "#,###")

Puts a default of 0 for the added sums, then formats the result.