Reporting on the overall conference data

Reporting on the overall conference evaluations was both easier and harder than reporting the speaker evaluations. Easier because we needed only a single report, with no need to break things down by speaker. Harder because instead of three open-ended questions, there were 12.

My first inclination was to handle the comments as I had on the speaker evaluations, with a separate cursor and detail band for each. But 12 detail bands seemed excessive. More importantly, I was feeling too lazy to write the code for the 12 queries and set up the 12 relations and so forth.

Instead, I decided to use the solution I’ve been using for years for simulating multiple detail bands. Create a single cursor with all the data in the right order, and an extra field that identifies the “detail band” to which a given record belongs. That still called for an enormous UNIONed query and I still felt too lazy to write it. So I decided to let the query write itself.

I wrote code to create a cursor with one record for each open-ended question. Then, the code loops through that cursor and uses textmerge to create the query that collects data for that question.

With that out of the way, the rest was easy. I wrote a query to compute the averages for the various numerical questions. For some questions, we actually wanted to see the breakdown of answers, not just the average, so the query handles those with SUM(IIF(, 1, 0)).

Here’s the complete program:

SELECT CNT(*) as nEvalCount, ;
SUM(IIF(lWeekend, 1, 0)) as nWeekend, ;
SUM(IIF(lWeekend, 0, 1)) as nWeek, ;
AVG(nKeyLength) as nKeyLength, ;
AVG(nKeyConten) as nKeyConten, ;
AVG(nKeyNext) as nKeyNext, ;
AVG(nConfRooms) as nConfRooms, ;
AVG(nWireless) as nWireless, ;
AVG(nHotel) as nHotel, ;
AVG(nTopics) as nTopics, ;
AVG(nLevel) as nLevel, ;
AVG(nSpeakers) as nSpeakers, ;
SUM(IIF(lKokopelli, 1, 0)) as nUsedKoko, ;
SUM(IIF(lKokopelli, 0, 1)) as nNoKoko, ;
AVG(nKokoUse) as nKokoUse, ;
SUM(IIF(lNextYear, 1, 0)) as nNextYear, ;
SUM(IIF(lNextYear, 0, 1)) as nNoNext, ;
AVG(nMaxHotel) as nMaxHotel, ;
SUM(IIF(nMaxHotel=1, 1, 0)) as nHotel1, ;
SUM(IIF(nMaxHotel=2, 1, 0)) as nHotel2, ;
SUM(IIF(nMaxHotel=3, 1, 0)) as nHotel3, ;
SUM(IIF(nMaxHotel=4, 1, 0)) as nHotel4, ;
AVG(nFAcility) as nFacility ;
FROM ConfEval ;

