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! 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi sanpanico 

         

        Could you show how you defined the parameter MSCasePQDays and used it in the filter of the dataset? I guess Null values are still filtered out somewhere so you couldn't see them. 

         

        Maybe you can try this: Allow Nulls as SSRS Report Parameter

         

        Best Regards,
        Jing