I use M3.6.2 anl latest CRVersion.
I need some help:
I have:
---SELECT
u.id,
u.lastname AS Nachname,
u.firstname AS Vorname,
u.city AS ORT,
u.institution AS Einrichtung,
u.department AS Abteilung,
c.fullname AS Kursname,
u.alternatename AS 'Berufsbez.',
DATE_FORMAT(FROM_UNIXTIME( cc.timecompleted),'%d.%m.%y' ) AS Abschlussdatum,
case when cc.timecompleted > 0 then 'Fertig' else 'Offen' end AS Kursabschluss,
case when u.lastaccess = 0 then '' else DATE_FORMAT(FROM_UNIXTIME( u.lastaccess),'%d.%m.%y') end as zuletzt_online
FROM
(((prefix_user AS u INNER JOIN prefix_user_enrolments AS ue ON u.id = ue.userid)
INNER JOIN prefix_enrol AS enr ON ue.enrolid = enr.id)
INNER JOIN prefix_course AS c ON enr.courseid = c.id)
INNER JOIN prefix_course_completions AS cc ON (ue.userid = cc.userid)
AND (enr.courseid = cc.course)
WHERE u.deleted=0 AND not(instr(firstname,"Leiter"))
AND c.visible = 1 AND c.category = %%CATEGORYID%%
AND u.city = (SELECT prefix_user.city FROM prefix_user WHERE prefix_user.id = %%USERID%%)
AND if((SELECT prefix_user.department FROM prefix_user WHERE prefix_user.id = %%USERID%%) != 'Gesamtleitung', u.department = (SELECT prefix_user.department FROM prefix_user WHERE prefix_user.id = %%USERID%%),'1 = 1')
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:u.department or u.alternatename%%
ORDER BY u.lastname, u.firstname, c.fullname---
and respective Filter in my Filter-adjustment:
when I select to see a report, filters 2 and 3 do not work.
When I use upper two filters only sql (Report) works fine.
What am i doing wrong?
Rolf