2 Replies Latest reply on Jan 30, 2016 10:40 AM by justinc

    Exclude a value in a summary field (report)

    GeoffreyMartin

      I have a survey database that is set up much like the ones that have been floating around the forums and such.  The responses are all kept in a table and relate to the questions and customers.  I've built a report to tally the responses.  I am now being asked to exclude a certain response from the sub-summary in the report.  Is this possible?

       

      Here's how the report is set up:

      2016-01-29_15-33-38.jpg

      The count_result field is the same field in both parts.  In the second sub summary I need to exclude any result "Does not apply". I would like to show the total responses as it is, and then possibly use another calculation to make the percentages reflect the corrected total responses. The field under the percentages is using GetLayoutObjectAttribute () to get the value in it's own row and the total in the subsummary below it to calculate the percentage. The report looks like this:

      2016-01-29_15-29-12.jpg

      I'm stuck right now.  Any ideas?  Here's the kicker, of the 14 questions, there's only 5 that have that "Does not apply" response available so I can't do something universally.

       

      Thanks in advance.

        • 1. Re: Exclude a value in a summary field (report)
          Extensitech

          First, exclude the n/a ("does not apply") from the found set when you do your find to print the report.

           

          To me, it seems like that should be enough, since if you aren't going to show the response, I wouldn't think you wanted to include it in the count of total responses. It seems, though, that you want to see:

           

          Worse 1

          Better 5

          Total Responses 18

           

          Which is weird to me. It seems like the sort of thing that would make me distrust the report. But okay.

           

          Create a self-relationship from responses to responses where a constant "does not apply" equals the response, and all other parameters of your search (date range? survey?) are also the same.

           

          For a modified "total responses to this question" field, create a calculated (rather than summary) field. Your calculation should use getsummary and then add the count of the relevant n/a's via the above relationship, which will be evaluated from the first or last record in the group, depending on where you put your subsummaries.

           

          Chris Cain

          Extensitech

          • 2. Re: Exclude a value in a summary field (report)
            justinc

            I agree that excluding those responses seems a bit odd.  But...

             

            How about an ESQL call that calculates what you want?  I have seen a number of recommendations to NOT use ESQL in the schema side of things (i.e. field calculations), but I think that it might be appropriate here.  Just be SURE that you commit  all records before going to this layout/report (uncommitted records can cause your ESQL to take a long time).

             

            With THAT out of the way, I could see a possible statement being:

            SELECT COUNT( answers ) FROM responses WHERE questionID = ### and questionResponse <> "not applicable"


            I imagine there are some mistakes in that statemen, so do some testing.