Report builder expression syntax issue for null (blank) values

[sanpanico]

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

[sanpanico]

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]

Can you provide some input data and the expected output ?

[sanpanico]

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

[v-jingzhan-msft]

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 · Accepted solution]

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]

Good day all!

Any other suggestions?  The solution to this seems to be elusive!

[sanpanico]

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)! 😉

[AmiraBedh]

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