CREATE CURSOR OpenEnded (cQuestion C(100), cField C(10))
VALUES ("What did you like about the conference?", ;
VALUES ("How can we improve the web site?", ;
VALUES ("How can we improve the registration process?", ;
VALUES ("How can we improve the conference check-in?", ;
VALUES ("How can we improve the variety of topics presented?", ;
VALUES ("How can we improve the evening sessions/activities?", ;
VALUES ("How can we improve other things?", ;
VALUES ("What else should the keynote include?", ;
VALUES ("Speakers recommended?", ;
VALUES ("Topics recommended?", ;
VALUES ("Why not attending next year?", ;
VALUES ("Other comments?", ;


cQuery = ""
nItem = 1
IF NOT EMPTY(m.cQuery)
cQuery = cQuery + CRLF + "UNION ALL ;" + CRLF

cThisQuestion = ALLTRIM(OpenEnded.cQuestion)
cThisField = ALLTRIM(OpenEnded.cField)
<> AS mComment, ;
>" AS cWhich, ;
"<>" AS cQuestion, ;
<> as nSort ;
FROM ConfEval ;
>) ;

cQuery = m.cQuery + m.cThisQuery
nItem = m.nItem + 1

cQuery = m.cQuery + CRLF + "ORDER BY nSort, nRec INTO CURSOR curComments"


SELECT curComments


The report looks simpler than the speaker evaluation report. All the average and break-out data is in the title band, which is set to appear on a separate page. Then, there’s a single group, based on the nSort field. The group header contains the cQuestion field and the detail band contains the mComment field and a dotted line, so we can see where one person’s comments end and the next begins. Here’s a picture of the report:

Next time, some comments on the actual evaluation data.

Southwest Fox 2007 Photos Online

I posted photos Tamar and I took at Southwest Fox 2007 today. Thanks to Dave Aring (once again!) for cleaning them up and Cesar Chalom for posting a blog entry that make quick work of generating thumbnail images using the GDIPlusX code from VFPX. If you were at the conference and took any photos you’d like to share, we’d love to have them. Please email them to dhennig AT stonefield DOT com.

Processing the Evaluations

I didn’t expect helping to run Southwest Fox to give me a chance to learn new things about VFP. But speaker coordination was one of my responsibilities, so I took the lead in handling the evaluation data. On the plane coming home, I designed a couple of tables to hold the data and started working on data entry forms.

We gave each attendee an evaluation booklet with an identical section for each time slot, plus an overall conference evaluation at the end. (One of the things I learned doing the data entry was that we should put the overall conference evaluation at the front of the booklet, not the back.) At the end of the conference, we divided the evaluation booklets into three equal piles; we each took a pile home with us.

Because the data entry would be distributed and we wanted things to be simple, I chose to go with a nearly flat file design. I created two tables, one to hold evaluations of the individual sessions, with one record for each session evaluated, and another to hold the overall conference evaluation data, with one record per evaluation booklet. We already had tables for the speakers, topics and timeslots, so I put foreign keys to those into the session evaluation table.

I created a data entry form for each table, keeping things as simple as I could, but with an eye toward streamlining data entry. One thing that made creating these forms easier was that we only needed to add records, not to edit them.

The real fun came in creating reports once we’d done the data entry. We needed two reports, one for the session evaluations and one for the overall conference evaluations. Each posed interesting problems. The key issue in both cases was that we had lots of free-form comments in the evaluations and needed a way to combine that data with the averages for the numeric questions.

For the session evaluations, we also needed to give each speaker his or her own evaluations and no one else’s. I started, as I pretty much always do for reports, by working on queries to pull out the data to report. I needed to average each of the numeric ratings for each repeat of each session, as well as count the number of evaluations submitted. No problem; a simple grouped query handles that:

SELECT iTimeSlot, iSpeaker, iTopic, CNT(*), ;
AVG(nPrepared) as nPrepared, ;
AVG(nKnowledge) as nKnowledge, ;
AVG(nInteresti) as nInterest, ;
AVG(nMatchDesc) as nMatchDesc, ;
AVG(nValuable) as nValuable, ;
AVG(nRelevant) as nRelevant, ;
AVG(nAgain) as nAgain;
FROM SessionEval ;
GROUP BY 1, 2, 3

To add the speaker’s name, the session title and the time the session was given, I used that query as a derived table (one of the features added in VFP 9 that’s really grown on me over time) inside another query that joined the look-up tables with these results:

SELECT SessAvg.*, Speaker.cFirst, Speaker.cLast, ;
Topic.mTitle, TimeSlot.tStart ;
(SELECT iTimeSlot, iSpeaker, iTopic, CNT(*), ;
AVG(nPrepared) as nPrepared, ;
AVG(nKnowledge) as nKnowledge, ;
AVG(nInteresti) as nInterest, ;
AVG(nMatchDesc) as nMatchDesc, ;
AVG(nValuable) as nValuable, ;
AVG(nRelevant) as nRelevant, ;
AVG(nAgain) as nAgain;
FROM SessionEval ;
GROUP BY 1, 2, 3 ) SessAvg ;
JOIN Speaker ;
ON SessAvg.iSpeaker = Speaker.iID ;
JOIN Topic ;
ON SessAvg.iTopic = Topic.iID ;
JOIN TimeSlot ;
ON SessAvg.iTimeSlot = TimeSlot.iID ;
ORDER BY cLast, cFirst, iTopic, tStart ;

With that data in hand, I looked at the harder part. For each session, we’d provided three places to write comments:

  • What did you like about this session?
  • What didn’t you like about this session?
  • Other comments

I wanted to group all the answers for each question for a given session together, so the speaker would see all the positives, then all the negatives, then any other comments.

Making this work gave me my first chance to use VFP 9’s multiple detail band reports in a production situation. I wrote three separate queries to pull out the answers into separate cursors, and related each of those cursors to the summary cursor, SessionAvgs. Here’s the query and relation for the things attendees liked about a session:

SELECT iTimeSlot, iSpeaker, mLikes, RECNO() as nRec ;
FROM SessionEval ;
ORDER BY 1, 2 ;
INDEX on PADL(iTimeSlot, 2) + PADL(iSpeaker, 2) TAG TimeSpeak

SELECT SessionAvgs
SET RELATION TO PADL(iTimeSlot, 2) + PADL(iSpeaker, 2) INTO curLikes

Then, I started to design the report. I added two groups: iSpeaker, the speaker’s primary key; and transform(iSpeaker) + transform(itimeslot), the combination of speaker and timeslot. I set the inner group to start on a new page. (In writing this, I realize that the outer group was unnecessary.)

I put the session-level information in the group header of the inner group, laid out pretty much as it looked in the evaluation booklet.

Next, I added two additional detail bands to the report. I set each of the three detail bands to be driven by one of the cursors containing the comments, and I gave each detail band its own header and footer bands (a very cool feature). I put the question for each band into the detail band header. The detail band itself contained a single field, set to float and stretch, with an expression like this:

transform(curLikes.nRec) + ": " + curLikes.mLikes

Why include the record number? To let speakers match up likes, dislikes and other comments from a particular attendee.

Once I got all this working, I thought I was done, and sent the data, the code and the report off to Rick and Doug for comments. Doug took my queries and modified them to generate rankings for both speakers and sessions. We decided that speakers might want to know their own rankings, so I incorporated Doug’s code into the program, and added a line to the group header to show that information.

I also added some code to generate PDFs, one per speaker. I tried both FRX2Any and XFRX, and found that for this particular report, XFRX handled things better. (I’m using each of the two for different client projects at the moment.)

Again, I thought I was done, but when I started writing a cover email to go to the speakers, I realized that, along with the rankings, it would be useful for the speakers to see the overall averages for each of the numeric items, so I added another query to calculate those, and added that data to the group header, as well.

Here’s the final report as it looks in the Report Designer:

Since this is already way too long for a blog entry, I’ll write about the report for the overall conference evaluations separately. Over the next week or two, I’ll also talk a little about what people had to say about Southwest Fox and the sessions.


A month ago Southwest Fox 2007 was just getting started. It was a terrific conference thanks to so many people including the speakers, the hotel staff, the conference center staff, and of course all the developers who came to the conference.

Since the conference wrapped up the three organizers have been working hard on a couple of fronts. The first front is to review the evaluations and get them entered into a set of tables, and then get the results to the speakers. This has been accomplished. The feedback to the speakers was very positive and the constructive criticism useful in making the sessions better. If you have feedback to provide the organizers you can send this via snail mail if you want it to be anonymous, or send it to info AT swfox DOT net. You can also mail in the evaluation forms if you forgot to drop it off at the registration table before heading home.

The general conference evaluations have been helpful in our planning for next year. I think we will be discussing some trends we recognize in more detail in future posts. The evaluations were anonymous, but attendees posted questions in the evaluation. We cannot directly send answers, so I believe we will address some of these questions here on this blog.

The second front is the planning for next year. Believe it or not, we started this process Sunday afternoon, right after the conference finished and we completed our wrap-up meeting with the conference center folks. We are working on the contract for next year with the same hotel and conference center.

Tamar, Doug, and I decided to start this conference blog as another way to communicate information about the conference to everyone who is interested in the event, and to open up a discussion about the things we are thinking of doing next year. I hope your find this useful.

Thanks again to everyone who made Southwest Fox 2007 a success.

Only 333 days until Southwest Fox 2008! Mark your calendars for October 16-19, 2008. More details to come.