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; >30days, 1yr, 2 yrs, etc.) :

 

Starting duration range:

=IIF(IsNothing(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),"", 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))

 

Note: I have also tried to incorporate the following syntax:

=Switch(
Parameters!IMSCasePQDays.Value=BLANK(),0,
Parameters!IMSCasePQDays.Value=-1,0,...

 

What results is the same as when I do not incorporate syntax to include blanks (nulls).  I have read that this syntax works in other conditions, but clearly I am missing something.  Suggestions welcome!
Cheers,

~San

 

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! 

8 Replies

  • sanpanico's avatar
    sanpanico
    Frequent Visitor
    1 year ago

    Amira, 

    Thank you for your response!
    I copied/pasted your suggestions, individually, and in conjunction. The first result was no change in the information. The second resulted in an error stating that "Failed to evaluate the FilterValues of the DataSet 'PrimaryDataSet'."
    I likely have an error in syntax?
    I will continue to move the conditions around.

    If anyone else has any suggestions, please submit them.  Again thank you!

    • AmiraBedh's avatar
      AmiraBedh
      Super User
      1 year ago

      Can you provide some input data and the expected output ?

      • sanpanico's avatar
        sanpanico
        Frequent Visitor
        1 year ago

        Amira, thank you.

        My intent is to show ALL data, including null values, to expose erroneous information (bad entries), to be corrected by the user(s).

        In Query Designer, filtering for null values (blanks), exposes that data.

         

        If no filter is applied, all information  (including null values data) is observed in the Excel output.

         

        However, I also need to allow the user to utilize a prompt (parameters) to select a desired range of data. (eg; >30days, 1yr, 2 yrs, etc.) which is why a filter (switch statement) is being implemented.  When I apply the filter, I lose the null values, leading me to believe that my syntax is improper.

         

        I cannot include further data, so I hope this additional information is helpful?

        ~San

  • 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
    )
)