Check out the LimeSurvey source code on GitHub!
Welcome, Guest
Username: Password:

TOPIC: custom reports and custom pages

custom reports and custom pages 4 years 5 months ago #77113

  • mmirasol
  • mmirasol's Avatar
  • Offline
  • Senior Lime
  • Posts: 42
  • Karma: 0
Hello guys,

What would be the best way to generate custom reports and custom pages from the data in lime? What I'm trying to do is as follows:

1. create custom lists of responses (for now just to display or export the respondents who passed screening and those that failed.)

2. create a translation page for submitted responses (both closed and open ended).

3. on the same page, a coding/tagging interface for translated responses to categorize them for reporting.

4. create custom reports based on the coded responses.

Thanks,
Mark
Last Edit: 4 years 5 months ago by mmirasol.
The administrator has disabled public write access.

Aw: custom reports and custom pages 4 years 5 months ago #77222

  • Steve
  • Steve's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 493
  • Thank you received: 46
  • Karma: 17
Hello Mark,

I'll move this thread to the "Development" forum, I think it's better to discuss it there.
Mit freundlichen Grüßen/Best regards,

Stefan Gohlke
LimeSurvey Team
Last Edit: 4 years 5 months ago by Steve. Reason: added missins s
The administrator has disabled public write access.

Aw: custom reports and custom pages 4 years 5 months ago #77225

  • mmirasol
  • mmirasol's Avatar
  • Offline
  • Senior Lime
  • Posts: 42
  • Karma: 0
thanks, steve. =)
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77336

  • tacman1123
  • tacman1123's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
I've been playing around with custom reports. The biggest challenge is the SGQA naming conventions. In version 2, a unique question code is likely to be required, I'm hopeful that the actual field names in the database will then be those codes, which will make external custom reports trivial: select * from results_<code>, then display the values you want.

Perhaps the YII architecture or the API will make custom reporting easier as well.

Tac
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77339

  • TMSWhite
  • TMSWhite's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 758
  • Thank you received: 83
  • Karma: 37
Tac-

Sounds like a good configuration option: give users the ability to specify whether to use SGQA or Qcode naming in the database. That's an easy change for the database, but would have broader implications for the statistics reporting and quotas.

We'd likely need to either change the variable names for the "reserved" fields in the database (like sid and submitdate) so that Qcode names don't overwrite those values.
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77342

  • tacman1123
  • tacman1123's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
perhaps the lime vars could be prefixed, e.g. lime_sid, lime_submitdate? Alternatively, there could just be a list of reserved words.

The bigger implication is for EM, but EM already supports question codes. I know a lot of JS exists using the SGQA naming, although I don't understand how those surveys are ever able to be moved.

I'm hoping that reports will be rewritten using MVC architecture anyway. I still haven't gotten a chance to dive into the Yii version yet. Is that being actively developed at the moment?

Tac
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77379

  • mmirasol
  • mmirasol's Avatar
  • Offline
  • Senior Lime
  • Posts: 42
  • Karma: 0
@tacman1123, thanks for bringing up the idea of the yii framework. =) is it possible to create the app externally instead with yii? i've only tried yii a little a bit. is it possible to create custom lists/forms from custom mysql in yii?
Last Edit: 4 years 4 months ago by mmirasol.
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77380

  • tacman1123
  • tacman1123's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
Two issues, not specific to Yii, but to any external program.
  1. Security: Not sure if/how to integrate LS tokens and security with an external app.
  2. Database: "Select * from <results_table>" gives you back a list of SGQA indexes to your data. You can write a custom report based on that, but then if you ever move it, or copy the survey, or whatever, the report won't work.

Tac
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77676

  • Mazi
  • Mazi's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 5977
  • Thank you received: 368
  • Karma: 260
How about an alternative approach: Convert response data in Limesurvey into a different table structure (one row per response with related data like SID, GID, QID, ...). This way you can get rid of the strange column naming at Limesurvey and can easily query data fro a certain survey, group or question.

Another approach might be to use some more tables, e.g. one table for the raw answer data with an ID pointing to table 2 which holds general information on related SID, GID, QID, parent_QID and the like. You can even have a thrid table with all the metadata like related token, submitdate, ref. URL, ...

In general it all depends on how you want to analyse data, which layout a certain tool needs to rely on and the like.

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.

custom reports and custom pages 4 years 4 months ago #77682

  • tacman1123
  • tacman1123's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
