Using Crystal Reports with Limesurvey

While Limesurvey is a very nice tool to create and manage web surveys, it’s a bit lacking in the reporting area. The functions are a limited and even if you want a quick and dirty, but presentable report, you must export the data and use other tools to format and present the answers to the survey.

While Limesurvey is a very nice tool to create and manage web surveys, it’s a bit lacking in the reporting area. The functions are a limited and even if you want a quick and dirty, but presentable report, you must export the data and use other tools to format and present the answers to the survey.

Limesurvey results imported in Crystal Reports
Limesurvey results imported in Crystal Reports

The problem is the way how Limesurvey stores its data, more specifically the recorded answers. Every column in the table contains theanswer for the question identified by the column and each line (or record) contains a respondent. It seems like a very sensible way to store the respondent’s answers, but the trouble is that it’s very difficult to construct a generic report template in reporting tools with this kind of table schema. To be of any use, the table data have to be converted in a usable form. Furthermore, the schema used by Limesurvey is less than optimal and doesn’t use foreign keys to link tables. This complicates everything.

To convert the answer table data, I’ve written a little python script. It should be pretty plug-and-play. To use it, you have to change the database access variables. The script only takes one argument, the survey id. You can get the survey id in Limesurvey’s administration panel. It should follow the survey title when you select a survey in the administration panel.

When the script runs, it creates a table with 12 fields (or columns). Without going too much into details, it creates a group id column, a question id column, a question type column and an answer column. These columns contain the fundamental data and structure of the survey. From there the data should be in a usable format by reporting engines.

Crystal Reports need to import the tables and define the links between the tables. It needs to import these tables lime_surveys_languagesettings, lime_groups, limesurvey_crystal_report and lime_questions. You might need to install the MySQL OCDB connector to access your database from Crystal Reports. Don’t forget configure your firewall so it only access connection from your IP or use a SSH tunnel. As you probably know, it’s a very bad idea to expose an open MySQL port to the internet.

Here’s the code:
[sourcecode language=”python”]
”’
Created on 2009-10-21
olivier@olihb.com
”’
import sys
import MySQLdb as mysql
#database settings
dbport=3306
dbaddr=’server.com’
dbuser=’user’
dbdb=’database’
dbpass=’password’
dboutputtable=’limesurvey_crystal_report’
dbtableprefix=’lime_survey_’
#exception
class InputException(Exception):
pass
class Ddict(dict):
def __init__(self, default=None):
self.default = default
def __getitem__(self, key):
if not self.has_key(key):
self[key] = self.default()
return dict.__getitem__(self, key)
def xint(s):
if s is None: return None
try:
ret = int(s)
except ValueError:
ret=None
return ret
def xtupletodict(l):
d={}
for x,y in l:
d.setdefault(x,[]).append(y)
return d
def xtupleto2dict(l):
d = Ddict(dict)
for x,y,z in l:
d[x][y]=z
return d
class Question(object):
def __init__(self,data):
self.data=data
self.qid=data[0]
self.gid=data[1]
self.type=data[2]
self.sid=data[3]
def getColumns(db,dbinputsurveyid):
cursor = db.cursor()
cursor.execute("Show columns from "+dbtableprefix+dbinputsurveyid)
data = cursor.fetchall ()
return list(zip(*data)[0])
def getAllRespondants(db,dbinputsurveyid):
cursor = db.cursor()
cursor.execute("select * from "+dbtableprefix+dbinputsurveyid)
data = cursor.fetchall();
return data
def getQuestions(db,dbinputsurveyid):
cursor = db.cursor()
sql = """SELECT qid,gid,type,sid FROM lime_questions where sid=%s order by gid,question_order asc;""" % dbinputsurveyid
cursor.execute(sql)
data = cursor.fetchall();
return data
def getAllAnswers(db,dbinputsurveyid):
cursor = db.cursor()
sql="""SELECT lime_answers.qid,answer FROM lime_answers left join
lime_questions on lime_answers.qid=lime_questions.qid
where lime_questions.sid=%s;""" % dbinputsurveyid
cursor.execute(sql)
data = cursor.fetchall();
return data
def getAllAnswersCode(db,dbinputsurveyid):
cursor = db.cursor()
sql="""SELECT lime_answers.qid,code FROM lime_answers left join
lime_questions on lime_answers.qid=lime_questions.qid
where lime_questions.sid=%s;""" % dbinputsurveyid
cursor.execute(sql)
data = cursor.fetchall();
return data
def getAllAnswersOrder(db,dbinputsurveyid):
cursor = db.cursor()
sql="""SELECT lime_answers.qid,code,sortorder FROM lime_answers left join
lime_questions on lime_answers.qid=lime_questions.qid
where lime_questions.sid=%s;""" % dbinputsurveyid
cursor.execute(sql)
data = cursor.fetchall();
return data
def addAnswers(db,respondantid,surveyid,groupid,qstid,qsttype,answer,answtxt,answorder,subanswer,subanswtxt,subansworder):
cursor = db.cursor()
sql = "INSERT INTO "+dboutputtable+""" (
respondantid,surveyid,groupid,qstid,
qsttype,answer,answtext,answorder,subanwser,subanswtext,subansworder)
VALUE(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"""
cursor.execute(sql,(respondantid,surveyid,groupid,qstid,qsttype,answer,answtxt,answorder,subanswer,subanswtxt,subansworder))
return
def createOrReplaceOutputTable(db):
cursor = db.cursor()
cursor.execute("""show tables like %s""",dboutputtable)
data = cursor.fetchone();
if data is None:
#Create table
sql = """CREATE TABLE %s (
id INT NOT NULL AUTO_INCREMENT,
respondantid INT NOT NULL,
surveyid INT NOT NULL,
groupid INT NOT NULL,
qstid INT NOT NULL,
qsttype TEXT NOT NULL,
answer VARCHAR(1000),
answtext TEXT,
answorder INT,
subanwser VARCHAR(1000),
subanswtext TEXT,
subansworder INT,
PRIMARY KEY ( id ) )
ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;""" % dboutputtable
cursor.execute(sql)
else:
#Empty table
cursor.execute("TRUNCATE " + dboutputtable)
return
def main(argv=None):
# extract input table
if argv==None:
argv=sys.argv
if len(argv)>2:
raise InputException("Needs a input survey id")
dbinputsurveyid = argv[1]
else:
dbinputsurveyid = argv
#connect to database
db=mysql.connect(host=dbaddr,user=dbuser,passwd=dbpass,db=dbdb,port=dbport)
#create or delete output table
createOrReplaceOutputTable(db)
#get data from database
questions=getQuestions(db, dbinputsurveyid)
columns=getColumns(db, dbinputsurveyid)
data=zip(*getAllRespondants(db, dbinputsurveyid))
answers = xtupletodict(getAllAnswers(db,dbinputsurveyid))
answersCode = xtupletodict(getAllAnswersCode(db,dbinputsurveyid))
answersOrder = xtupleto2dict(getAllAnswersOrder(db,dbinputsurveyid))
#looping over questions
for q in questions:
question = Question(q)
#List choice
if question.type == ‘L’ :
qstid = dbinputsurveyid+’X’+str(question.gid)+’X’+str(question.qid)
index=columns.index(qstid)
for i,a in enumerate(data[index]):
if a!=None and a!=”:
answer=int(data[index][i])
answerCodes=list(answersCode[question.qid])
answerindex = answerCodes.index(str(answer))
answerorder = answersOrder[question.qid][a]
addAnswers(db, data[0][i], question.sid, question.gid, question.qid, question.type, answers[question.qid][answerindex], None,answerorder, None, None,None)
# Multiple choices(M)
elif question.type ==’M’ :
for i,a in enumerate(answersCode[question.qid]):
qstid = dbinputsurveyid+’X’+str(question.gid)+’X’+str(question.qid)+a
qsttxt = answers[question.qid][i]
index=columns.index(qstid)
answerorder = answersOrder[question.qid][a]
for j,r in enumerate(data[index]):
if r == ‘Y’:
addAnswers(db, data[0][j] , question.sid, question.gid, question.qid, question.type, qsttxt, None, answerorder,1,None,None)
# Array-10(B)
elif question.type ==’B’ :
for i,a in enumerate(answersCode[question.qid]):
qstid = dbinputsurveyid+’X’+str(question.gid)+’X’+str(question.qid)+a
qsttxt = answers[question.qid][i]
index = columns.index(qstid)
for j,r in enumerate(data[index]):
if r!=None and r!=”:
addAnswers(db, data[0][j] , question.sid, question.gid, question.qid, question.type, qsttxt, None, answerorder, r, None,None)
#Text or Numeric
elif question.type ==’T’ or question.type ==’N’:
qstid=dbinputsurveyid+’X’+str(question.gid)+’X’+str(question.qid)
index=columns.index(qstid)
for i,a in enumerate(data[index]):
if a!=None and a!=”:
addAnswers(db, data[0][i], question.sid, question.gid, question.qid, question.type, None, a, None,None, None,None)
return
if __name__ == ‘__main__’:
main()
[/sourcecode]

