I want to filter the “sc_task” report by whether the assignee is enrolled or not.

[M_Tomy]

Dear Expert

I am trying to use “sc_task” as a data source to report the response performance.
Data Source
・Table          : sc_task
・Condition  : Specify multiple groups of your choice for the assignee group.

Reporting conditions
・Type　        ：Multi-pivot table pivot
・Column        : Assignee
・Rows            : item, status
・Aggregate    : Count

However, if I do as above, retired members who were in the assignee group in the past will appear in the report.
I want the default view of the report to show only members who are in the current group.
However, sometimes I want to view the data including retired members, so I want to control this with an interactive filter if possible.
It would be nice to be able to control the members to be reported based on whether they are currently in the group or not by “ture/false”.

Matters to be discussed
①Please let me know if you have any knowledge that would be helpful for me to know what conditions I should use to create the filter.
② Or if there is another way to do what you want to do above, please let me know.

Best regards in advance.

[Robert H]

Hello @M_Tomy ,

This is only partially feasible.

Please note that information about group membership is only available in the Group Members [sys_user_grmember] table, while your report is built on the Catalog Task table, and there is no way to dot-walk from there to the above table.

What you can do is create a Database View that joins these two tables in the following way:

Here is the where-clause:

member_group=task_assignment_group && member_user=task_assigned_to

The resulting view will only contain Catalog Tasks where both the Assignment group and the Assigned to match an existing record in the Group members table, in other words, when the task assignee is currently a member of the assignment group.

Example: I created these two tasks and assigned them to different members of the Network group. Later I removed David Dan from that group.

This is what the Database View will look like:

So if you create a report on the above Database View instead of the Catalog Task table you can achieve the first part of your requirement.

But having an interactive filter that switches between this list and the full list (including former group members) is not possible. You need two separate reports.

Regards,

Robert