Forum Discussion

sanpanico's avatar
sanpanico
Frequent Visitor
1 year ago
Solved

Report builder expression syntax issue for null (blank) values

Good day! I am attempting to return all data including null values (blanks) in a result set.  I have created the following filter to extract, return information to calculate duration in days (eg; >3...
Show and Tell
  • sanpanico's avatar
    sanpanico
    1 year ago

    Issue resolved by creating a separate calculated field called: "PQDays_Calc", 

    and gave it this expression:  =IIf(IsNothing(Fields!PQ_Days.Value),0, Fields!PQ_Days.Value)

    Removed the check box for Null values, then, filtered on the new calc field with the original Switch Statements:

    Starting:

    =
    Switch( 
    Parameters!IMSCasePQDays.Value = -1, 0, 
    Parameters!IMSCasePQDays.Value = 0, 0, 
    Parameters!IMSCasePQDays.Value = 1, 31, 
    Parameters!IMSCasePQDays.Value = 2, 100, 
    Parameters!IMSCasePQDays.Value = 3, 366, 
    Parameters!IMSCasePQDays.Value = 4, 731, 
    Parameters!IMSCasePQDays.Value = 5, 1096 
    )

    Ending:

    =
    Switch(

    Parameters!IMSCasePQDays.Value = -1, 36500, 
    Parameters!IMSCasePQDays.Value = 0, 30, 
    Parameters!IMSCasePQDays.Value = 1, 99, 
    Parameters!IMSCasePQDays.Value = 2, 365, 
    Parameters!IMSCasePQDays.Value = 3, 730, 
    Parameters!IMSCasePQDays.Value = 4, 1095, 
    Parameters!IMSCasePQDays.Value = 5, 36500
    )

    I over complicated the issue initially! 

AmiraBedh's avatar
AmiraBedh
Super User
1 year ago

 

You should use IsNothing to check if a parameter is nullat least it is the correct way to handle nulls in RB expressions.

If the parameter might also be an empty string (""), use Parameters!IMSCasePQDays.Value = "" :

Starting Duration Range:
=IIF(IsNothing(Parameters!IMSCasePQDays.Value) Or Parameters!IMSCasePQDays.Value = "", "", 
    Switch(
        Parameters!IMSCasePQDays.Value = -1, 0,
        Parameters!IMSCasePQDays.Value = 0, 0,
        Parameters!IMSCasePQDays.Value = 1, 31,
        Parameters!IMSCasePQDays.Value = 2, 100,
        Parameters!IMSCasePQDays.Value = 3, 366,
        Parameters!IMSCasePQDays.Value = 4, 731,
        Parameters!IMSCasePQDays.Value = 5, 1096
    )
)
Ending Duration Range:
=IIF(IsNothing(Parameters!IMSCasePQDays.Value) Or Parameters!IMSCasePQDays.Value = "", "", 
    Switch(
        Parameters!IMSCasePQDays.Value = -1, 36500,
        Parameters!IMSCasePQDays.Value = 0, 30,
        Parameters!IMSCasePQDays.Value = 1, 99,
        Parameters!IMSCasePQDays.Value = 2, 365,
        Parameters!IMSCasePQDays.Value = 3, 730,
        Parameters!IMSCasePQDays.Value = 4, 1095,
        Parameters!IMSCasePQDays.Value = 5, 36500
    )
)