How to calculate average survey rating per assessment

[SwathiBanda]

I’ve created a survey in ServiceNow for Incidents with 5 questions:

• Questions 1–4 are rating type (scale 1 to 5).

• Question 5 is free-text (comments).

What I want is:

• Calculate the average of Questions 1–4,

• Also include the free-text comment from Question 5.

For example:

Survey Instance Q1 Q2 Q3 Q4 Avg Score Comments

SURV001	5	4	5	4	4.5	"Resolved quickly"
SURV002	2	3	2	2	2.25	"Took too long"

I understand the data is stored in asmt_metric_result (individual answers) and asmt_assessment_instance (survey instances), but I’m not sure how to:

1. Combine the responses so each survey instance is a single row,

2. Calculate the average across just the first 4 rating questions,

3. Include the comment (Q5) in the same row.

Has anyone implemented this before?

[Rafael Batistot]

Hi @SwathiBanda 

Reporting/UI (No-Code Option)

You can use a Performance Analytics / Report Source approach:

• Create a database view joining asmt_assessment_instance to asmt_metric_result.
• Filter by your survey.
• Use GROUPBY on the instance.
• Use aggregates for Q1–Q4 (AVG), and max/text aggregate for Q5 (the comment).

But the DB View needs you to know the metric definitions (questions) because they’re just rows in asmt_metric_result.

[Pradeep Thipani]

Hi @SwathiBanda ,

Create a database view between both tables and build a report on top of it. This way, you can retrieve the complete information available in both tables. Follow the steps below to join the two tables:

https://www.servicenow.com/docs/bundle/xanadu-platform-administration/page/use/reporting/task/t_Crea...

https://medium.com/@saurabhdubey836/database-views-the-tale-of-joining-two-tables-8f16a5771b71

Thanks,

Pradeep

[Bhuvan]

@SwathiBanda 

To display the assessment results grouped by survey, you can create a data visualization or report on 

'asmt_assessment_instance_question' table filtered by category

If you want to get an average of 4 questions and answer from 5th question, there are couple of options,

1. Create a new field in asmt_assessment_instance table and create a Business Rule to calculate the average value of 4 questions using gliderecord and store it. In report/data visualization, you can use the aggregated field and question 5 field to show the results

2. Create a database view table with correct where clause to merge tables and create a new field on the view table that calculates the average of 4 questions. Create a report or data visualization on database view table and show the results

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan