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

TOPIC: SQL query for report builder

Re: SQL query for report builder 3 years 1 month 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 1 month 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 1 month ago #65156

  • c_schmitz
  • c_schmitz's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 789
  • Thank you received: 113
  • Karma: 92
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 1 month ago #65157

  • c_schmitz
  • c_schmitz's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 789
  • Thank you received: 113
  • Karma: 92
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 1 month 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 1 month ago #65159

  • c_schmitz
  • c_schmitz's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 789
  • Thank you received: 113
  • Karma: 92
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 1 month 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.
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.184 seconds
Donation Image