Report: How to display Survey Question/Responses in columns.

[Community Alums]

I am trying to build a report having survey questions/responses in columns. I need the columns to show Question 1, Question 2, Question 3, Question 4, Question 5 and all the answers in a single row. How can I do this?

[drjohnchun]

Below screenshot shows how you might be able to do it using the OOB reporting feature; you'll need to use the Pivot type and add filters to fit your needs.   The table to use is Assessment Instance Question [asmt_assessment_instance_question] (or the legacy Survey Response [survey_response] table, if you're using it).

You may also look into Performance Analytics Pivot Tables.

Another option is to export the data and use Excel to pivot the data, using either the legacy Pivot or Power Pivot.

I don't think you can create a database view for this since you can't turn the questions into columns using the built-in capabilities.

Hope this helps.

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

Winner of November 2016 Members' Choice Award

[Community Alums]

I designed a separate table for each survey because the question #s varies for each survey. I created a business rule for the new survey response records. For existing survey records, created a background script (to copy all response data into new table).

Columns go like this..

Business rule:

==========================================================

(function executeRule(current, previous /*null when async*/) {

      var gr = new GlideRecord('u_company_satisfactory_survey_response');

      gr.addQuery('u_survey_instance', current.sys_id);

      gr.query();

      if (!gr.next()){

              gs.log('-----------------' + gr.u_survey_instance.getDisplayValue());

              gr.initialize();

      }

      gr.u_survey_instance = current.sys_id;

      gr.u_metric_type = current.metric_type;

      gr.u_assigned_to =   current.user;

      gr.u_incident_number = current.trigger_id;

      gr.u_incident_assigned_to = current.related_id_1;

      gr.u_incident_assignment_group = current.related_id_2;

      if (gr.state == 'complete'){

      gs.log('---------------complete');

              var result = new GlideRecord('asmt_metric_result');

              result.addQuery('instance', gr.u_survey_instance);

              result.query();

              while(result.next()){

                      if(result.metric == 'edfeec4f373ea20009dc65e2b3990ed4'){ //1

                              gr.u_question_1 = result.metric;

                              gr.u_string_value_1 = result.string_value;

                              gr.u_normalized_value_1 = result.normalized_value;

                      }

                      if(result.metric == 'a1feec4f373ea20009dc65e2b3990ed5'){ //2

                              gr.u_question_2 = result.metric;

                              gr.u_string_value_2 = result.string_value;

                              gr.u_normalized_value_2 = result.normalized_value;

                      }

                      if(result.metric == 'e1feec4f373ea20009dc65e2b3990ed5'){ //3

                              gr.u_question_3 = result.metric;

                              gr.u_string_value_3 = result.string_value;

                              gr.u_normalized_value_3 = result.normalized_value;

                      }

                      if(result.metric == '25feec4f373ea20009dc65e2b3990ed5'){ //4

                              gr.u_question_4 = result.metric;

                              gr.u_string_value_4 = result.string_value;

                              gr.u_normalized_value_4 = result.normalized_value;

                      }                      

                      if(result.metric == '65feec4f373ea20009dc65e2b3990ed5'){ //5

                              gr.u_question_5 = result.metric;

                              gr.u_string_value_5 = result.string_value;

                              gr.u_normalized_value_5 = result.normalized_value;

                      }

              }

      }

      if (gr.isNewRecord()){

              gr.insert();

      }

      else {

              gr.update();

      }

})(current, previous);

[KB15]

OK. That's what I expected. I was thinking you had some magical script that would build a table out with all the unique questions from each survey. 😃 I appreciate you sharing the code.

[KB15]

Also, which table was this running against? Metric Result?

[mdash]

Hi,

I tried to use the above BR. But in my case, while it is successfully creating a new record in custom table, it does not copy the responses to the individual fields. They just stay empty.