Post reply

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

  • 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

  • September 28, 2015 at 10:07 am

    #1829937

    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

  • September 28, 2015 at 11:08 am

    #1829950

    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

  • December 16, 2015 at 11:54 am

    #1846395

    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.

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

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