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

TOPIC: SQL query for report builder

Re: SQL query for report builder 3 years 3 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.

Re: SQL query for report builder 3 years 3 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.

Re: SQL query for report builder 3 years 3 months ago #65156

  • c_schmitz
  • c_schmitz's Avatar
  • NOW ONLINE
  • LimeSurvey Team
  • Posts: 837
  • Thank you received: 120
  • Karma: 93
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.
Support us, too. Donate to the LimeSurvey project and help keep us going!
The administrator has disabled public write access.

Re: SQL query for report builder 3 years 3 months ago #65157

  • c_schmitz
  • c_schmitz's Avatar
  • NOW ONLINE
  • LimeSurvey Team
  • Posts: 837
  • Thank you received: 120
  • Karma: 93
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.
Support us, too. Donate to the LimeSurvey project and help keep us going!
The administrator has disabled public write access.

Re: SQL query for report builder 3 years 3 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.

Re: SQL query for report builder 3 years 3 months ago #65159

  • c_schmitz
  • c_schmitz's Avatar
  • NOW ONLINE
  • LimeSurvey Team
  • Posts: 837
  • Thank you received: 120
  • Karma: 93
I see. Well, features like that are on our roadmap but do not exist yet. Sorry.
Support us, too. Donate to the LimeSurvey project and help keep us going!
The administrator has disabled public write access.

Re: SQL query for report builder 3 years 3 months ago #65161

  • laloune
  • laloune's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 8
  • Karma: 0
c_schmitz wrote:
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.

okay thanks for your response

is it also on the roadmap yet ?

if yes I am ok to contribute (for testing for instance :))
The administrator has disabled public write access.

Re: SQL query for report builder 3 weeks 6 days ago #114769

Greetings,


Do you have the mentioned features nowadays?.

Best,
The administrator has disabled public write access.

Re: SQL query for report builder 3 weeks 2 days ago #114827

  • redman
  • redman's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 3
  • Karma: 0
I am also very interested in this solution.
The administrator has disabled public write access.

Re: SQL query for report builder 3 weeks 2 days ago #114831

  • fransmarcelissen
  • fransmarcelissen's Avatar
  • OFFLINE
  • Gold Lime
  • Posts: 172
  • Thank you received: 33
  • Karma: 11
Hi,
I do'nt know whether I understand this questions correctly, but until recently I created reports automatically in R and in spss, after reading the database with an simple sql statement. I have created hundreds of reports in this way. This is also possible from spss (and probably also in pspp). The sql statement is simply SELECT * FROM `lime_survey_XXX` where XXX is the survey id. The disadventage of this is that the variable names are SGQ codes (something like 242445X263X2350). Another disadvantage is that in general reading the database is only possible if you are on the server where the database is installed).

Since some time I notices that remotecontrol is much simplier and useful. See manual.limesurvey.org/RemoteControl_2_API#export_responses. Using rc it is possible (for example in R, or php) to read the answers directly in a batch mode, with decent variable names. This data tables can directly be used in R/spss/pspp, or whatever statistical program or report generator you are using.

In my expercience R is much, much more useful than spss.
The administrator has disabled public write access.
The following user(s) said Thank You: Ben_V
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.144 seconds
Donation Image