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 ;
FROM ;
(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 ;
INTO CURSOR SessionAvgs
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 ;
WHERE NOT EMPTY(mLikes) ;
ORDER BY 1, 2 ;
INTO CURSOR curLikes
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.
0 Comments