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.

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