Export Submissions with one column per radio option

[philou]

Hi,
how would I go about exporting the submissions in an Excel layout where I have one column per option, as defined in my option lists for the form fields?

Example:
Question 1 – your favourite colour? Options: green, blue, red
Question 2 – are you vegetarian? Options: yes, no, sometimes

I would like to export the answers as follows (I am using newlines here to represent separate columns)

green:
blue: X
red:
yes:
no:
sometimes: X

Reason for this is, that I can more easily give my client an Excel sheet which they can add up by simply counting the cells with values. If we have a column with cells that contain either "green", "blue" or "red", counting the number of answers is difficult. The client wants excel and not a solution where they use the report tool inside formtools.

If it's best done via the smarty templates, then where can I find out what smarty tags to use to get to the options in an option list for a field?

Many thanks for any pointers!
philou

[philou]

I have solved it myself. A custom SQL query seemed the quickest way. In case someone else wants to achieve the same thing, below is the query. Just replace:
- your table name in the database
- names of your columns
- values of radio buttons

Code:

SELECT submission_id AS 'id',
    
    CASE WHEN question_1 = '1' THEN 1 END AS 'green',
    CASE WHEN question_1 = '2' THEN 1 END AS 'blue',
    CASE WHEN question_1 = '3' THEN 1 END AS 'red',

    CASE WHEN question_2 = '1' THEN 1 END AS 'yes',
    CASE WHEN question_2 = '-1' THEN 1 END AS 'no',
        CASE WHEN question_2 = '0' THEN 1 END AS 'sometimes'

FROM ft_form_1 WHERE 1

p.