Filter condition similar to sql

[mrcool]

Hi Guys,

I would like to know if it's possible to filter few rows in cognos in similar to the below query:

select

col1,
col2

from table

where (col1,col2) <> ('abc','yz')

I am basically removing only few combinations of the columns. At present I have concatenated the 2 columns in cognos and then I have applied the filter but I would like to know if there any other options available.

P.S I achieved the same behaviour with or condition too.

Thanks,
mc

[linbai]

use the sql as data source

[bdbits]

where (col1,col2) <> ('abc','yz')

To be honest, I have not seen that kind of column/value list in a SQL WHERE clause before and I have been doing this for decades. I find it very ambiguous and would use different syntax, but maybe that is a lack of familiarity. Not even sure exactly what it is saying.

At any rate, I definitely do not recommend using SQL as a report data source. Cognos allows it but using it is sub-optimal, is subject to problems when the underlying data semantics change, and limits some of the out-of-the-box product functionality. Use SQL only if you absolutely must.

[BigChris]

Are you essentially looking for those records where

Code Select Expand

Col1 <> 'abc' and Col2 <> 'xy'
Your original statement isn't very clear (and like bdbits, I've never come across that syntax before)

Col1	Col2	Included?
abc	xy	No
abc	mn	??
def	xy	??
def	mn	??

If you can fill in the blanks we might be able to help you get to the results you're after.

[mrcool]

Hi Guys,

Thanks for your response.
I am using postgresql and it does support this syntax.

Are you essentially looking for those records where

Code Select Expand

Col1 <> 'abc' and Col2 <> 'xy'
Your original statement isn't very clear (and like bdbits, I've never come across that syntax before)

Col1	Col2	Included?
abc	xy	No
abc	mn	??
def	xy	??
def	mn	??

If you can fill in the blanks we might be able to help you get to the results you're after.

Chris - Col1 <> 'abc' and Col2 <> 'xy' is not same as (col1,col2)<>('abc','xy')
Your filter condition will drop 2 rows where as my condition will drop only 1 row which is the combination of 'abc' in col1 and 'xy' in column2. From your examples only 1st row will be dropped.
You can achieve the same result using the or condition Col1 <> 'abc' or Col2 <> 'xy' .

Please let me know if I am not clear enough.

[bdbits]

This is covered in the Report Studio user guide (aka online help).

http://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/t_cr_rptstd_wrkdat_filter_data_rel.html#cr_rptstd_wrkdat_filter_data_rel