There is no filter for cohorts in Custom Reports.
This is needed so that we can generate a report for someone who is managing a cohort.
Is it possible to add this filter option?
I am not sure if the following will work as I did not try it, but please try it and let us know.
for information:
Filters
Filters add a control to the report that allows the user to select a subset of data when the report runs. Filters require two steps:
Select the filter you want to use on the "Filters" tab
Add SQL code to make the filter affect the output.
These are the filters that can be used to constrain your report:
%%FILTER_CATEGORIES: %%FILTER_COURSEMODULEID: %%FILTER_COURSEMODULEFIELDS: %%FILTER_COURSEMODULE: %%FILTER_COURSES: %%FILTER_COURSEENROLLEDSTUDENTS: %%FILTER_USERS: %%FILTER_ROLE: %%FILTER_SEARCHTEXT: %%FILTER_SEMESTER: %%FILTER_STARTTIME: %%FILTER_ENDTIME: %%FILTER_SUBCATEGORIES: %%FILTER_COURSEUSER: %%FILTER_SYSTEMUSER: %%FILTER_YEARHEBREW: %%FILTER_YEARNUMERIC:
Try the following, please
SELECT
u.id AS userid,
u.firstname,
u.lastname,
c.name AS cohort_name
FROM prefix_user u
JOIN prefix_cohort_members cm ON u.id = cm.userid
JOIN prefix_cohort c ON cm.cohortid = c.id
WHERE 1=1
%%FILTER_USERS:c.name%%
ORDER BY cohort_name, lastname, firstname
for information:
Filters
Filters add a control to the report that allows the user to select a subset of data when the report runs. Filters require two steps:
Select the filter you want to use on the "Filters" tab
Add SQL code to make the filter affect the output.
These are the filters that can be used to constrain your report:
%%FILTER_CATEGORIES: %%FILTER_COURSEMODULEID: %%FILTER_COURSEMODULEFIELDS: %%FILTER_COURSEMODULE: %%FILTER_COURSES: %%FILTER_COURSEENROLLEDSTUDENTS: %%FILTER_USERS: %%FILTER_ROLE: %%FILTER_SEARCHTEXT: %%FILTER_SEMESTER: %%FILTER_STARTTIME: %%FILTER_ENDTIME: %%FILTER_SUBCATEGORIES: %%FILTER_COURSEUSER: %%FILTER_SYSTEMUSER: %%FILTER_YEARHEBREW: %%FILTER_YEARNUMERIC:
Try the following, please
SELECT
u.id AS userid,
u.firstname,
u.lastname,
c.name AS cohort_name
FROM prefix_user u
JOIN prefix_cohort_members cm ON u.id = cm.userid
JOIN prefix_cohort c ON cm.cohortid = c.id
WHERE 1=1
%%FILTER_USERS:c.name%%
ORDER BY cohort_name, lastname, firstname
