In the custom PRD report the ItemSearch parameter affects field aggregation

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: