Hi,

Would be a massive help if someone could help me with creating a custom report builder source.

I am trying to turn the following data into a report builder :

- For all users in ( one or more ) audiences, I need to get the MAX of their prog completions completion date and the minimum of their next due data.

Here is the raw SQL that I think gives me what I want :

SELECT co.name,u.email, FROM_UNIXTIME(MAX(pc.timecompleted)) AS 'Completion Date' FROM mdl_prog p JOIN mdl_prog_completion pc ON pc.programid = p.id AND p.certifid IS NOT NULL JOIN mdl_cohort_members cm ON cm.userid = pc.userid JOIN mdl_cohort co ON co.id = cm.cohortid JOIN mdl_user u ON u.id = cm.userid GROUP BY u.id,p.id

which gives me the following joins with 'prog' being my base source

- prog_completion

- cohort_members

- cohort

- users ( or ausers in the reportbuilder)

Although I get the right data in my report my total number of records are just "2 records"

I believe this is due to the fact that I have my GROUP BY in my source where "GROUP BY prog_completion.id,cohort.id"

Happy to share more snippets of my source class if needed.

Working with Totara 12.

Thanks

Hittesh