Filtering a Soundings Survey Part 1

I was at Flex Camp Boston a few days back 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. I could filter the data based on questions, like this:

There is a question checkbox, and you can control the results that appear in your questions. Functional, but it only restricts the questions you see answers to, it does not filter the data.

I wanted to run reports based on answers that users had given. For example, I want to know how "advanced" users responded to my questions, compared to how "newbies" responded. Out of the box, Soundings doesn't provide this sort of advanced filtering; I thought it was time to brush up on my ColdFusion coding hat and give it a shot.

I've been using Soundings a lot these days, so decided to write something generic that could apply to any survey instead of just crunching data in excel. I was envisioning that I'd run a full report and then through a series of checkboxes or drop downs I'd be able to filter the data and get new results. ( In retrospect, maybe I should have tried to modify the main filtering page, but I didn't ).

Unfortunately, the code is barely documented, so I've struggled to pound through this while staying true to the original intent of the code. Reports are generated on the stats.cfm page, so I started there. I created a custom tag (reportfilter.cfm) that will loop through all the questions and display the filter box for each one. Let's hack that apart line by line:

First, I define the input variable of the custom tag. "surveyquestions" will contain a query object of questions.

I want to make sure that I only display one set of results, so I added this line to run my code upon only when the execution mode is start. No end tag for me.

This starts the form; which submits back onto itself. It also specifies the form variables for Format, SurveyID, and the optional QuestionFilter. These are the values that soundings uses on stats.cfm to decide whether to display the "pick a survey" form or the actual survey results.

Time to loop over the Survey Questions.

If someone already filled out this form, I want to find the answer they provided. That's what this group of code does. Answers are stored in the form scope as "form[question#Rank#]". I based this whole approach, loosely, off the code used to display the surveys.

There must have been an easier way to do this that escapes me. I wanted to pass the current query row (AKA a single question) into a "filterinput" custom tag. My solution was to copy all the row information into a struct and use that as the argument.

This calls a custom tag to display the filterinput handler. For each question type I created a filterinput.cfm tag. Many of them I left blank, instead dealing with the type of questions I wanted to filter, but this approach can easily be extended for your own custom types, or for text boxes or a matrix (which I didn't implement). I'll show an example "filterinput" in a second.

The final lines of the custom tag just close out the tags that we left open, while also adding a filter button.

This is the reportfilterinput.cfm tag placed in the "handlers/multiplechoice/" directory. Similar files can be created and put in the directories for other question types:

The question is my question structure, containing a single question row from the query. The answer will contain the currently selected answer, and it passed in from the form scope. The step is actually duplicate data (since I'm passing in a full query row), but it is an artifact left over from my 'bastardizing' of the display templates.

This retrieves the answers to the current question.

Output the question, along with the question number.

I haven't decided how kludgy this is or not. For filtering purposes, I wanted to use checkboxes even if the user can only enter a single question. Perhaps I want to know how "Guru and Advanced" users answered, compared to "newbie and intermediate" users? From a filtering stand point I want to be able to select a combination of answers.

The end result of this is that the question#attributes.step# will often contain a list of values instead of just a single value. So, to match up whether the answer should be checked, I used ListFindNoCase. Sometimes ListFind functions can be problematic in CF. Lists in ColdFusion are stored strings, and ListFind functions isn't comparing your target string to list elements, but rather doing a text comparison. It can generate false positives in some cases, where the item you want to find is part of another list item. In this case, it works fine, though.

The other multiplechoice options, are just a wrapper for "multiplechoice"; and I ignored ones that allowed the user to enter an 'other' option. I'm assuming I Won't want to filter on the 'other'.

The end result looks something like this:

Now that I have a long list of questions and answers on the stats reporting page, I decided it was too long and wrapped it up in a div layer that I can hide (or display) by clicking the filter text. There was probably some JavaScript built into Spry to do this, but I used a library I already had for this exact purposes. ( Given my time constraints and other stuff going on, I didn't want to take time to learn spry ).

Now that I have a [slightly kludgy] filtering interface, the next step is to take that data and actually filter the data. That will have to wait for another post. My plan is to use the form values 'questionxx' to get a list of valid OwnerIDs from the database; and then use those OwnerIDs to adjust the number counts. Conceptually it's real easy, right?