Thanks for quick reply @Dr Atul G- LNG , But the end user says it was already done in their previous project.
Here is the step breakdown for the steps they used while implementing-
Steps-
To create a dashboard in ServiceNow that displays one survey response per line, with columns
for incident number, respondent, date submitted, rating for each question, and survey comments, follow these steps:
ServiceNow surveys are typically stored in these tables:
- Survey Instance (asmt_assessment_instance): Represents one survey response.
- Survey Response (asmt_assessment_response): Each answer to a question in the survey. However, I was not able to find the asmt_assessment_response table instead of that I used asmt_metric_result table but I am stuck at the database view and the clause using which I can accomplish this.
- Incident (incident): The related incident record.
2. Create a Report Source
You need a report source that joins the survey instance with its related incident and responses.
a. Go to Reports > Administration > Report Sources
- Click New.
- Select Table as the source type.
- Choose Assessment Instance [asmt_assessment_instance] as the base table.
b. Add Related Fields
- Add columns via related fields:
- Incident Number: Use the reference from the asmt_assessment_instance to the incident (often via the metric_instance or reference field).
- Respondent: Usually the user field.
- Date Submitted: The sys_created_on field.
- Survey Comments: Look for a free-text or comment field in the instance or responses.
c. Flatten Ratings
- Challenge: Survey ratings are stored as individual rows in asmt_assessment_response (one per question).
- Solution: Use a Database View to pivot responses so each question rating appears in a separate column.
3. Create a Database View (Recommended)
To show each survey response on a single line with ratings as columns:
a. Go to System Definition > Database Views
- Click New.
- Name your view, e.g., u_survey_incident_dashboard.
b. Add Tables to the View
- Add asmt_assessment_instance (alias: ai).
- Add incident (alias: inc) via the reference.
- Add asmt_assessment_response (alias: ar) for each survey question (one join per question).
c. Configure Joins
- Join ai to inc on the incident reference.
- Join ai to each ar on ai.sys_id = ar.instance and ar.question = [specific question sys_id].
d. Select Columns
- inc.number (Incident Number)
- ai.user (Respondent)
- ai.sys_created_on (Date Submitted)
- ar1.value (Rating for Question 1)
- ar2.value (Rating for Question 2)
- ...repeat for each question
- ai.comments or ar.comment (Survey comments, as applicable)
4. Create the Report
a. Go to Reports > Create New
- Select Table or Database View as the source.
- Choose your new database view.
b. Configure the List Report
- Add columns: Incident Number, Respondent, Date Submitted, each rating column, and comments.
- Set filters as needed (e.g., survey for resolved incidents, date ranges).
c. Save and Add to Dashboard
- Save the report.
- Add it to a dashboard via Performance Analytics > Dashboards or Self-Service > Dashboards.
5. Test and Refine
- Preview your report to ensure each line represents a single survey response with all required data.
- Adjust your database view or report columns as needed.
I've tried replicating it but I got stuck at database view and clause via which we can join the table. Can you help me in this your inputs will be very helpful.
Please let me know if anything you finds about this.
Best Regards,
Saurabh V.
