Post reply

Expression to show Null values as zero

  • September 5, 2011 at 12:19 am

    #256616

    Hi...

    I have a report showing a field "QTY" which has null values in it. I have tried displaying it as 0 using Qty = nothing, Qty is nothing.. but nothing has worked. the report shows #Error.

    This field is from table "A" in the cube. The item from table A that matches with the item in Table B alone has values. Others have this null value.

    Any suggestions???

  • September 5, 2011 at 4:24 am

    #1376995

    the generic way to deal with nulls is usually to use an isnull command, or if this doesn't exist in the language you can achieve the same thing with coalesce. Failing that you could try a case or switch statement.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • September 8, 2011 at 6:38 am

    #1378489

    Here are a couple that I use.

    Common SSRS expression would be

    =IIf(Fields!qty.Value is nothing, 0, Fields!qty.Value) - swap single null with 0

    =IIf(Fields!qty.Value is nothing, Fields!qty2.Value, Fields!qty.Value) - swap null with another field. If the other field might be null, you could do a nested If, or as mentioned a switch may also work, depending on your situation.

    SQL

    isnull(qty, 0) as qty

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

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