Skip to main content
Kinetic Community

Power Reporting With Kinetic Request And Kinetic Survey

At the WWRUG10 Conference I gave a presentation called Power Reporting With Kinetic Request And Kinetic Survey.  I believe that my powerpoint presentation may help you.  However, please note that I used presentation as more of an Outline so a lot of the details are not included.  However, I still believe that this could help.

In the presentation I spoke about 3 different Remedy forms that are commonly used for Reporting and therefore could also be used in your Reporting or in the creation of a Universe.  Those forms/tables are listed below:

  • KS_SRV_CustomerSurvey_base - This form will allow you to build reports based off of the Status of the Survey or Request.
  • KS_SRV_CustomerSurveyResults_join - This form will allow you to build reports that will display all Attributes and AnswerViewers.
  • KS_SRV_CustomerQuestionAnswerResults_join - This form will allow you to build reports that will display all Attributes, AnswerViewers, Full Answers, and Questions.

As examples always help me learn I have attached Three Crystal Reports to this posting and built some notes below:

 

Status.rpt - This report searches the KS_SRV_CustomerSurvey_base form in order to display the current Status of each of the Surveys or Requests.  This report uses the following SQL Query:

SELECT "KS_SRV_CustomerSurvey_base"."SurveyInstanceID", "KS_SRV_CustomerSurvey_base"."SurveySentDate", "KS_SRV_CustomerSurvey_base"."SurveyCompletedDate", "KS_SRV_CustomerSurvey_base"."Status", "KS_SRV_CustomerSurvey_base"."Survey_Template_Name"
FROM   "KS_SRV_CustomerSurvey_base" "KS_SRV_CustomerSurvey_base"
WHERE  "KS_SRV_CustomerSurvey_base"."SurveyInstanceID"='KS00D0B7A9C485Kt8TSwCo9JFAu8XR'

*Note that the WHERE statement is pulling back a specific SurveyInstanceID or just 1 specific Survey (example Customer Satisfaction Survey).

Submissions 2010.rpt - This report searches the KS_SRV_CustomerSurveyResults_join form in order to display specific information about all the Submissions of a specific Survey or Request.  This report uses the following SQL Query:

SELECT "KS_SRV_CustomerSurveyResults_join"."First_Name", "KS_SRV_CustomerSurveyResults_join"."Last_Name", "KS_SRV_CustomerSurveyResults_join"."Contact_Info_Value", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer14", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer11", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer12", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer1", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer2", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer4", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer22", "KS_SRV_CustomerSurveyResults_join"."surveyTemplateInstanceID", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer10", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer13", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer16", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer17", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer19", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer18", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer20", "KS_SRV_CustomerSurveyResults_join"."SurveyCompletedDate", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer5", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer6", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer7"
FROM   "KS_SRV_CustomerSurveyResults_join" "KS_SRV_CustomerSurveyResults_join"
WHERE  "KS_SRV_CustomerSurveyResults_join"."surveyTemplateInstanceID"='<wbr>KS00D0B7A9C485fUvXSwtEhGHAjlFt' AND "KS_SRV_CustomerSurveyResults_join"."Last_Name"='Augustson'

*Note that because I have Production data against this specific Survey that I had to limit the results to the Specific SurveyTemplateInstanceID (example Customer Satisfaction Survey) and Last_Name = 'Augustson'.  By putting my Last Name into the qualification I am only displaying the one Test record.

As I know the Question to AnswerViewer relationship I have hard-coded the field heading and placed the correct AnswerViewer under that field heading.

Submissions 2010 - Full Answers.rpt - This report is a copy of the Submissions 2010 report, however, it contains 3 subreports.  The subreports are used to pull back the Full Answers for the Topic (AnswerViewer5), Abstract (AnswerViewer6), and the Synopsis (AnswerViewer7) as these questions could contain data that is over the 255 limit attached to the AnswerViewer fields.  Each of the subreports use the KS_SRV_CustomerQuestionAnswerResults_join form as this form holds the FullAnswer field.  The main report has the following SQL Query:

SELECT "KS_SRV_CustomerSurveyResults_join"."First_Name", "KS_SRV_CustomerSurveyResults_join"."Last_Name", "KS_SRV_CustomerSurveyResults_join"."Contact_Info_Value", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer14", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer11", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer12", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer1", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer2", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer4", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer22", "KS_SRV_CustomerSurveyResults_join"."surveyTemplateInstanceID", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer10", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer13", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer16", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer17", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer19", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer18", "KS_SRV_CustomerSurveyResults_join"."AnswerViewer20", "KS_SRV_CustomerSurveyResults_join"."SurveyCompletedDate", "KS_SRV_CustomerSurveyResults_join"."CustomerSurveyInstanceId"
FROM   "KS_SRV_CustomerSurveyResults_join" "KS_SRV_CustomerSurveyResults_join"
WHERE  "KS_SRV_CustomerSurveyResults_join"."surveyTemplateInstanceID"='KS00D0B7A9C485fUvXSwtEhGHAjlFt' AND "KS_SRV_CustomerSurveyResults_join"."Last_Name"='Augustson'