8 thoughts on “Using Crystal Reports with Limesurvey”

  1. Hi Olivier. Very nice code. I guess it is interrupted on line 221. Isn´t it? By the way, do you know an equivalent code in xSQL language?
    Thanks

  2. Thank for this tutorial – I tried creating a crystal report from lime survey awhile back and ran into problem being able to aggegrate the tables and data. I’m familiar with crystal and with the Lime Survey database, but I’m not familiar with how to run python scripts (in windows) or where I need to plug in the survey id. Can you dummify your tutorial a little more for those of us who really want to report our lime survey results from crystal but aren’t familiar with python?

    1. Hi ADA,
      I never really used python under Windows, but I guess you need to install python and type:

      python.exe scriptName.py yourSurveyID

      You’ll also need to install the MySQL adapter for Python (I guess that you’ll also need to run MySQL or tunnel the connection from your local machine to your server with a ssh tunnel).
      This is an old script. I don’t know if it works with the recent versions of limesurvey.

  3. Olivier – I’m in dier need of doing what you have done here, but like ADA, I’m not familiar with Python either. I know I may be stretching by asking you for this, but is there any way you could convert the python script above into an SQL script that I can just import into PHPMyAdmin? I know I would have to change the surveyID, which I could do manually before I uploaded the script. I’m not too worried about the version compatibality since I’m using an old version of limesurvey myself. I think others (besides myself and ADA) will find sql import script very useful – especially since you are the top google search result for incorporating crystal reports into limesurvey.

  4. Hi Olivier,
    it seems to me that i was looking for exactly this! Thank you very much!
    But i wonder where to put the sid – i managed to connect to my database (rows 009 to 016) but i do not understand where i am supposed to write the sid.
    Furthermore, i get an error message when i´m getting to the end of your code. Possibly this happens because of the missing sid?
    Thank you for your reply 🙂
    greetings from aachen/GER
    sacha

Leave a Reply to Olivier H. Beauchesne Cancel reply

Your email address will not be published. Required fields are marked *