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.

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.

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?

I hate doing that, but I’m way overbooked for freelance work. Furthermore, I’m not even sure that this script still works. The people behind limesurvey can do custom work, you can reach them here: http://www.limesurvey.com/customization

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.