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
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
