Page tree
Skip to end of metadata
Go to start of metadata

 This report shows each users responses to the survey questions.

 

 

Report NameItem Evaluation By Individual Response (CSV)
REPORT_NAME_IDItemEvaluationByIndividualResponseCSV
REPORT_ID384
CATEGORY_IDLearning
GROUP_IDItemEvaluationByIndividualResponse
WORKFLOW_IDRun ItemEvaluationByIndividualResponse Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENUYes
DMN_IDPUBLIC

SQL Query used

ItemDataSet
SELECT * 
FROM   (SELECT Decode(QS.anonymous, 'Y', 'b', 
                                    'N', 'a')                      SORT_ORDER, 
               Decode(QS.anonymous, 'Y', CONCAT('ANONYMOUS', SS.stud_id), 
                                    'N', SS.stud_id)               STUD_ID, 
               SS.stud_id                                          STUD_ID_ORIG, 
               SS.stud_survey_id, 
               Decode(QS.anonymous, 'Y', CONCAT('ANONYMOUS', CONCAT(S.fname, 
                                                             CONCAT(' ', CONCAT 
                                                             (S.lname, 
                                                                         CONCAT 
                                                             (' ', 
               S.mi))))), 
                                    'N', CONCAT(S.fname, 
                                         CONCAT(' ', CONCAT(S.lname, 
                                                     CONCAT(' ', 
                                         S.mi))))) 
                                                   STUD_NAME, 
               QS.survey_id, 
               SS.survey_completion_date, 
               CS.cpnt_survey_id, 
               CPNT.cpnt_title, 
               SQ.section_id, 
               SQ.question_id, 
               QS.name, 
               CS.cpnt_typ_id, 
               CS.cpnt_id, 
               CS.rev_dte, 
               SSEC.section_order, 
               SSEC.section_title, 
               SSEC.resource_type, 
               SQ.question_order, 
               SQ.question_type, 
               SQ.question_text, 
               SR.stud_response_value, 
               CONCAT(( INSTDETAIL.fname ), 
               CONCAT(' ', CONCAT(( INSTDETAIL.lname ), 
                           CONCAT(' ', ( INSTDETAIL.mi ))))) 
                                      INSTRUCTOR_NAME, 
               Decode(SQ.question_type, 'RATING SCALE', (SELECT 
               pa_rating_scale_label.rating_label 
                                                         FROM 
               pa_rating_scale_label 
                                                         WHERE 
               pa_rating_scale_label.rating = SR.stud_response_value 
               AND pa_rating_scale_label.rating_scale_id = SQ.rating_scale_id), 
                                        'OPEN ENDED', Max(SR.stud_response_text) 
               , 
                                        Max(QAC.choice_label))     ANSWER, 
               (SELECT COUNT(*) AS TotalSurveys 
                FROM   pa_stud_survey pss, 
                       pa_cpnt_survey psc 
                WHERE  psc.cpnt_survey_id = pss.cpnt_survey_id 
                       AND psc.survey_id = QS.survey_id)           TOTAL_SURVEY, 
               (SELECT COUNT(*) AS TotalSurveys 
                FROM   pa_stud_survey pss, 
                       pa_cpnt_survey psc 
                WHERE  psc.cpnt_survey_id = pss.cpnt_survey_id 
                       AND psc.survey_id = QS.survey_id 
                       AND pss.survey_status_id = 'COMPLETED') 
               COMPLETED_SURVEY, 
               (SELECT COUNT(*) AS TotalSurveys 
                FROM   pa_stud_survey pss, 
                       pa_cpnt_survey psc 
                WHERE  psc.cpnt_survey_id = pss.cpnt_survey_id 
                       AND psc.cpnt_survey_id = CS.cpnt_survey_id) 
               TOTAL_ITEM_SURVEY, 
               (SELECT COUNT(*) AS TotalSurveys 
                FROM   pa_stud_survey pss, 
                       pa_cpnt_survey psc 
                WHERE  psc.cpnt_survey_id = pss.cpnt_survey_id 
                       AND psc.cpnt_survey_id = CS.cpnt_survey_id 
                       AND pss.survey_status_id = 'COMPLETED') 
                      COMPLETED_ITEM_SURVEY, 
               SS.sch_id 
               SCHDULE_OFFERING, 
               SR.stud_comments 
               STUDENT_COMMENTS, 
               ( CASE 
                   WHEN (SELECT COUNT(DISTINCT( rating_scale_id )) AS 
                                V_RATING_SETS 
                         FROM   pa_survey_question 
                         WHERE  section_id IN (SELECT section_id 
                                               FROM   pa_survey_section 
                                               WHERE  survey_id = QS.survey_id) 
                                AND question_type = 'RATING SCALE') = 1 THEN ( 
                   SELECT 
Decode(Round(Sum(PSR.stud_response_value) / COUNT(*), 1), NULL, -1, 
Round(Sum( 
PSR.stud_response_value) / COUNT(*), 1)) 
FROM   pa_survey_responses PSR, 
pa_stud_survey PSS, 
pa_cpnt_survey PCS, 
pa_survey_question PSQ, 
pa_ssg_inst PSI, 
pa_ssg_locn PSL 
WHERE  PSS.cpnt_survey_id = PCS.cpnt_survey_id 
AND PSR.stud_survey_id = PSS.stud_survey_id 
AND PSR.question_id = PSQ.question_id 
AND PSS.survey_status_id = 'COMPLETED' 
AND PCS.survey_id = QS.survey_id 
AND PSS.cpnt_survey_id = CS.cpnt_survey_id 
AND PSS.stud_id = SS.stud_id 
AND PSQ.question_type = 'RATING SCALE' 
AND PSR.stud_response_value <> 0 
AND PSS.sch_id = PSI.schd_id(+) 
AND PSS.sch_id = PSL.schd_id(+) 
AND PSS.item_completion_date = (SELECT Min(item_completion_date) 
                            FROM   pa_stud_survey 
                            WHERE 
cpnt_survey_id = PSS.cpnt_survey_id 
AND stud_survey_id = PSS.stud_survey_id) 
/**  and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch]  and PSS.SCH_ID IN [ScheduledOfferingSearch]  and TRUNC(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]  and NOT TRUNC(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]            AND PSI.INST_ID IN [InstructorSearch]  and PSL.LOCN_ID IN [LocationSearch]  and PCS.IS_ACTIVE = [IncludePreviousItemAssociations]  */ 
) 
ELSE -1 
END )                                             AS MEAN_SCORE 
FROM   pa_questionnaire_survey QS 
LEFT OUTER JOIN pa_cpnt_survey CS 
         ON QS.survey_id = CS.survey_id 
RIGHT OUTER JOIN pa_stud_survey SS 
          ON CS.cpnt_survey_id = SS.cpnt_survey_id 
LEFT OUTER JOIN pa_survey_responses SR 
         ON SS.stud_survey_id = SR.stud_survey_id 
LEFT OUTER JOIN pa_survey_question SQ 
         ON SR.question_id = SQ.question_id 
LEFT OUTER JOIN pa_survey_section SSEC 
         ON SQ.section_id = SSEC.section_id 
LEFT OUTER JOIN pa_question_answer_choices QAC 
         ON QAC.question_id = SQ.question_id 
LEFT OUTER JOIN pa_sch_seg SCHEDSEG 
         ON SCHEDSEG.schd_id = SS.sch_id 
LEFT OUTER JOIN pa_ssg_inst INSTRUCTOR 
         ON INSTRUCTOR.schd_id = SCHEDSEG.schd_id 
            AND INSTRUCTOR.ssg_seg_num = SCHEDSEG.ssg_seg_num 
LEFT OUTER JOIN pa_ssg_locn LOCATION 
         ON LOCATION.schd_id = SS.sch_id 
LEFT OUTER JOIN pa_inst INSTDETAIL 
         ON INSTDETAIL.inst_id = SR.inst_id, 
pa_student S, 
pv_course CPNT 
WHERE  ( QAC.answer_id = SR.stud_response_value 
OR SR.stud_response_value IS NULL 
OR SQ.question_type = 'RATING SCALE' ) 
AND SQ.section_id IS NOT NULL 
AND SS.survey_status_id = 'COMPLETED' 
AND ( SS.stud_id = NULL 
   OR SS.stud_id = S.stud_id ) 
AND CS.cpnt_typ_id = CPNT.cpnt_typ_id 
AND CS.cpnt_id = CPNT.cpnt_id 
AND CS.rev_dte = CPNT.rev_dte 
AND QS.level_indicator = 1 
/**  and QS.SURVEY_ID in [SurveyIDSearch]  and SS.STUD_ID in [UserSearch]  and trunc(SS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]  and NOT trunc(SS.ITEM_COMPLETION_DATE) > [LearningEventToDate]  and SS.SCH_ID in [ScheduledOfferingSearch]      and INSTRUCTOR.INST_ID IN [InstructorSearch]  AND LOCATION.LOCN_ID in [LocationSearch]  and QS.IS_ACTIVE  = [QuestionnaireSurveyStatus]  and CS.IS_ACTIVE  = [IncludePreviousItemAssociations]  and (CS.cpnt_typ_id,CS.cpnt_id,CS.rev_dte) in [ItemSearch]  and [security:PA_QUESTIONNAIRE_SURVEY QS]  */ 
GROUP  BY Decode(QS.anonymous, 'Y', 'b', 
                    'N', 'a'), 
Decode(QS.anonymous, 'Y', CONCAT('ANONYMOUS', SS.stud_id), 
                    'N', SS.stud_id), 
SS.stud_id, 
SS.stud_survey_id, 
Decode(QS.anonymous, 'Y', CONCAT('ANONYMOUS', CONCAT(S.fname, CONCAT( 
                                             ' ', 
                         CONCAT 
                                    (S.lname, 
                                                CONCAT 
                                    (' ', 
S.mi))))), 
                    'N', CONCAT(S.fname, CONCAT(' ', CONCAT(S.lname, 
                                                     CONCAT(' ', 
                                                     S.mi))))), 
QS.survey_id, 
SS.survey_completion_date, 
CS.cpnt_survey_id, 
CPNT.cpnt_title, 
SQ.section_id, 
SQ.question_id, 
QS.name, 
CS.cpnt_typ_id, 
CS.cpnt_id, 
CS.rev_dte, 
SSEC.section_order, 
SSEC.section_title, 
SSEC.resource_type, 
SQ.question_order, 
SQ.question_type, 
SQ.question_text, 
SR.stud_response_text, 
SR.stud_response_value, 
SQ.rating_scale_id, 
SS.sch_id, 
SR.stud_comments, 
INSTDETAIL.fname, 
INSTDETAIL.lname, 
INSTDETAIL.mi) master, 
(SELECT DISTINCT SR1.stud_comments, 
     ss.cpnt_survey_id, 
     sr1.question_id, 
     ss.stud_id 
FROM   pa_survey_responses SR1, 
pa_stud_survey SS 
WHERE  SR1.stud_survey_id = SS.stud_survey_id 
AND SR1.stud_comments IS NOT NULL) student_comments 
WHERE  student_comments.cpnt_survey_id(+) = master.cpnt_survey_id 
       AND student_comments.question_id(+) = master.question_id 
       AND student_comments.stud_id(+) = master.stud_id 
/** and master.STUD_ID in [UserSearch] */ 
ORDER  BY master.stud_id, 
          master.cpnt_survey_id, 
          question_order 

 

Link: http://wiki.scn.sap.com/wiki/x/j4ERG

 

  • No labels