Because dates are stored in Unix format, you need to create these calculated fields to transform them in timestamp:
|
date_notification | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_SFB_FEEDBACK_NOTIFICATIONS.NOTIFICATION_DATE) |
date_responded | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_SFB_FEEDBACK_NOTIFICATIONS.RESPONDED_DATE)You can simply copy and paste the expression above to the Expression field |
date_valid_until | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_SFB_FEEDBACK_NOTIFICATIONS.VALID_UNTILL_DATE) |
date_schedule_start | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSFB_FEEDBACK_SCHEDULE.schedule_start_date) |
date_schedule_end | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSFB_FEEDBACK_SCHEDULE.schedule_end_date) |
If you need help creating the fields check this: Advanced Reporting 101: Calculated Fields
5. Display your tables
You can create a new set and add all three tables above to it or add just the fields you are going to use for reports. Here are the fields I have added:
6. Close the domain designer
You can follow the second part of the document below for that:
Advanced Reporting 101: Accessing the Domain Designer
7. Create your reports
The joins you created above will allow you to have tickets information along with the surveys responses. That means you can get who resolved the ticket, the requester, CCTI or any other relevant information directly from the Tickets set and use it to filter, group, organize your reports:
Average per question and org:
The Answer average is calculated using a custom measure:
if("answer_submitted" == 'Strongly Agree' or "answer_submitted" == 'Thumbs Up (Liked)', 5, if("answer_submitted" == 'Agree', 4, if("answer_submitted" == 'Neutral', 3, if("answer_submitted" == 'Disagree', 2, if("answer_submitted" == 'Strongly Disagree' or "answer_submitted" == 'Thumbs Down (Dislike)', 1, 0)))))
This expression will convert text responses to values and in the Summary Calculation I set it to Average:
Average trend per question
My slice has just a few surveys answered that I created to build this document. Yours will look much better, I promise.
Here, the chart type is Time Series Spline. And I'm using the ticket's Created Date field for grouping. I recommend using closed date or date_notification. I'm not using them because my charts would look even worse since all dates would be the same
Same information in a Crosstab:
Response Rate by Month
Again, I just keep in mind that I have just a few surveys responded and this will look much better in a system with more data:
Table with questions and answers grouped by ticket
By now you probably got the idea. You can also have reports by Resolved by Group, CCTI, etc. Just use your imagination.
Cheers,
Daniel Sasajima