13 Replies Latest reply on Nov 16, 2015 11:07 AM by Richard Lester

    track-it survey reports

    Richard Lester
      Share This:

      Does anyone have suggestions on creating a crystal report to pull trending data on surveys.  Track-it 11 only had one canned report on surveys and that does not help with trending.  I can't find a stucture of table relations so not sure the best tables to use.

        • 1. Re: track-it survey reports

          What type of information are you looking to report on?  I have done some reports for our department and would be willing to pass them on. Though I am sure you have different requirements than us.


          Here is a list of the Survey tables and their content. The first 7 are the tables you will most likely interact with.


          tblsvSurveyQuestionResponses - Answers to Survey Questions

          tblsvSurveyEmailLog - List of Surveys Sent with Date and WO

          tblsvQuestions - Questions

          tblsvQuestionAnswers - Answers Assigned to each question

          tblsvSurveyQuestions - List of Questions in survey and display Order

          tblsvSurveys - List of Surveys

          tblsvSurveyResponses - Survey Completed Date and WO

          ----Not necessarily core to reporting ----------

          tblsvQuestionCategory - Which Questions are assigned to what Category

          tblsvCategories - Question Groups

          tblsvLastRunTime - Unknown

          tblsvWOEvaluated - Unknown

          tblsvMonitorConfiguration - Survey Frequency Configuration

          tblsvEmailConfiguration- Email Configuration Settings

          tblsvSurveyConfiguration - Survey Configuration

          tblsvQuestionTypes - What type of question (free form, multiple choice, Multiple Select)

          • 2. Re: track-it survey reports
            Richard Lester

            Here is what I am running into.  I am trying to find a way to trend questions but all the fields question field in dbo_vwsvallsurveyresponses and dbo_tblsvsrveyquestions have memo fields as the field type.  I seems that grouping using crystal reports does not allow you to use a memo field for grouping so I need suggestions on how to trend replies to questions or even a way to have the reporting like the drilldown in the admin console.


            • 3. Re: track-it survey reports
              Cris Coffey

              If we can get all the ideas out here, we can look at creating a report that does these things. What exactly do you want to see on the report? What business question are you trying to solve?

              • 4. Re: track-it survey reports
                Richard Lester

                I am  looking to create a report that will count up scoring on questions.  I have the questions in Questions Groups for the kind of question so I can once created change questions or add questions to the group for future surveys. I have the replies to the questions as a 1 to 5 scale 5 being the highest score.  So when I run the report it would show the tech if it is connected directly to the work order how many surveys that were completed.  I would then have the average grade for each of the question groups

                Example  groups: Access, Availability, communications, competence, courtesy, overall satisfaction and responsiveness.

                So if I could use the question Groups for grouping this would work.  Another question for the questions and answer fields in the tables they are set as memo and need to know if I would have the type changed to text if that would do bad things to the database?  Crystal does not allow me to group using memo fields and that is what is making this so hard.

                • 5. Re: track-it survey reports
                  Richard Lester

                  If you could send me reports you have on surveys I would really like to see how you addressed it.

                  • 6. Re: Re: track-it survey reports


                    In order to get what we were looking for i created a view in SQL that the attached report references. I have attached command to create the view in TrackItSurveyView. You will want to change the Survey ID to the one you are using. You can get the Survey ID from [tblsvSurveys] the number in the ID column.


                    In the attached Sampe Survey Report 1, the results are first grouped by the Technicians department so that we could break down what area is having/not having an issue. I have another section of the same report that gives a total count of all the Surveys sent and taken. I will post that later after I make sure I don't have any more custom views for that.


                    If you have specific questions you want to discuss you can messsage me your email as well.

                    1 of 1 people found this helpful
                    • 7. Re: track-it survey reports
                      Cris Coffey

                      You could also create a calculated field in the report that you then group on.  It might be helpful if you could post a screen shot of the survey questions along with a mockup of what you want to see on the report. I am still having some trouble visualizing what you need. I am sure we could make it work somehow if we could clearly define what the report should look like.

                      • 8. Re: track-it survey reports
                        Richard Lester


                        Do thank you for the custom view information and the sample survey.  I am wondering if you could give me a report that gives a summary  of all the surveys that were entered.  I have tried to adjust the one you gave but I start running into duplication of data so I go from having 43 records returned to several thousand.

                        • 9. Re: track-it survey reports
                          Richard Lester

                          I found another easier way to get the report I needed.  I discovered that if you create a formula "question text is what I called mine" that only has the field {vwsvAllSurveyResponses.Question} you can then use it in Crystal Reports Group Expert.  Also creating a SQL view can work.

                          7-11-2013 12-42-16 PM.png

                          • 10. Re: track-it survey reports

                            I have a suggestion:  How about dumping Crystal completely and use SQL reporting instead?  Not as pretty, but far more flexible and useful.

                            • 11. Re: track-it survey reports
                              Richard Lester

                              My management likes pretty things so Crystal was the pretty choice☺ just wish that support could have given this information or have more canned reports on surveys.

                              • 12. Re: track-it survey reports

                                Hi Paul,


                                Where can we get survey completion date? The date when user has filled survey.



                                • 13. Re: track-it survey reports
                                  Richard Lester

                                  In the vwsvallsurveyresponses table you have DateCompleted.