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! 

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

sanpanico's avatar
sanpanico
Frequent Visitor
1 year ago

Good day Jing, and thank you for the response.

Yes, I have checked the box to allow nulls.

 

Specified my values from the filter I created against the main DataSet. (default value is "-1"

 

I set the Starting, Ending filter range (based on Amira's recommendation - Thanks again Amira!):

 

I am asking RB to evaluate the data to see if there are null values (blanks) in Fields!PQ_Days.Value, then perform the Switch action Parameters selection based on that evaluation result.

My current result is this, which leads me to believe there is a syntax issue:

 

I have also created a separate DataSet for the null PQ_Days, but that only introduced more confusion for me (and Report Builder)! 😉