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 ;
  INTO CURSOR ConfSummary      

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

#DEFINE CRLF CHR(13) + CHR(10)

cQuery = ""
nItem = 1
SCAN
  IF NOT EMPTY(m.cQuery)
     cQuery = cQuery + CRLF + "UNION ALL ;" + CRLF
  ENDIF
 
  cThisQuestion = ALLTRIM(OpenEnded.cQuestion)
  cThisField = ALLTRIM(OpenEnded.cField)
  TEXT TO cThisQuery TEXTMERGE NOSHOW
SELECT RECNO() AS nRec, ;
      <> AS mComment, ;
      "<>" AS cWhich, ;
      "<>" AS cQuestion, ;
      <> as nSort ;
 FROM ConfEval ;
 WHERE NOT EMPTY(<>) ;
  ENDTEXT
 
  cQuery = m.cQuery + m.cThisQuery
  nItem = m.nItem + 1
ENDSCAN

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

EXECSCRIPT(cQuery)

SELECT curComments

REPORT FORM ConfEval PREVIEW

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.