Hello Group,
I have a survey using a database view that uses the assessment instance, assessment result and task tables. The survey has one Likert Scale question. The caller can also add a comment but it is not mandatory. If a comment is added then the assessment instance table will have one record for the comment and another for the value (0 to 4). When I run a report I get two records in the case where the user entered a comment. The field "string value" is always 0 to 4 on one record if the user takes the survey. If they enter a comment, then there will be a second record and the string value will be the comment. But that means two records whenever a comment is added and the report ends up rather redundant looking.
I am considering running a "fix script" on the metric results table that will update the "string value" in the record with the 0 to 4 value with the comment from the second record (I'll know the comment record because its value is always -1) .
Has anyone else run in to this, or have a better suggestion on how to create a report that has the information from the comment in the same report line as the rating?
See attached of report sample below for how two records are produced .
And the answer is... It hit me that the approach was to figure out how I would do it in SQL were I to right it and try to duplicate that here and it worked. By linking to the Survey result twice I bring back two records for the same survey. But if then it was easy to have the report filter out the record that did not have a -1 which indicates it is the record with the comment AND the rating! Below is the Database view followed by the report with filters. Hoping this save someone else a lot of time.
