Filtering a Soundings Survey Part 2

DotComIt, my company, was a sponsor at Flex Camp Boston last month and collected a bunch of "market research" type of data. To collect the data I used Soundings, a free open source survey utility from ColdFusion's open source guru, Ray Camden.

After entering all the data (not a fun job), I realized that Soundings was lacking in the reporting area, so I set out to expand the reporting capabilities of the project. You can read my first entry here where I create a template designed for collecting the information we wanted to use to filter the survey.

The form is passing a form field named "QuestionXX" and the value is the AnswerID. We are also passing the question type in a form field named "QuestionTypeXX". We need to take that data and turn it into a list of users who gave that answer. I wrote a UDF to do just that and put my UDF into the "includes/udf.cfm" file. Let's look at that UDF:

The preceding segment is the function header, and a bunch of local variables are defined. Pretty boring.

This segment loops over all the form fields. If the form field is named questionXX then we want to process it. In retrospect, I guess this won't work if you number your questions using single digits, or triple digits.

We found a question, so let's get the ownerIDs. But, the way we get ownerIDs is different depending on the type of question, so we enter into a case statement based on the question type:

Complicated looking query, I know. You'll have to believe me when I say it works. :-) I created a case entry, and query, for each question type I cared about filtering; but I'll keep this blog post to that one. This finishes off the function:

If this is the first result set, we take it. If we already had a result set, we use the ListInCommon function to remove items that were not in both result sets. This, in effect, forces our filtered results to always use the "AND" operator; which was fine for my purposes.

Then we end the loop, return the results, and end the function. Simple enough, right?

Back to the main stats page, first call the function to get the IDs, like this:

When we run the stats for each question type, we need to pass in those IDs:

HandlerRoot is part of the question and tells us where to go to find that questions individual handlers. I modified each relevant stats.cfm. I'll take a look at the multiplechoices/stats.cfm; which is called as a custom tag:

I made no changes here; this just checks to see if we want to get the 'other' responses [or not] and then retrieves the possible answers.

This is the query. I added the condition. If we passed in ownerIDs, then we use them to filter the query results. Otherwise, we keep things as they were.

This saves the totals, if anything was returned.

Then we grab the number of other values. It's another query with the same ownerID filtering code.

This ends of the first if statement. If we dont' care about getting the other data, then we run the query without it. This query has the same ownerID filtering change.

Finally, we pass the data back out of the tag and exit. It works well. I modified all the individual stat files to do, in essence, the same exact thing. Once you modify the results, the display code doesn't need any changes.

And that's how I did it!