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
REPORT_NAME_IDItemEvaluationByIndividualResponse
REPORT_ID121
CATEGORY_IDLearning
GROUP_IDItemEvaluationByIndividualResponse
WORKFLOW_IDRun ItemEvaluationByIndividualResponse Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENUYes

 

SQL Query used

MeanScoreForItemSection
SELECT ( 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 = ?) 
                        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 = ? 
AND PSS.cpnt_survey_id = ? 
AND PSS.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   dual 
MeanScoreForSurveySection
SELECT ( 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 = ? 
                                              AND section_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 = ? 
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 PSQ.section_id = ? 
AND PSS.stud_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   dual 
QuestionComments
SELECT DISTINCT SR1.stud_comments, 
                Concat(( INSTDETAIL.fname ), Concat(' ', Concat(( 
                                             INSTDETAIL.lname ), 
                                                         Concat(' ', ( 
                                                         INSTDETAIL.mi ))))) 
                COMMENTS_INSTRUCTOR_NAME 
FROM   pa_survey_responses SR1, 
       pa_stud_survey SS, 
       pa_inst INSTDETAIL 
WHERE  SR1.stud_survey_id = SS.stud_survey_id 
       AND SR1.stud_comments IS NOT NULL 
       AND SS.cpnt_survey_id = ? 
       AND SR1.question_id = ? 
       AND INSTDETAIL.inst_id(+) = SR1.inst_id 
       AND Concat('ANONYMOUS', SS.stud_id) = ? 
SurveyByItem
SELECT ?                                                   AS fromDate, 
       ?                                                   AS toDate, 
       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, 
       Decode(SQ.question_type, 'RATING SCALE', (SELECT 
       CONCAT(CONCAT(pa_rating_scale_label.rating, ' - '), al.label_value) 
                                                 FROM   pa_rating_scale_label, 
       pv_i18n_active_locale_label al 
                                                 WHERE 
       pa_rating_scale_label.rating = SR.stud_response_value 
       AND pa_rating_scale_label.rating_scale_id = SQ.rating_scale_id 
       AND al.label_id (+) = pa_rating_scale_label.rating_label 
       AND al.locale_id (+) = ?), 
                                'OPEN ENDED', Max(SR.stud_response_text), 
                                Max(QAC.choice_label))     ANSWER, 
       CONCAT(( INSTDETAIL.fname ), CONCAT(' ', CONCAT(( INSTDETAIL.lname ), 
                                                CONCAT(' ', 
       ( INSTDETAIL.mi ))))) 
                                                           INSTRUCTOR_NAME, 
       (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 
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_ssg_inst INSTRUCTOR 
                    ON INSTRUCTOR.schd_id = SS.sch_id 
       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_QUESTION_ANSWER_CHOICES QAC, 
       pa_student S, 
       pv_course CPNT--, 
--PA_SSG_INST INSTRUCTOR, 
--PA_SSG_LOCN LOCATION       
--AND ( ( SQ.QUESTION_TYPE = 'MULTIPLE CHOICE' OR SQ.QUESTION_TYPE = 'ONE CHOICE' ) AND ( QAC.ANSWER_ID = SR.STUD_RESPONSE_VALUE OR QAC.ANSWER_ID IS NULL OR SR.STUD_RESPONSE_VALUE IS NULL))
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 ( SS.SCH_ID = INSTRUCTOR.SCHD_ID OR SS.SCH_ID IS NULL ) 
--AND ( SS.SCH_ID = LOCATION.SCHD_ID OR SS.SCH_ID IS NULL )  
/**         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 

 

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

 

 

  • No labels