This question was closed Oct 31, 2019 at 02:33 PM by Michael Matveev for the following reason: The question is answered, right answer was accepted
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:
5 Answers
-
Posted on Oct 17, 2019 at 04:31 PM
Have you either previewed the query in PRD or run the report without the aggregation to see what data is being aggregated? I gave your SQL a glance and suspect the use of first_value() may be return underlying data that is not what you expect.
Add a comment
-
Posted on Oct 17, 2019 at 07:58 PM
Check the third query of the union. The aggregation filter condition is completion date is not null. The first 2 queries of the union have a where clause of SC.COMPL_DTE is null which means only the third query would generate data applicable to the aggregation.
I advise that you review your outer joins. Some seem backwards to me. For example, "left join PA_INST I on I.INST_ID = SR.INST_ID" where SR is PS_SCHD_RESOURCES only gives query results where the scheduled offering resources exists in the instructor table because the left outer joins is get instructors whether or not there is match in scheduled offering resources. I think you want left join PA_INST I on SR.INST_ID = I.INST_ID so that the query results has scheduled offering data whether or not there is match in the instructors.
I do not see a query column with the name "Статус" as shown in the expression of the aggregation properties. What LAST are you trying to get? How is the group ID defined?
Add a comment
-
Posted on Oct 21, 2019 at 11:25 AM
Hello, Jorge.
I was running the report without aggregation. The correct data was export .
To make sure the error is not due to incorrect Query Text, I created a new report with one table. The report displays 4 fields, 2 of them are aggregated. And in this simple report the same error is repeated.
Please see new Query Text of Data Set:
SELECT
sc.stud_id as "Идентификатор пользователя",
sc.CPNT_ID as "Идентификатор предмета",
sc.COMPL_DTE as "Дата прохождения",
sc.CMPL_STAT_ID as "Пройден"
FROM PA_CPNT_EVTHST sc
/** where and sc.STUD_ID in [UserSearch]
and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch]
and [security: PA_STUDENT s]
*/
Please see Aggregation and Aggregation_1:
Add a comment
-
Posted on Oct 21, 2019 at 06:06 PM
Michael,
I suggest adding columns from PS_SCHD_RESOURCES and PA_CBT_STUD_CPNT to the query you are using to check the underlying data.
Try this query.
select SC.STUD_ID, UC.USER_VALUE as "LDAP", S.LNAME || ' ' || S.FNAME || ' ' || S.MI as USER_NAME, SC.CPNT_TYP_ID, SC.CPNT_ID, SC.REV_DTE, SC.COMPL_DTE, SC.CMPL_STAT_ID, SC.SCHD_ID, SS.START_DTE, SS.END_DTE, G.TOTAL_TIMES from PA_CPNT_EVTHST SC left join PA_STUDENT S on SC.STUD_ID = S.STUD_ID -- sub-query instead of in-line query. Faster in my experience. left join ( select UC1.STUD_ID, UC1.USER_VALUE from PA_STUD_USER UC1 where UC1.COL_NUM = 110 ) UC on S.STUD_ID = UC.STUD_ID -- sub-query to get the earliest offering start date and latest offering end -- date instead of analytics functions left outer join ( select SS1.SCHD_ID, min(SS1.START_DTE) as START_DTE, max(SS1.END_DTE) as END_DTE from PA_SCH_SEG SS1 group by SS1.SCHD_ID ) SS on SC.SCHD_ID = SS.SCHD_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 and -- matching online tracking completion date to the event completion date gives -- the TOTAL_TIMES for the completion. Using FIRST_VALUE analytics function -- with order by CREATE_DATE descending the most recent created which may not -- "pair" to the event correctly. SC.COMPL_DTE = G.COMPL_DTE where 1 = 1 /** and SC.STUD_ID in [UserSearch] and (SC.CPNT_TYP_ID, SC.CPNT_ID, SC.REV_DTE) in [ItemSearch] and [security: PA_STUDENT S] */ order by SC.STUD_ID, SC.CPNT_TYP_ID, SC.CPNT_ID, SC.REV_DTE, SC.COMPL_DTE descAdd a comment
-
Posted on Oct 21, 2019 at 06:11 PM
Have you previewed the query results or run the report with a hard-wired "(SC.CPNT_TYP_ID, SC.CPNT_ID, SC.REV_DTE) in (<item tuple>)"? I think a report without aggregation columns the includes scheduled offering data and online tracking data and the hard-wired where clause for items can help reveal what is causing the discrepency.
Add a comment
Add a comment