Detailed Report Filter

[jmills]

I've created a list report which shows employees, and the licenses/certifications they have.  I'd like to filter the report to allow a user to show employees who either have a certain license, don't have, or both.

The current functionality of the License filter, only focuses on the Row within the list that includes/excludes the filtered license.  What I'd like it to do is include/exclude the employee from the list, rather than just the detailed row.

How to I set the focus to include/exclude an employee row, rather than just the licenses within each employee?  is this a master/detail relationship where the employee is the master?

[CognosPaul]

Try this:

Create a list with two fields, employee and license. Group the employee. In the query create a new data item with the expression:

case when [License] in (#promptmany('License','string')#) then 1 else 0 end

Set the aggregation to sum and drag that into the summary filter. Use the expression:
[Data item1] > 0
Set the scope of the filter to employee.

When you run it, the report will show every employee that has that license, with all of their licenses.

If I understand your other requirement, you want a list of all employees that don't have the selected licenses.

Change the summary filter to:
[Data item1] #prompt('Operator','token')# 0

Now create a value prompt for the Operator param:
Use: >
Display: Show all employees that have license

Use: =
Display: Show all employees that lack license

[jmills]

Thanks for the suggestion, however i'm running into the following error:

QE-DEF-0359 The query contains a reference to at least one object '[Data item1]' that does not exist.

not sure why i'm getting this... any thoughts?  Thanks.

[CognosPaul]

Use the name of the data item you created for the case statement.

[jmills]

I got it to work, thanks... I forgot to Group the Employee.