*Note that because I have Production data against this specific Survey that I had to limit the results to the Specific SurveyTemplateInstanceID (example Customer Satisfaction Survey) and Last_Name = 'Augustson'.  By putting my Last Name into the qualification I am only displaying the one Test record.

The first subreport is called Submissions 2010-Sub.rpt.  This subreport is used to return the FullAnswer value from a specific QuestionInstanceId.  In this case that question is the Topic.  Below is the SQL query:

SELECT "KS_SRV_CustomerQuestionAnswerResults_join"."surveyTemplateInstanceID", "KS_SRV_CustomerQuestionAnswerResults_join"."QuestioninstanceId", "KS_SRV_CustomerQuestionAnswerResults_join"."CustomerSurveyInstanceId", "KS_SRV_CustomerQuestionAnswerResults_join"."FullAnswer"
FROM   "KS_SRV_CustomerQuestionAnswerResults_join" "KS_SRV_CustomerQuestionAnswerResults_join"
WHERE  "KS_SRV_CustomerQuestionAnswerResults_join"."CustomerSurveyInstanceId"='AG00D0B7A9C4858vykTA_iF_LwSHPv' AND "KS_SRV_CustomerQuestionAnswerResults_join"."surveyTemplateInstanceID"='KS00D0B7A9C485fUvXSwtEhGHAjlFt' AND "KS_SRV_CustomerQuestionAnswerResults_join"."QuestioninstanceId"='KS00D0B7A9C485f0vXSwI05GHAtlFt'

*Note that the subreport is linked to the main report based on the CustomerSurveyInstanceId.  The CustomerSurveyInstanceId is the unique identifier for each person's submission to a specific Survey (example Joe's submission to the Customer Satisfaction Survey).  The surveyTemplateInstanceID is the unique identifier for a Survey (example Customer Satisfaction Survey).  The QuestioninstanceId is the unique identifier for a question in a specific Survey (example What is your first name on the Customer Satisfaction Survey).

The second subreport is called Submissions 2010-Sub.rpt - 01.  This subreport is used to return the FullAnswer value from a specific QuestionInstanceId.  In this case that question is the Abstract.  Below is the SQL query:

SELECT "KS_SRV_CustomerQuestionAnswerResults_join"."surveyTemplateInstanceID", "KS_SRV_CustomerQuestionAnswerResults_join"."QuestioninstanceId", "KS_SRV_CustomerQuestionAnswerResults_join"."CustomerSurveyInstanceId", "KS_SRV_CustomerQuestionAnswerResults_join"."FullAnswer"
FROM   "KS_SRV_CustomerQuestionAnswerResults_join" "KS_SRV_CustomerQuestionAnswerResults_join"
WHERE  "KS_SRV_CustomerQuestionAnswerResults_join"."CustomerSurveyInstanceId"='AG00D0B7A9C4858vykTA_iF_LwSHPv' AND "KS_SRV_CustomerQuestionAnswerResults_join"."surveyTemplateInstanceID"='KS00D0B7A9C485fUvXSwtEhGHAjlFt' AND "KS_SRV_CustomerQuestionAnswerResults_join"."QuestioninstanceId"='KS00D0B7A9C485f0vXSwsE5GHAulFt'

*Note that the only difference between this subreport and the one described above is the QuestionsinstanceId.

The third subreport is called Submissions 2010-Sub.rpt - 02.  This subreport is used to return the FullAnswer value from a specific QuestionInstanceId.  In this case that question is the Synopsis.  Below is the SQL query:

SELECT "KS_SRV_CustomerQuestionAnswerResults_join"."surveyTemplateInstanceID", "KS_SRV_CustomerQuestionAnswerResults_join"."QuestioninstanceId", "KS_SRV_CustomerQuestionAnswerResults_join"."CustomerSurveyInstanceId", "KS_SRV_CustomerQuestionAnswerResults_join"."FullAnswer"
FROM   "KS_SRV_CustomerQuestionAnswerResults_join" "KS_SRV_CustomerQuestionAnswerResults_join"
WHERE  "KS_SRV_CustomerQuestionAnswerResults_join"."CustomerSurveyInstanceId"='AG00D0B7A9C4858vykTA_iF_LwSHPv' AND "KS_SRV_CustomerQuestionAnswerResults_join"."surveyTemplateInstanceID"='KS00D0B7A9C485fUvXSwtEhGHAjlFt' AND "KS_SRV_CustomerQuestionAnswerResults_join"."QuestioninstanceId"='KS00D0B7A9C485f0vXSwLU9GHAvVFt'

*Note that the only difference between this subreport and the one described above is the QuestionsinstanceId.

In order to determine how the Questions are "Mapped" to the AnswerViewer values I would suggest the following:

Search the KS_SRV_SurveyQuestion form for your specific Survey using the SurveyInstanceID.  Once completed you will need to look at the FieldMapNumber field.  The value stored in the FieldMapNumber field is directly related to the AnswerView value.  See the example below:

SurveyInstanceID = KS00D0B7A9C485fUvXSwtEhGHAjlFt

Question:

First Name

FieldMapNumber = 8

Because the FieldMapNumber = 8 that means that the First Name question will push its Answer into AnswerViewer8

I have run a quick and dirty report out of Remedy to show you the relationships on my Survey.  The Excel file is called FieldMapNumber to AnswerViewer.xls