Customized individual response reports

More
2 years 5 months ago #129710 by ervit
ervit created the topic: Customized individual response reports
Hi

I have a survey where the responses are always reviewed on an individual basis. As such, I need a way to review a completed response in a document (can be HTML, DOC or PDF). LimeSurvey's bult-in tools always generate a table with all questions and subquestions. As a result, with 300 questions, I get 1400 data points, many of which are empty (a lot of questions are conditional and are not even shown to respondents based on their previous answers, so I don't want those in the report) or N/A. (also, in checkbox questions, i would like to ignore the unchecked ones reported as "No") In addition, Expression Manager {expressions} in question text are not interpreted when viewing results (some questions are customized based on previous answers), and the formatting of exported documents is fixed. As such I am looking for solutions to the following issues:
1. Export/View *only answered* questions and sub-questions;
2. Enable Expression Manager {expressions} interpretation in response views;
3. Enable customized formatting of such outputs.

The format of the PDF export works best for me as it shows answers *under* the question, not side by side, as in Word export or online views, but I'd like to do away with the table containers for each question and, as mentioned above, remove questions with no answers.

To push this further and as a separate feature, it would be great to allow fully customized reports, i.e. where answers could be presented in the order specified by us. (this is probably too much to ask, but it would be nice to have)

I guess my question is as follows:
1. Is there anything with this functionality available? (maybe I have missed it in the settings/manual). and if not,
2. Are there any tools or approaches that can help me in my quest?

Perhaps it is possible to modify existing procedures in php files to obtain these results? I'm zero in php, unfortunately, so will need some guidance. Maybe there are some automated tools out there to make it easier?

Please Log in or Create an account to join the conversation.

More
2 years 5 months ago #129717 by ervit
ervit replied the topic: Customized individual response reports
Now that I've hit a bug with result export function ( bugs.limesurvey.org/view.php?id=10200 ), I think the best option would be to build on the code that shows individual response table in response browser. Can anyone tell me which php file generates this report and where this file resides? Perhaps I can modify it to do what i need?

Please Log in or Create an account to join the conversation.

More
2 years 5 months ago - 2 years 5 months ago #129738 by holch
holch replied the topic: Customized individual response reports
Limesurvey is mainly a survey tool, which by default is made for analyzing many answers on an agregated basis. So while you can see individual responses, this is not the focus of a survey tool. So you will have to compromise here.

You might be able to send individual responses via email (via the advanced admin notification email) and include responses into this email via the Expression Manager.

If you are "zero" in PHP, then modifying existing procedures will not be an option, I fear. "Guidance" for this would rather mean a full blown PHP crash course than a quick help in the forum.

I'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds
Last Edit: 2 years 5 months ago by holch.

Please Log in or Create an account to join the conversation.

More
2 years 5 months ago #129746 by ervit
ervit replied the topic: Customized individual response reports
Thanks Holch,

The email option that you mention, does it have any other option besides:
1. using {ANSWERTABLE} to aggregate all answers; or
2. customizing it question by question through Expression Manager? (will the email template even allow so much text/code?)

Is this the only way to access the results with Expression Manager? Can I trigger the email from the admin panel somewhere? I will need to test it a few times and it seems I will have to submit a survey to generate the email.

Is there a way to generate the {ANSWERTABLE} with the following options:
1. Leave out unanswered questions;
2. present array questions in a table as in the original question rather than as a collection of separate questions/answers?

While I'm new to php, i am no stranger to coding in other environments and I learn quickly from examples. I just don't want to learn the whole of php for the sake of a few small (hopefully) changes. :) At the very least it would be helpful to know which files can be modified (perhaps made into plugins/extensions) to achieve what i'm looking for. I understand that this is free software with no strings attached so hoping people can provide some pointers...

Please Log in or Create an account to join the conversation.

More
1 year 11 months ago #138978 by Moonlight7
Moonlight7 replied the topic: Customized individual response reports
I'm interested in same feature. I wonder how you ended up achieving it if at all @ervit

Please Log in or Create an account to join the conversation.

More
1 year 11 months ago - 1 year 11 months ago #138994 by lintu
lintu replied the topic: Customized individual response reports
Hi Ervit/Moonlight7,

