Database null value as parameter vs TRS null parameter

[Coopbase]

Hi, I would like to know how to show the null value from the database as parameter without the conflict of TRS null filter.

Here is my current problem right now;

In my PostgreSql table, I have a status column where it has a null value ( Activated, Pending, blank value[null] ), I don't have a problem showing the Activated and Pending data when I filter them but if I choose the null value which is supposedly it should show the blank status instead it will show all status, Its like a no filter in its parameter.

What should be the best approach here? I read about multi-value parameter but I'm not quite sure about it.

[Todor]

Hello,

If the Report Parameter is supposed to accept Null values, you need to set its property AllowNull to True. The default value is False. I tested this and it worked correctly in the Report Server Manager. The Null values were respected by the Html5 Viewer.

I tested also to filter the report data with the value of the Nullable parameter. The data was filtered on the report level as expected. Here is my filtering Expression:

= (Parameters.WithNull.Value is Null) ? (Fields.Value is Null) : (Fields.Value = Parameters.WithNull.Value)

=

=true

The reason for the explicit check of whether the parameter is Null is that when comparing with a Null value you need to use the operator 'is' rather than '='.

Can you share more details about the scenario? If you sent us a sample runnable report we could use to reproduce the issue locally, this would be great.

Regards,
Todor
Progress Telerik

[Coopbase]

Thanks for responding,

Yes, I already set the property AllowNull to True.

Goal:
1. The idea is if I set the parameter to null, It would show all the data regardless on what is the status, 
2. But at the same time, I want to filter out too just the null values in the table in my parameter.

So this is what I did,

In my SQL query I put there in my WHERE clause:
WHERE signup_status = @signupstatus OR @signupstatus is NULL

But still, If I set the parameter into null, It will show all the values. Is there a way to filter out the null values in the table instead of showing all the values.

[Todor]

If I understood correctly, you would like to construct a SQL query that would return all but the Null values when the parameter is Null. I'm afraid this is outside our area of expertise, but here are some threads you may find useful:

• sql query if parameter is null select all
• How do I make my parameter work when it is a null value?
• Working with SQL NULL values