ReportServer Forum

Patryx

Member

Registered: 2019-03-25

Parameter in report for choosing Yes/No or empty

Hi,
I have problem with datasource parameter with single selection using dropdown. I used CSV list with give me 'value' and 'display'. 'Value' and 'display' are strings.
I want to have after opening combobox 3 options (first as empty to let users easily switch off parameter value, Yes and No):

Yes
No

First problem was how to display empty string (your component makes it squeezed what's look awful, but I resolve it to put as empty invisible chars).
BUT the problem is that I always get"null" when I choose first option. Why? I want to have NULL as I declared in Query wrapper.
Any scenario how to get value of the parameter (datasource with single selection) as NULL? I nned it to compare if I should use any filter or no.

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter in report for choosing Yes/No or empty

Hi Patryx,

can you please share screenshots of the configuration in order for us to be able to recreate it ?

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Parameter in report for choosing Yes/No or empty

Ok.
Parameter name: P_PEDESTRIAN_INVOLVED. I added it as column in report in SELECT : SELECT $P{P_PESEDTRIAN_INVOLVED} as key1 FROM ...(my table).

From parameter tab I set nothing or set the empty one. The value presented as:

From parameter tab I set Yes. The value presented as:

How to get value as typical NULL (I want in my Query used iwn WHERE LIKE $P{P_PEDESTRIAN_INVOLVED} IS NULL?
Why key1 (VARCHAR) shows "true" and" false" or "null" even if I have N and T chosed?

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter in report for choosing Yes/No or empty

Hi Patryx,

can you post the query you are trying to use? The rest of this query (simplified, but still a correct query):
WHERE LIKE $P{P_PEDESTRIAN_INVOLVED} IS NULL.
How are you trying to use the parameter in your query exactly ?

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Parameter in report for choosing Yes/No or empty

sorry, I shouldn't have written LIKE. I meant WHERE like  $P{P_PEDESTRIAN_INVOLVED} IS NUL, so for example:
WHERE ($P{P_PEDESTRIAN_INVOLVED} IS NULL OR ($P{P_PEDESTRIAN_INVOLVED} ='T' AND columnX in (1,2)) OR ($P{P_PEDESTRIAN_INVOLVED} ='N' AND columnX in (5,6)) )
But this doesn;t matter. Question is how to get $P{P_PEDESTRIAN_INVOLVED} as NULL

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter in report for choosing Yes/No or empty

Hi Patryx,

your datasource parameter is a multi-value parameter, so you can not access it like a single-value parameter.

Check the multi-value expressions: https://reportserver.net/en/guides/admi … arameters/
$X{IN, column, parameterkey}
$X{NOTIN, column, parameterkey}

etc.

I think the appropriate would be:
$X{IN, column, parameterkey}
Regarding NULL values:
"If all parameter values are other than NULL, an expression in the form <column> IN (?, ?, .., ?) will be generated. If the list of values includes NULL values as well as values other than NULL the generated expression is:
(<column> IS NULL OR <column> IN (?, ?, .., ?))"

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Parameter in report for choosing Yes/No or empty

Hi,
I remember if I choose single selection in datasource parameter, the construction $X{IN, column, parameterkey} doesn't work. I had such example in the past and I had to reorganize my Where conditions(from $X{IN...} to normal column = parameterkey) . Unfortunately I am now out of office till Monday so I cannot check it.
Let's assume it works as you write but I need to do create different 'where' depends on value chosen in a parameter. I wrote you example. This doesn't solve it because it just check if column has one of the value.

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter in report for choosing Yes/No or empty

Hi Patryx,

"but I need to do create different 'where' depends on value chosen in a parameter."
please post exactly what you are trying to do. What 'wheres' are you trying to use ?

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Parameter in report for choosing Yes/No or empty

Hi Eduardo,
As I posted before, for example:

 
WHERE ($P{P_PEDESTRIAN_INVOLVED} IS NULL OR ($P{P_PEDESTRIAN_INVOLVED} ='T' AND columnX in (1,2)) OR ($P{P_PEDESTRIAN_INVOLVED} ='N' AND columnX in (5,6)) )

.
In general I need to read the parameter value chose in single selection mode from datasource parameter. In my example I have to know if user chose nothing (Null) or chose Yes or No.

Last edited by Patryx (2019-08-14 21:05:23)

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter in report for choosing Yes/No or empty

Hi Patryx,

you are right, this is a single-value datasource parameter, so the $IN{} clause doesn't make sense here. We will take a look at this and let you know.

Regards,
Eduardo

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter in report for choosing Yes/No or empty

Hi Patryx,

in the meanwhile, you can also use a similar query as a workaround:

Don't use NULL as a the key, but e.g. -1.
Then you can  write a similar query:

WHERE (case when $P{P_PEDESTRIAN_INVOLVED}=-1 then null else $P{P_PEDESTRIAN_INVOLVED} end case IS NULL OR (case when $P{P_PEDESTRIAN_INVOLVED}=-1 then null else $P{P_PEDESTRIAN_INVOLVED} end case ='T' AND columnX in (1,2)) OR (case when $P{P_PEDESTRIAN_INVOLVED}=-1 then null else $P{P_PEDESTRIAN_INVOLVED} end case ='N' AND columnX in (5,6)) )

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Parameter in report for choosing Yes/No or empty

Hi Eduardo,
Which Datatype in Edit parameter I should choose to make your example working? Could you paste screens?
Your example doesn't work. Another bug in your Report Server - even I choose default value as the empty element (here with -1) when I open report and I don't set this parameter, its value after execute query is "null". If I choose an "empty" element in parameter tab, then the value is changed to -1. To sum up, the initial (default value) is never set...

Last edited by Patryx (2019-08-19 08:32:17)

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Parameter in report for choosing Yes/No or empty

Hi Patryx,

for completeness: we are looking into the problem you mention with null values in RS-3623.

Regarding the default value: RS-3632

Regards,
Eduardo