none
How to handle null values in SSRS 2008

    Question

  • my sharepoint list query is fetching null value as there are no rows  so  in my reporting server it gives an error "parameter values missing" . i used IsNothing like this in filter part in one of my dataset

    expression:  true

    operator: =

    value:  =Iif(IsNothing(Parameters!Bench_Resource.Value(0)),"NA","present")

    still its not working


    thank you

    Thursday, June 07, 2012 1:31 PM

Answers

  • Hi Immortal12,

    From your description, it seems that the parameter “Bench_Resource” retrieves values from the SharePoint list query, right? The error may occur if the query returns no value while the parameter doesn’t allow null value.

    To avoid this issue, please open the Parameter Properties window, check the “Allow null value” and set the “Available Values” to “None”. If it is a multi-value parameter, please uncheck the “Allow multiple values” at this time. After that, modify the filter like below:
    Expression: [Bench_Resource]
    Operator: =
    Value: =IIF(IsNothing(Parameters!Bench_Resource.Value),Nothing,Parameters!Bench_Resource.Value)

    When the SharePoint list is updated and returns non-null values, we can modify the parameter and retrieve the parameter available values from the query.

    Hope this helps.

    Regards,
    Mike Yin

    Thursday, June 14, 2012 6:40 AM

All replies

  • Add a new Calculated Field (ex: CalcBench_Resource) in the Field Source, and set the expression to replace null with <Empty>.
    Now filter your data by the calculated field, not the current field.
    =IIF(Fields!CalcBench_Resource.Value=Nothing,"<Empty>",Fields!CalcBench_Resource.Value)

    Thursday, June 07, 2012 2:29 PM
  • Hi Immortal12,

    From your description, it seems that the parameter “Bench_Resource” retrieves values from the SharePoint list query, right? The error may occur if the query returns no value while the parameter doesn’t allow null value.

    To avoid this issue, please open the Parameter Properties window, check the “Allow null value” and set the “Available Values” to “None”. If it is a multi-value parameter, please uncheck the “Allow multiple values” at this time. After that, modify the filter like below:
    Expression: [Bench_Resource]
    Operator: =
    Value: =IIF(IsNothing(Parameters!Bench_Resource.Value),Nothing,Parameters!Bench_Resource.Value)

    When the SharePoint list is updated and returns non-null values, we can modify the parameter and retrieve the parameter available values from the query.

    Hope this helps.

    Regards,
    Mike Yin

    Thursday, June 14, 2012 6:40 AM
  • Hello Immortal,

    Please try out this.. this may help you..

    in Expression field ..  : =IIF(IsNothing((Parameters!Bench_Resource.Value(0)), "1", Field!Resource.Value )

    Operator : whatever is required for your report.

    Type : (If it is integer, write it in expression as 1 else if it its string, write it as "1")

    Value : =(IIF(IsNothing((Parameters!Bench_Resource.Value(0)), "1",Parameters!Bench_Resource.Value)


    20CF


    Thursday, June 14, 2012 7:10 AM