I have the same requirement too. I have just installed LimeSurvey and will do further research on how to accomplish this. Let's all share our findings and best options here.

Thanks

P.S. I just noticed that this thread is at least 6 months old. Have you identified a solution for this?
Last Edit: 1 year 11 months ago by lintu.

Please Log in or Create an account to join the conversation.

More
1 year 11 months ago #139005 by ervit
ervit replied the topic: Customized individual response reports
Hello guys,

I ended up doing a few macros in Excel that take the logic table and the results table and then put all responses in a more readable format (where 2D arrays are shown in a table, for example, as opposed to the crude linear output given by LimeSurvey). The macros then can export this into a formatted HTML file. It's not fancy, but it does the job for me. I only finished fixing bugs last week, and it doesn't process all types of questions, probably about half of them - the ones I use in my survey. I am going to post it on the forum with some instructions, but I need to make sure those instructions are clear, as it's not a "product", I just did it for myself, so there are a lot of shortcuts

Please Log in or Create an account to join the conversation.

More
1 year 11 months ago - 1 year 11 months ago #139019 by ervit
ervit replied the topic: Customized individual response reports
So here is my solution to my problem :)

DISCLAIMER: this is provided as is, without any warranties or support. Use at your own risk! Back up all data before using!

I made an Excel file with macros that achieve most of what I need through a series of steps. Unfortunately it's not a fully automated solution and some manual operations are still required, but these are warranted in my case as I have over 350 questions in my survey and this saves a lot of time for me. Note that the file only works with a single response. This is not intended for multi-response processing.

The Excel file relies on macros so these must be enabled to be useful. The file can be renamed any way you like. I made the file in Excel 2016, but I suspect it will work in 2013 and 2010, and maybe 2007. It probably won't work in versions prior to 2007, although it might (previous versions don't have the Ribbon interface, where I put the macro buttons).

So here is what I do to achieve a well-formatted export of a response.

Open the Excel file and enable its macros (note that you should always have macros disabled by default). If Excel is not prompting you to enable macros and they don't work, you need to adjust its settings (Google for enabling macros in Excel).

Note: the sheet called Libraries lists question types and the column called Text actually shows which question types the macros will process. The other question types will probably not be processed correctly.

Part 1. Set up the file for a particular survey.

Step 1. In your LimeSurvey, open the "Survey Logic file"
Step 2. Copy the logic file table. I use the Table2Clipboard addon for my Firefox browser for this as it has an option to "Copy whole table"
Step 3. Open the Logic_file sheet in the Excel file and paste the logic file table in cell A1. Excel will show a little icon in the bottom right corner saying "(Ctrl)"; if you click it or press the Ctrl key, it will expand into two options, the last of which is "Match Destination Formatting (M)". This is the one you need to click. This is important because it unmerges any merged cells, removes any images and extra formatting.
Step 4. On the Ribbon toolbar, find the "Custom Tab" tab, click it and there click the third smiley face that says "Generate question list". Excel will ask you if you're sure, press "Yes". Give it a few seconds (minutes) to do its thing. Now you're ready to actually work with responses.

You will need to repeat Part 1 for each new survey or whenever you modify a survey. Once Part 1 is done, you can use it to process multiple responses without repeating the steps above.

Part 2. Work with responses.

Step 1. Go to your LimeSurvey and open (view) the response that you want to process. LimeSurvey will open a table with that particular response. It will have 2 columns:on the left there will be [Question code] + "Question text"; and on the right it will have the answers. You need to copy this table the same way as the logic file table above.
Step 2. Open the RawExport sheet in the Excel file and paste the table into cell B1. Note that it has to be B1, not A1. The first column must remain empty! As with the Logic file table above, select "Match Destination Formatting" in Paste Options. If you're processing multiple responses, just clear the sheet RawExport before pasting a new response (i.e. delete everything on the sheet by selecting all and pressing the Delete key).
Step 3. In the Custom Tab on the Ribbon toolbar, click the "Generate Excel Output report" button (the first smiley face on the left). Give it time to do its thing, it might take a while. The Output sheet will be filled with responses presented in a more or less readable form, with unanswered questions mostly removed and tabular data tabulated. But since this is not very readable, there's one more step remaining, see below.
Step 4. In the same toolbar, click the smiley face in the middle - "Generate HTML report". It will create a file called MyHTML.htm in the same location as your Excel file with exported and formatted results. Before you do it, make sure the attached CSS file (bootstrap.css) is in the same folder as the Excel file, or you will get a "File not found" error. The CSS file is only needed to generate the HTML report. You can actually use any other CSS file you want, as long as you call it bootstrap.css, although I have hard-coded some CSS class attributes in VBA (not a major problem). The CSS will be embedded into the MyHTML.htm file so you won't need the bootstrap.css file to read the HTML file.

