Forum Discussion
Report builder expression syntax issue for null (blank) values
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!
Can you provide some input data and the expected output ?
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
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,
JingIssue 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!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)! 😉
Good day all!
Any other suggestions? The solution to this seems to be elusive!