Reports on Survey by Incident Assignment group

[Travers M]

Let me preface this by saying I'm what you might call report building deficient.   Dot walk as I may, I can't figure out how to get this report and I'm starting to think it may not even be possible.     What I'm after is the assignment group that resolved the Incident that generated the survey.   This is using the new survey tables and not the legacy stuff but I'm getting to dead ends on the Assessment Metric and Assessment Instance tables.   I can get things from the person it's assigned to, but nothing tied back to the Incident it generated from.     Anyone out there that can help?

[gbleir]

Travers,

I have been hunting the answer to this as well.   I did get one answer out here that seemed to be a way it could be done.   However, that said, I want to do it through the report builder and with no schematics showing what reference variable from one table links to what variable on another, it is very time consuming doing the "hunt and peck" through each table in "columns and tables" and building my own reference schematic.   It's all SQL "under the covers" I believe so it shouldn't be that hard but without documentation it really is quite difficult.   (I was a SQL programmer prior to this "gig").       So anyway here is one answer I got to how you might access what you need.

""There should be a task_survey record pointing to both your survey_instance and the task - you'll need to step up to it and down to the task."

Ta-da!   Piece of cake.     So I believe he is saying that Task_Survey.Survey.sys_id would "point" to Survey_instance.sys_id.     And Task_Survey.task.sys_id would link to task.sys_id (and that would get you say task.number which would be the incident number.

I've also attached the screen shot given to me by support.   If you go to Reports > Create New you can work with it there. You'll see an "Available" and "Selected" list box when you first open the page.   Ostensibly, you are supposed to be able to get to where you need to from there.     I'm finding it a bit tricky though.  

If this helps at all or you find another piece of the puzzle I don't know about let me know!

Thanks,

Greg Bleir

[samiul1]

Thanks Greg.

But isn't it 'Task_Survey' is now legacy?

[gbleir]

Samiul,

You are correct!   With the help of one of the Service Now Techs the solution was to create a view based on asmt_assessment_instance, asmt_metric_result, and the task table.   By doing that I was able to gather all the information that I needed.   Unfortunately, the comment on my survey creates a second assessment record separate from the 0,1,2,3, etc choice on the Likert.   That means that when I run my report, if the caller added a comment, I have two records displayed.   I am working on how get all the info on just one record.

Greg

[andersn]

Please let me know if you find a good working solution to this.

The report im trying to create is as follows:

The report should provide details of NPS (Surveys) conducted

Measures

NPS score

Total Survey Sent

Total Responses Received

Response rate

Count & % of detractors

Count & % of Promoters

Count & % of Passive

Correlation of time to resolve with NPS score per instance, service or as a whole

Dimensions:

Ticket Type (IM, RM)

Service

Call Category

Sub Category

Business Unit

Priority

Owner team / Vendor which has worked on the ticket

Number of bounces (how many times a ticket was sent back and forth to different parties)

SLA status (in SLA or breached)

So far im totally lost here..