This is it

Disclaimer #2: Excel is a registered trademark/product of Microsoft Corporation; bootstrap.css is the work of the nice people from getbootstrap.com.
Attachments:
Last Edit: 1 year 11 months ago by ervit.

Please Log in or Create an account to join the conversation.

More
1 year 11 months ago #139065 by lintu
lintu replied the topic: Customized individual response reports
Hello ervit,

Great work and thanks for taking the time to share with all the detailed instruction!

Take care

Please Log in or Create an account to join the conversation.

More
1 year 11 months ago #139066 by ervit
ervit replied the topic: Customized individual response reports
Well I hope it helps other people

Please Log in or Create an account to join the conversation.

More
1 year 11 months ago #139260 by ervit
ervit replied the topic: Customized individual response reports
I have improved this a little. Now the HTML file also includes JS code (Bootstrap and jQuery) that adds a fixed navigation bar at the bottom that is collapsible and tracks your position within the report. Now you can jump to a specific group without having to scroll (my reports are very long and have 19 question groups, so this helps). This extra code adds weight to the file, but I'm using minified versions, so the total additional file size is a few hundred kilobytes. You will need all three additional files (in addition to the Excel file) for this to work.
Attachments:

Please Log in or Create an account to join the conversation.

More
1 year 10 months ago #139862 by ervit
ervit replied the topic: Customized individual response reports
Here's a fixed Excel file. I hadn't realized that there was some VBA code specific to my survey, so I wrapped it in error handling. Seems to work with other surveys now. I guess the fact that no one has complained to date means no one cares, but here, just in case someone decides to give this a try.

I also moved the navbar to top and removed the 100% table width in HTML file, so that tables are not stretched across the entire screen.
Attachments:

Please Log in or Create an account to join the conversation.

More
1 month 1 week ago #168568 by alanrcooke
alanrcooke replied the topic: Customized individual response reports
Hi Ervit - thanks very much for this post which has saved me a lot of pain and grief!

I have run into a problem when I run it in Excel - hope you can help.


After pasting logic file in cell A1 (unmerged) and then hitting smiley face to generate questions - I get the following debug error when macro won't run - "target_sheet.UsedRange.Cells.Delete 'clear the target sheet" ??

Can you pls advise what this means / how I might fix it so that the macro will run as intended etc.

Thanks again - Alan

Please Log in or Create an account to join the conversation.

More
1 month 6 days ago #168577 by ervit
ervit replied the topic: Customized individual response reports
Hi Alan,

It's hard to debug without working with exactly the same data.

Let's try this: before running the "Generate question list" macro, look in the Qlist sheet. If there is anything at all there (for example, an empty formatted table), delete those columns, so that you're left with an empty sheet. Then try running the macro. Please let me know if this helps or not.

Please Log in or Create an account to join the conversation.

More
1 month 6 days ago #168578 by alanrcooke
alanrcooke replied the topic: Customized individual response reports
Hi Ervit - thanks for getting back to me - and yes this did work !

When I opened the Qlist tab it already had headers in each blank column - Question number; question code; etc. - so I assumed they were supposed to be there - when I deleted them the macro worked a treat!

I am building up some test data in my survey - so haven't checked the rest yet - but I am sure it will be fine.

Thanks again - and if I do run into any more snafus I'll drop you a quick post.

Cheers - Alan

Please Log in or Create an account to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!