Skip to Content

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:

Add a comment
10|10000 characters needed characters exceeded

Related questions

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
    10|10000 characters needed characters exceeded

  • 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
    10|10000 characters needed characters exceeded

  • Posted on Oct 21, 2019 at 11:25 AM

    Jorge Campos

    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:

    aggregation.png

    aggregation-1.png



    aggregation.png (24.9 kB)
    aggregation-1.png (25.5 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • 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 desc
    Add a comment
    10|10000 characters needed characters exceeded

  • 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
    10|10000 characters needed characters exceeded