Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: SQL query for report builder

SQL query for report builder 6 years 3 weeks ago #36527

  • snowball
  • snowball's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 2
  • Karma: 0
What is the SQL query that is needed to extract individual reports from the database when using a third party software for report building?
I have looked at the printanswers.php file, but I get stuck when trying to "translate" it to a SQL query.
No problem to donate if I could get the answer.
The administrator has disabled public write access.

SQL query for report builder 6 years 3 weeks ago #36529

  • Mazi
  • Mazi's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 5839
  • Thank you received: 347
  • Karma: 261
Here is the link to the database layout from the docs: docs.limesurvey.org/tiki-index.php?page=...meSurvey+Development

Also check manual -> SGQA identifier. Each answer has such an identifier which is used as column name within the "lime_survey_12345" table which holds all answer data for a certain survey.

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

SQL query for report builder 6 years 1 week ago #37051

  • snowball
  • snowball's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 2
  • Karma: 0
Thanks for the reply. I had a look at it but I did not find what I have been looking for. I cannot imagine that there are no other users who need more professional reporting than what is offered by limesurvey. As long as limesurvey offers only limited reporting it would be helpful to support users at least with providing the sql query so they can use other software for reporting. The exporting formats are not a solution as it offers little flexibility and need too much handwork.
The administrator has disabled public write access.

SQL query for report builder 6 years 1 week ago #37059

  • Mazi
  • Mazi's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 5839
  • Thank you received: 347
  • Karma: 261
Hi, I answered to your email directly.

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

SQL query for report builder 5 years 8 months ago #43868

  • gigipaul
  • gigipaul's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 1
  • Karma: 0
Hello,
I am also interested in getting the results out of the lime surveys with assessment values, so we can generate reports out of it. Please let me know
Thanks
Gigi
The administrator has disabled public write access.

SQL query for report builder 5 years 8 months ago #43990

  • Mazi
  • Mazi's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 5839
  • Thank you received: 347
  • Karma: 261
gigipaul wrote:
Hello,
I am also interested in getting the results out of the lime surveys with assessment values, so we can generate reports out of it. Please let me know
Thanks
Gigi
Saving assessment results hasn't been implemented yet. You can vote for this feature here: ideas.limesurvey.org/ideatorrent/idea/7/

To achieve this with current versions you have to code your own script to generate reports.

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

SQL query for report builder 4 years 10 months ago #58738

  • laloune
  • laloune's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 8
  • Karma: 0
Hi,

I am also strong interested in having an sql query to get the result out of the limesurvey mysql db

do you have more information about it ?

I also thought of an API that could generate a csv file that would be then importable into a report builder, any idea ?

thanks in advance,
laloune
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #64569

  • sofos
  • sofos's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 9
  • Karma: 0
Hi, the links dead, and the german xls doesn't work in openoffice :(
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #64927

  • plbt5
  • plbt5's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 7
  • Karma: 0
I Have exactly the same question as Snowball here: the need to generically retrieve the data from the database in order to feed it into a data reporting tool.

Thank you in advance
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #64936

  • c_schmitz
  • c_schmitz's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 917
  • Thank you received: 131
  • Karma: 96
There is no way to have a generic statement to retrieve response data.
For each survey the responses table is individually created with individual field names.
What do you need exactly?
Best regards

Carsten Schmitz
LimeSurvey project leader
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #65147

  • plbt5
  • plbt5's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 7
  • Karma: 0
I need exactly the same as Snowball in his initial question: "What is the SQL query that is needed to extract individual reports from the database when using a third party software for report building?". In my case the third party software is de open source PSPP application.

I want to extract data from the LS-database, either one individual response or all (or a set of) responses together in one pass. I'll probably need to group the answers based on a group-id, or a specific answer ("age > 21" or combination of answers).

What does the database schema look like?

Thanks in advance
regards
Paul
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #65153

  • laloune
  • laloune's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 8
  • Karma: 0
c_schmitz wrote:
What do you need exactly?

hi,

If I understand well other contributors need (please correct if I'm wrong), the need would be to have a "denormalized" view, in order to integrate the data into report designer (or databases).

For instance:

in the response table of each survey we have the following structure:
id;submitdate;lastpage;startlanguage;39977X2X23;39977X2X24SQ001 (and so on)
1;1980-01-01 00:00:00;2;fr;2;5
2;1980-01-01 00:00:00;2;fr;25;31

what would be useful (and more exploitable I would say) is to have the following structure:

id;submitdate;lastpage;startlanguage;question_id;response
1;1980-01-01 00:00:00;2;fr;39977X2X23;2
1;1980-01-01 00:00:00;2;fr;39977X2X24SQ001;5
2;1980-01-01 00:00:00;2;fr;39977X2X23;25
2;1980-01-01 00:00:00;2;fr;39977X2X24SQ001;31

Number of lines increases whereas number of columns decreases.

With this format it is far more usable to make joins, or filters (for instance on the type of questions)

any idea ?

Thanks in advance
laloune
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #65156

  • c_schmitz
  • c_schmitz's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 917
  • Thank you received: 131
  • Karma: 96
plbt5 wrote:
I need exactly the same as Snowball in his initial question: "What is the SQL query that is needed to extract individual reports from the database when using a third party software for report building?". In my case the third party software is de open source PSPP application.

I want to extract data from the LS-database, either one individual response or all (or a set of) responses together in one pass. I'll probably need to group the answers based on a group-id, or a specific answer ("age > 21" or combination of answers).

What does the database schema look like?

Thanks in advance
regards
Paul

Why don't you use the SPSS export? You can still remove data from SPSS after you have imported it.
Best regards

Carsten Schmitz
LimeSurvey project leader
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #65157

  • c_schmitz
  • c_schmitz's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 917
  • Thank you received: 131
  • Karma: 96
laloune wrote:
c_schmitz wrote:
What do you need exactly?

hi,

If I understand well other contributors need (please correct if I'm wrong), the need would be to have a "denormalized" view, in order to integrate the data into report designer (or databases).


any idea ?

Thanks in advance
laloune

Hello!

As you already pointed out there is nothing like that in LimeSurvey and there is no way to create such a view yet. I am sorry.
Best regards

Carsten Schmitz
LimeSurvey project leader
The administrator has disabled public write access.

SQL query for report builder 4 years 5 months ago #65158

  • plbt5
  • plbt5's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 7
  • Karma: 0
What I want to achieve is a batch-processing pipeline, configured on beforehand and doing it's trick on the moment or event of my choice. Export in SPSS-format requires a manual interaction that I don't want to perform.
The administrator has disabled public write access.
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.192 seconds