Query field check if empty

[Corvinus]

Hello!

I am new to Cognos, but I need some assistance.

I have made a query with two optional variables ?paramVERS1? and ?paramVERS2?

The situation now is: I only can use filters for my results:
If no variable is filled, then I get all results, this is ok.
If both variables are filed, I only get the results for both variables, which is also ok.

BUT:
If only one variable is filled and the other is empty, it gives me all results. I would like to know, how I can apply a filter, where it can find out, if a variable is not filled or empty.

I have tried to use
if (?paramVERS1? is null) then (?paramVERS1?='Not in List')

But it only gives me an error.

So i do not know, how I can write a filter, where I can find out, if a variable is null or empty.

Thanks in advance,
Corvinus

[cognostechie]

Hello!

I am new to Cognos, but I need some assistance.

I have made a query with two optional variables ?paramVERS1? and ?paramVERS2?

The situation now is: I only can use filters for my results:
If no variable is filled, then I get all results, this is ok.
If both variables are filed, I only get the results for both variables, which is also ok.

BUT:
If only one variable is filled and the other is empty, it gives me all results. I would like to know, how I can apply a filter, where it can find out, if a variable is not filled or empty.

I have tried to use
if (?paramVERS1? is null) then (?paramVERS1?='Not in List')

But it only gives me an error.

So i do not know, how I can write a filter, where I can find out, if a variable is null or empty.

Thanks in advance,
Corvinus

Are you saying, if you provide values in one parameter and not in the other one then the report is not filtering anything? If so, that's not how Cognos behaves. There must be something else going on. When there are two prompts and both are set as optional they will work independently and the query will be filtered if one of them has values. The prompts work with the filters in the query. How are the filters defined? Have you created the filters for both prompts or only one of them? In this case, you don't need to check if the prompts are null or not though to check it you would use, 'If ?paramVERS1? is null then..  else.. '

[Corvinus]

I have a filter for both variables.
There is a database behind, and in this database I want to find out, if a company has bought something.

So, if I do not provide values fot the variables, I get a list from all companies.
If I provide values for both variables, I get filtered results for both comapnies.
If I provide a value for only one variable, I also get a list from all companies (but I want a filter only for variable 1)

So I try to find a way, how I can set a filter correctly, If the second variable is not given a value.

[cognostechie]

Your filter is probably wrong. 'Variables' in Cognos are called Prompts. For the two Prompts you have, you should have two separate filters and both should be 'optional'.

[Field_Name] = ?paramVERS1?

[Field_Name] = ?paramVERS2?

[Field_Name] would be the query item from your package.