I've spent the last few hours poking around the Yii branch, trying to figure out a strategy for adding custom reports. The reason I'm using Yii is that it seems more likely to be included in the LS core, as I assume 1.92 is pretty much frozen.

Part of the issue is figuring out how to associate reports with specific surveys. I guess it could be done the same way that token attributes are done, a new field in the survey table that's a list of reports. The UI could then render that list for those reports.

For reporting, I prefer using Twig, and see that it can be easily integrated with Yii (yiiext.github.com/extensions/twig-renderer/index.html). Not sure the best way to get community buy-in that having Twig as an option is a good idea. I resisted it for a long time ("After all, php IS a templating language!"), but once I adopted it I became a huge fan, and it makes the MVC architecture easier to follow.

Eventually I'll figure out git and branches and just add it to my repository and do a pull request once it's working and can be justified.

Tac
Last Edit: 4 years 4 months ago by tacman1123. Reason: wrong msg
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77686

  • TMSWhite
  • TMSWhite's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 758
  • Thank you received: 83
  • Karma: 37
Mazi wrote:
How about an alternative approach: Convert response data in Limesurvey into a different table structure (one row per response with related data like SID, GID, QID, ...). This way you can get rid of the strange column naming at Limesurvey and can easily query data fro a certain survey, group or question.

Here is how I would recommend doing this. I've used this Entity-Attribute-Value approach for a decade, and performance is excellent (despite running on a low-end Amazon EC2 instance and having a 17 GB MySql database with over 200,000 responses and 70 million rows of data).
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77692

  • tacman1123
  • tacman1123's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
We used that approach for a massive project several years ago, 11 million documents and about 200 million document attributes. Indeed, performance wasn't a problem.

The other advantage to this approach is that it becomes simple to add repeatable groups. For example, if you have a group called 'child' with 3 questions, name, gender, birthday, and you want this to be repeatable. Currently after a lot of typing and renaming of fields, you end up with a big table with fields like child1_name, child2_name. Reporting on this is a mess.

But with Entity-Attribute-Value, you could have unrestricted repeatable questions, AND when you report on it, you'd be able to treat each "child" as an independent entity, not looping through to some artificial maximum. That is, you could rewrite a report that says "give me a list of all the children in this survey's responses."

Is this a possibility for 2.0? If so, I'd love to participate in the design discussion, we had done some preliminary work for our survey system, and I have some thoughts on integrating timing, survey versions, and the like.

Tac
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77694

  • TMSWhite
  • TMSWhite's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 758
  • Thank you received: 83
  • Karma: 37
Tac-

It would be great to have you involved.

2.0's feature list hasn't been locked down yet, so anything goes. Once we pass the lock phase, we'd need to focus on 2.1.

So, perhaps the best place to start would be for you to create a wiki page with a proposed data model so that we can discuss it. I'm especially interested in your ideas for unrestricted repeating groups.

EAV with qcode naming could also help another topic you raised - importing answers from one survey into a new one without having to worry about SGQA naming.
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77695

  • tacman1123
  • tacman1123's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 125
  • Karma: 1
I'm also wondering if people would be open to changing the surveys table to a EAV structure. There are 57 fields now, and of course there are more I'd like to to add. As an EAV, admins could add their own survey attributes. For example, I want to add "custom_reports". Instead of altering the data structure of surveys, I'd simply add a new record (not field) to survey_attribute_definition, which would be:

code: custom_reports
type: TEXT

There would probably be things like order, prompt, advanced, and maybe some validation rules, whatever.

But then it's active in the interface, without having to change the underlying code! You'd access it with some sort of method call, e.g. $survey->getAttribute('custom_reports').

Where the attribute is stored would depend on things like access speed, filtering and sorting. So expiration_date and is_public would probably continue to be stored in the surveys table (to make the query faster and easier) but custom_reports would be in the attribute data table (something like survey_attribute_data, or survey_attributes).

Tac
The administrator has disabled public write access.

custom reports and custom pages 4 years 4 months ago #77697

  • TMSWhite
  • TMSWhite's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 758
  • Thank you received: 83
  • Karma: 37
Another excellent idea. We've been using EAV for advanced question_attributes for a while now, and it makes it much easier to add new attributes, or associate existing attributes with new question types -- all without having to specify a data model upgrade. Taking a similar approach for general survey attributes would also be nice - especially if we changed over to use a model-driven GUI generation approach as was done for advanced question attributes. Take a look at the questionAttributes() function within common_function.php to see how it is done for advanced question attributes.
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.292 seconds
Imprint                   Data Protection Statement                  Revocation information and revocation form