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?
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
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.
Use the name of the data item you created for the case statement.
I got it to work, thanks... I forgot to Group the Employee.