Trying to write an 'is missing' Filter

[rob8143]

Good Morning,

I am trying to write an 'is missing' filter in a folder.  For some reason the Filter

[Course Code]='XXX XXX'   works with no errors in Validation but
[Course Code]is missing'XXX XXX' will not work.

I am VERY VERY Green when it comes to Cognos, so I am wondering if there is something simple here.

Thanks

[MFGF]

Hi,

Could you explain exactly what you are trying to achieve with the filter?

[Course Code] = 'XXX XXX'      will return rows where the course code is XXX XXX
[Course Code] is missing      will return rows where course code is null (ie rows without a course code)
[Course Code] <> 'XXX XXX'      will return rows where the course code is not XXX XXX

Do any of these suit your requirement, or are you looking for something else?

Regards,

MF.

[rob8143]

Hi MF,

Essentially what I am trying to accomplish is a report that shows all employees who do not have that specific course code in their profile. In this case it would be the 'XXX XXX'

Thanks,
Rob

[anilkumar]

u can use not starts with'xxxxxx'

[jahnavi]

You can use 'not in' to filter this

[MFGF]

Hi MF,

Essentially what I am trying to accomplish is a report that shows all employees who do not have that specific course code in their profile. In this case it would be the 'XXX XXX'

Thanks,
Rob

Thanks.

How about:

[Course Code] <> 'XXX XXX' or [Course Code] is missing

MF.

[rob8143]

Is there a reason I get an error every time I use another operator other than the = sign?

[MFGF]

What is the exact expression you are using, and what does the error say?

MF.

[rob8143]

[Course] not('XXX XXX' or 'YYY YYY')

It is giving me a Parsing Error(QE DEF 0260 before or near position 14)

[MFGF]

Yes - this is not legal syntax.

Try this:

[Course] not in ('XXX','YYY')

Regards,

MF.

[rob8143]

Thanks...that worked.  Now... the Report that I want to generate next is one that will show me a list by Employee that does not have XXX or YYY in their entire profile, as opposed to line by line.

Is that possible?

[MFGF]

Hi,

You could try the following:

Create a list report with Employee number and Course in it, and group by Employee number.

Drag in a Query Calculation from the toolbox (call it Select Flag or something similar) with the following expression:

if ([Course] in ('XXX XXX', 'YYY YYY')) then (1) else (0)

Add a Summary filter with the expression:

total([Select Flag] for [Employee number]) = 0

Make sure you set the scope on the right hand side to be Employee number.

Run the report to make sure you get correct employees listed, then cut (don't delete) the Select Flag column from the list.

MF.