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)
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.
image001.png 78.0 K
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?
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.
If you could send me reports you have on surveys I would really like to see how you addressed it.
1 of 1 people found this helpful
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.
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.
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.
I have a suggestion: How about dumping Crystal completely and use SQL reporting instead? Not as pretty, but far more flexible and useful.
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.
Where can we get survey completion date? The date when user has filled survey.
In the vwsvallsurveyresponses table you have DateCompleted.