Hello, Experts.
Please tell me, whether the ItemSearch parameter affects field aggregation (in my the custom report, aggregation displays the last value) in PRD reports for LMS? The problem is that when I use the ItemSearch filter when export the report, aggregation starts to work incorrectly and displays not the last value, but the first.
Please see Query Text of Data Set:
SELECT distinct
sc.stud_id as "Идентификатор пользователя",
(Select USER_VALUE from PA_STUD_USER where s.STUD_ID = stud_ID and COL_NUM = 110) as "LDAP",
s.LNAME ||' '|| s.FNAME ||' '||s.MI as "Сотрудник",
sc.AP_ID as "Профиль назначения",
s.JOB_TITLE as "Должность",
y.REGULAR_TEMP_DESC as "Отдел",
d.JL_DESC as "Магазин",
s.REGION_ID as "Регион",
sc.CPNT_ID as "Идентификатор предмета",
c.CPNT_TITLE as "Название предмета",
null as "Учебная программа",
c.CPNT_CLASSIFICATION as "Тип",
to_char((first_value(sr.START_DTE) OVER (PARTITION BY sc.SCHD_ID ORDER BY sr.START_DTE DESC) + interval 3 hour),'DD.MM.YYYY') as "Дата начала",
to_char((first_value(sr.END_DTE) OVER (PARTITION BY sc.SCHD_ID ORDER BY sr.END_DTE DESC) + interval 3 hour),'DD.MM.YYYY') as "Дата окончания",
NULL as "Дата прохождения",
sc.ASSGN_DTE as "Назначен",
NULL as "Пройден",
i.LNAME ||' '|| i.FNAME ||' '||i.MI as "ФИО тренера",
c.CPNT_LEN as "Время",
null as "Балл",
first_value(g.TOTAL_TIMES) OVER (PARTITION BY sc.CPNT_TYP_ID, sc.CPNT_ID, sc.STUD_ID, sc.REV_DTE ORDER BY g.CREATE_DTE DESC) as "Количество запусков"
FROM PV_STUD_COURSE sc
LEFT JOIN PV_COURSE c ON sc.CPNT_TYP_ID = c.CPNT_TYP_ID and sc.CPNT_ID = c.CPNT_ID and sc.REV_DTE = c.REV_DTE
LEFT JOIN PA_STUDENT s ON sc.STUD_ID = s.STUD_ID
LEFT JOIN PA_JOB_LOC d on d.JL_ID = s.JL_ID
LEFT JOIN PA_REGULAR_TEMP y on y.REGULAR_TEMP_ID = s.REGULAR_TEMP_ID
LEFT JOIN PS_SCHD_RESOURCES sr ON sr.SCHD_ID = sc.SCHD_ID
LEFT JOIN PA_INST i ON i.INST_ID = sr.INST_ID
LEFT JOIN PA_CBT_STUD_CPNT g ON sc.STUD_ID = g.STUD_ID and sc.CPNT_TYP_ID = g.CPNT_TYP_ID and sc.CPNT_ID = g.CPNT_ID and sc.REV_DTE = g.REV_DTE
LEFT JOIN PA_CPNT_SUBJ sj ON sj.CPNT_ID = sc.CPNT_ID and sj.CPNT_TYP_ID = sc.CPNT_TYP_ID
Where sc.COMPL_DTE is null
/**and s.STUD_ID in [UserSearch]
and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch]
and s.REGION_ID in [RegionSearch]
and [security: PA_STUDENT s, PV_COURSE c, PA_CPNT_EVTHST sc]
*/
UNION
SELECT distinct
s.stud_id as "Идентификатор пользователя",
(Select USER_VALUE from PA_STUD_USER where sc.STUD_ID = stud_ID and COL_NUM = 110) as "LDAP",
s.LNAME ||' '|| s.FNAME ||' '||s.MI as "Сотрудник",
stc.AP_ID as "Профиль назначения",
s.JOB_TITLE as "Должность",
y.REGULAR_TEMP_DESC as "Отдел",
d.JL_DESC as "Магазин",
s.REGION_ID as "Регион",
sc.CPNT_ID as "Идентификатор предмета",
c.CPNT_TITLE as "Название предмета",
cq.QUAL_TITLE as "Учебная программа",
c.CPNT_CLASSIFICATION as "Тип",
to_char((first_value(sr.START_DTE) OVER (PARTITION BY stc.SCHD_ID ORDER BY sr.START_DTE DESC) + interval 3 hour),'DD.MM.YYYY') as "Дата начала",
to_char((first_value(sr.END_DTE) OVER (PARTITION BY stc.SCHD_ID ORDER BY sr.END_DTE DESC) + interval 3 hour),'DD.MM.YYYY') as "Дата окончания",
NULL as "Дата прохождения",
sc.ASSGN_DTE as "Назначен",
NULL as "Пройден",
i.LNAME ||' '|| i.FNAME ||' '||i.MI as "ФИО тренера",
c.CPNT_LEN as "Время",
null as "Балл",
first_value(g.TOTAL_TIMES) OVER (PARTITION BY sc.CPNT_TYP_ID, sc.CPNT_ID, sc.STUD_ID, sc.REV_DTE ORDER BY g.CREATE_DTE DESC) as "Количество запусков"
FROM PA_STUD_QUAL_CPNT sc
LEFT JOIN PV_COURSE c on sc.CPNT_TYP_ID = c.CPNT_TYP_ID and sc.CPNT_ID = c.CPNT_ID and sc.REV_DTE = c.REV_DTE
LEFT JOIN PA_STUDENT s ON sc.STUD_ID = s.STUD_ID
LEFT JOIN PA_JOB_LOC d on d.JL_ID = s.JL_ID
LEFT JOIN PA_REGULAR_TEMP y on y.REGULAR_TEMP_ID = s.REGULAR_TEMP_ID
LEFT JOIN PV_STUD_COURSE stc on stc.CPNT_TYP_ID = sc.CPNT_TYP_ID and stc.CPNT_ID = sc.CPNT_ID and stc.REV_DTE = sc.REV_DTE and stc.COMPL_DTE = sc.COMPL_DTE and stc.STUD_ID = sc.STUD_ID
LEFT JOIN PS_SCHD_RESOURCES sr ON sr.SCHD_ID = stc.SCHD_ID
LEFT JOIN PA_QUAL cq ON cq.QUAL_ID = sc.QUAL_ID
LEFT JOIN PA_INST i ON i.INST_ID = sr.INST_ID
LEFT JOIN PA_CBT_STUD_CPNT g ON sc.STUD_ID = g.STUD_ID and sc.CPNT_TYP_ID = g.CPNT_TYP_ID and sc.CPNT_ID = g.CPNT_ID and sc.REV_DTE = g.REV_DTE
LEFT JOIN PA_CPNT_SUBJ sj ON sj.CPNT_ID = sc.CPNT_ID and sj.CPNT_TYP_ID = sc.CPNT_TYP_ID
Where sc.COMPL_DTE is null
/**and s.STUD_ID in [UserSearch]
and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch]
and s.REGION_ID in [RegionSearch]
and [security: PA_STUDENT s, PV_COURSE c, PA_CPNT_EVTHST sc]
*/
UNION
SELECT distinct
sc.stud_id as "Идентификатор пользователя",
(Select USER_VALUE from PA_STUD_USER where s.STUD_ID = stud_ID and COL_NUM = 110) as "LDAP",
s.LNAME ||' '|| s.FNAME ||' '||s.MI as "Сотрудник",
Null as "Профиль назначения",
s.JOB_TITLE as "Должность",
y.REGULAR_TEMP_DESC as "Отдел",
d.JL_DESC as "Магазин",
s.REGION_ID as "Регион",
sc.CPNT_ID as "Идентификатор предмета",
c.CPNT_TITLE as "Название предмета",
cq.QUAL_TITLE "Учебная программа",
c.CPNT_CLASSIFICATION as "Тип",
to_char((first_value(sr.START_DTE) OVER (PARTITION BY sc.SCHD_ID ORDER BY sr.START_DTE DESC) + interval 3 hour),'DD.MM.YYYY') as "Дата начала",
to_char((first_value(sr.END_DTE) OVER (PARTITION BY sc.SCHD_ID ORDER BY sr.END_DTE DESC) + interval 3 hour),'DD.MM.YYYY') as "Дата окончания",
sc.COMPL_DTE as "Дата прохождения",
null as "Назначен",
sc.CMPL_STAT_ID as "Пройден",
i.LNAME ||' '|| i.FNAME ||' '||i.MI as "ФИО тренера",
c.CPNT_LEN as "Время",
sc.GRADE as "Балл",
first_value(g.TOTAL_TIMES) OVER (PARTITION BY sc.CPNT_TYP_ID, sc.CPNT_ID, sc.STUD_ID, sc.REV_DTE ORDER BY g.CREATE_DTE DESC) as "Количество запусков"
FROM PA_CPNT_EVTHST sc
LEFT JOIN PV_COURSE c on sc.CPNT_TYP_ID = c.CPNT_TYP_ID and sc.CPNT_ID = c.CPNT_ID and sc.REV_DTE = c.REV_DTE
LEFT JOIN PA_STUDENT s ON sc.STUD_ID = s.STUD_ID
LEFT JOIN PA_JOB_LOC d on d.JL_ID = s.JL_ID
LEFT JOIN PA_REGULAR_TEMP y on y.REGULAR_TEMP_ID = s.REGULAR_TEMP_ID
LEFT JOIN PV_STUD_COURSE stc on stc.CPNT_TYP_ID = sc.CPNT_TYP_ID and stc.CPNT_ID = sc.CPNT_ID and stc.REV_DTE = sc.REV_DTE and stc.COMPL_DTE = sc.COMPL_DTE and stc.STUD_ID = sc.STUD_ID
LEFT JOIN PS_SCHD_RESOURCES sr ON sr.SCHD_ID = sc.SCHD_ID
LEFT JOIN PA_STUD_QUAL_CPNT sqc on sqc.CPNT_TYP_ID = sc.CPNT_TYP_ID and sqc.CPNT_ID = sc.CPNT_ID and sqc.REV_DTE = sc.REV_DTE and sqc.STUD_ID = sc.STUD_ID and sqc.COMPL_DTE = sc.COMPL_DTE
LEFT JOIN PA_QUAL cq ON cq.QUAL_ID = sqc.QUAL_ID
LEFT JOIN PA_INST i ON i.INST_ID = sr.INST_ID
LEFT JOIN PA_CBT_STUD_CPNT g ON sc.STUD_ID = g.STUD_ID and sc.CPNT_TYP_ID = g.CPNT_TYP_ID and sc.CPNT_ID = g.CPNT_ID and sc.REV_DTE = g.REV_DTE
LEFT JOIN PA_CPNT_SUBJ sj ON sj.CPNT_ID = sc.CPNT_ID and sj.CPNT_TYP_ID = sc.CPNT_TYP_ID
/** where s.STUD_ID in [UserSearch]
and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch]
and s.REGION_ID in [RegionSearch]
and [security: PA_STUDENT s, PV_COURSE c, PA_CPNT_EVTHST sc]
*/
Please see Aggregation: