Reporting on surveys - how to obtain an average score

scottatah · ‎07-03-2014

Hello again,

We just implemented Survey's into production and I'm now attempting to build some reports with the data. I've put together a quick dashboard showing:

Customer comments regarding, how IT can improve.
Low scores on questions
Surveys requested vs completed last 90 days
Count of survey scores last 30 days for each of my question

Management will want to know the average satisfaction rate across the board, and while I can determine that manually with a few steps, I'd prefer a chart. My survey consists of:

7 Select Box questions
- Text: Completely satisfied Value: 7
- Text: Satisfied Value: 6
- Text: Somewhat satisfied Value: 5
- ...
- Completely dissatisfied Value: 1
1 MultiLine Text question.

I'd like to be able to report on the average score of each question and possibly the average score of all questions (excluding the multi-line) combined.

When browsing the reporting options, the common field for reporting averages seems to be Answer_integer. The wiki, Legacy:Best Practice - Task Survey Management - ServiceNow Wiki, suggests the Answer_integer is for reporting on number response values, and Answer is for string response values. Looking at the survey_response table I can see that my questions have Answer's, but not Answer_integers. While this somewhat makes sense, it doesn't help. How can I report averages on my answer values (which are all numbers).

Should I have used different question types (we went through them all and preferred this option). Should I build a work-around to have the Answer automatically copy to the Answer_integer?

Note: We're currently on Calgary. Looks like a number of improvements were made in Eureka which walso makes reviewing the wiki a little tougher. We're aiming to upgrade to Eureka, but not for a month or two.

Any help is appreciated!

tltoulson · ‎07-08-2014

Hi Scott,

You absolutely have the right idea. I have created a general purpose business rule for this purpose that works for all Questions and Question Types as long as the answer can be converted to an integer. I avoided editing ServiceNow provided Business Rules to ensure that they receive their upgrades properly (if you edit their business rule, it does not get updated if they make changes). Here is the rule that I use:

Table: Survey Response

Order: 200 (ensures that it runs after the original Update Answers for Reporting)

When: before

Insert: true

Update: true

Script:

(function(){


         var i = parseInt(current.response);


         if (!isNaN(i)) {


                   current.answer_integer = i;


} })();

This Business Rule will convert any current.response into an answer_integer if the response can be converted to an integer. This works because the original Business Rule will only ever set one field or the other. If a string can be converted to an integer, then both response and answer_integer are populated. I hope this helps.

View solution in original post

scottatah · ‎07-07-2014

I've discovered an Update Answers for Reporting business rule that does the following:

Based off that, I discovered that I'm using mostly type 5 (select box) questions along with one type 2 (multi line text) question. I understand from the business rule that only type 4 questions populate the answer_integer (as they're the only ones with guaranteed numeric values), but since all of our type 5 questions have numeric values tied to the answers, I don't see why they couldn't populate the answer_integer as well?

I tried modifying the business rule to be as below and it seems to work after a little testing, however, I'm not sure that's the most appropriate way to write the code or if it's the best way to go about things in general? By limiting it to instance.survey.name == IS-Survey (the only survey we have created), I hope to minimize any side-effects, but I'm still learning.

I also understand this business rule only updates on change, so it wouldn't effect existing surveys. That's an issue, but there's less than 20 surveys submitted so far, so I could just manually populate the answer_integer fields for those.

Thoughts or suggestions from the more experienced and wiser folks out there?

tltoulson · ‎07-08-2014