question Re: How to calculate average survey rating per assessment in Developer forum

How to calculate average survey rating per assessment

SwathiBanda — Fri, 05 Sep 2025 00:34:00 GMT

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:

Combine the responses so each survey instance is a single row,
Calculate the average across just the first 4 rating questions,
Include the comment (Q5) in the same row.

Has anyone implemented this before?

Re: How to calculate average survey rating per assessment

Rafael Batistot — Fri, 05 Sep 2025 01:31:57 GMT

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.

Re: How to calculate average survey rating per assessment

Pradeep Thipani — Fri, 05 Sep 2025 02:04:18 GMT

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_CreateADatabaseView.html

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

Thanks,

Pradeep

Re: How to calculate average survey rating per assessment

Bhuvan — Fri, 05 Sep 2025 02:21:29 GMT

@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

Re: How to calculate average survey rating per assessment

SwathiBanda — Sat, 06 Sep 2025 17:43:10 GMT

I was able to create the database view by joining asmt_assessment_instance with multiple aliases of asmt_metric_result for each survey question (Q1–Q5 and Comments). That part works fine.

where should I create the new “Average” field?

When I open the database view itself, I only see the View Tables related list (to define joins). I don’t see an option to “create a new field” directly on the database view table.

Re: How to calculate average survey rating per assessment

SwathiBanda — Sat, 06 Sep 2025 17:54:46 GMT

I was able to create the database view by joining asmt_assessment_instance with multiple aliases of asmt_metric_result for each survey question (Q1–Q5 and Comments). That part works fine.

where should I create the new “Average” field?

When I open the database view itself, I only see the View Tables related list (to define joins). I don’t see an option to “create a new field” directly on the database view table.

Re: How to calculate average survey rating per assessment

Bhuvan — Sat, 06 Sep 2025 23:26:40 GMT

@SwathiBanda

Go to System Definition -> Database Views and open the database view created

Configure -> Table

Add a new field for database view table similar to creation of new field for table

As per community guidelines, you can accept more than one answer as accepted solution. If my response helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

Re: How to calculate average survey rating per assessment

Bhuvan — Sun, 07 Sep 2025 01:04:00 GMT

@SwathiBanda

For your requirement, create a new field in 'asmt_assessment_instance' table and create a Business Rule that would calculate sum of values of 'asmt_assessment_instance_question' using relation asmt_instance.number = asmt_instance_question.instance

For example, below is an assessment instance and has 6 question as part of assessment survey,

To calculate the sum of values, you can create a BR like below and set value of new field with the count and in turn use it in view tables for reports and dashboards

var count=0; var je = new GlideRecord('asmt_assessment_instance_question'); je.addQuery("instance",'982a2c44d7211100158ba6859e6103a4'); je.query(); while (je.next()) { count+=je.value; } gs.print(count);

If you want to filter the assessment values and calculate count for specific questions only, add a query to exclude. Below is a sample,

This is only for reference as you can do similar function using calculated fields and GlideAggregate using SUM aggregation.

As per community guidelines, you can accept more than one answer as accepted solution. If my response helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

Re: How to calculate average survey rating per assessment

Bhuvan — Mon, 08 Sep 2025 05:30:49 GMT

@SwathiBanda

Did you get a chance to review this ?

As per community guidelines, you can accept more than one answer as accepted solution. If my responses helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

Re: How to calculate average survey rating per assessment

SwathiBanda — Mon, 08 Sep 2025 13:01:52 GMT

Thank you!

Re: How to calculate average survey rating per assessment

Bhuvan — Mon, 08 Sep 2025 13:17:51 GMT

@SwathiBanda

Glad to know it helped you !

Thanks,

Bhuvan