1

I am using Drupal 7.54. and my users are grouped together through a custom user field.

I want to export all webform's submissions for a given group of users in one CSV.

So as suggested in the post How do I programmatically export all webform submissions into a single csv?, I plan to use a custom datablock that fits my need.

I am new to Drupal and don't have much knowledge about how it works and how Drupal stores custom fields in the database.

I couldn't build the right SQL.

What would be the appropriate sql statement in this context?

2

Have a look at this example SQL, which is a so called "Data Block" I created using the Forena module (disclosure: I'm a co-maintainer), and starting from relative path admin/structure/forena/data:

SELECT s.nid AS node, s.sid AS submission, d.cid AS component, d.data AS fieldvalue
FROM {webform_submissions} s JOIN {webform_submitted_data} d ON s.sid=d.sid 
WHERE d.cid=3 AND s.nid=5
ORDER BY s.sid ASC

Some details about the example I'm using here:

  • 5 in the WHERE-clause is the node id of the webform (which I can edit via node/5/edit). For this webform, I want to query some of its submissions (= results (which I can browse via node/5/webform-results).
  • 3 in the WHERE-clause is the component id of the webform (which I can edit via node/5/webform/components/3). This particular component is configured as an eMail field, and these collected eMail IDs is what I want to export to a CSV file.

Adapt the hardcoded values 3 and 5 (in the WHERE clause) to fit your requirements. Or transform them in parameters contained in a data block (use this link if you wonder how to do that). Then save this data block with whatever name you want, eg drupal_cust/webform_submissions_data.

Use it as a prototype to build your custom report from it, using the Forena Report designer in which you select the webform_submissions_data data block mentioned above. Which just a few click you could create a report with a "layout" for which the design (in XHTML format) looks like so (also note the "tokens" shown within the td-tags):

<body>
  <div id="webform_submissions_data_block" class="FrxTable" frx:block="drupal_cust/webform_submissions_data">
    <table>
      <thead>
        <tr>
          <th>node</th>
          <th>submission</th>
          <th>component</th>
          <th>fieldvalue</th></tr></thead>
      <tbody>
        <tr id="webform_submissions_data" frx:foreach="*">
          <td>{node}</td>
          <td>{submission}</td>
          <td>{component}</td>
          <td>{fieldvalue}</td>
        </tr>
      </tbody>
    </table>
  </div>
</body>

Don't forget to select the "document types" you want to enable (to export the report), such as "CSV" (as in your question).

For the sake of completeness: the "layout" of a report is what is contained with the <body> and </body> tag. Similar to a regular HTML file, Forena will add an appropriate <head> and </head> tag in front of it, and surround everything with <html> and </html> tag (and also adds a few more lines at the beginning, to make it an XML file. So that when you save your report specifications, the entire file gets saved in your file system as a so called FRX file (FRX = Forena Report XML). For the above report layout, this is how the corresponding FRX file looks like:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE root [
<!ENTITY nbsp "&#160;">
]>
<html xmlns:frx="urn:FrxReports">
<head>
<title>Webform Export</title>
<frx:category>Webform</frx:category>
<frx:options/>
<frx:parameters>
</frx:parameters>
<frx:docgen>
  <frx:doc type="csv"/>
  <frx:doc type="pdf"/>
</frx:docgen>
<frx:fields>
</frx:fields>
<frx:menu/>
<frx:cache/>
<style/>
</head>
<body>
  <div id="webform_submissions_data_block" class="FrxTable" frx:block="drupal_cust/webform_submissions_data">
    <table>
      <thead>
        <tr>
          <th>node</th>
          <th>submission</th>
          <th>component</th>
          <th>fieldvalue</th></tr></thead>
      <tbody>
        <tr id="webform_submissions_data" frx:foreach="*">
          <td>{node}</td>
          <td>{submission}</td>
          <td>{component}</td>
          <td>{fieldvalue}</td>
        </tr>
      </tbody>
    </table>
  </div>
</body>
</html>

For the report we designed here, apart from that table in the report body, about the only extra spec we entered in this example is that we want to enable export options for format "CSV" and "PDF". That's what's indicated via what's between the <frx:docgen> and </frx:docgen> tag. And since we don't use things like report parameters, menu items, special fields, etc, the corresponding frx tags (frx:parameters, frx:menu, frx:fields, etc) have no values specified in FRX file.

Bonus tip: for this scenario, because of the hardcoded values 5 and 3 (in the WHERE clause of the SQL), the report columns titled node and component are always the same (= 5 and 3). So if you'd want to quickly get rid of those columns in the report, just delete their corresponding lines in the FRX files (with the th and td tags), using your favorite editor (no need to go back to the WYSIWYG report editor, unless you prefer to use that editor). Save the FRX file, and refresh your report ... done.

That's about it ... Sorry the above doesn't give you an idea about how it is like to prototype your data block, or to design your actual report using that data block.

1

Thank you Pierre.Vriens for your detailed answer.

After some digging in the Drupal database I found the SQL statement to build a datablock with Forena that fits my need.

Here it is if someone have a use case close to mine.

SELECT s.nid AS node, s.sid AS submission, d.cid AS component, d.data AS fieldvalue
FROM users u JOIN webform_submissions s ON u.uid=s.uid
JOIN webform_submitted_data d ON d.sid=s.sid
JOIN field_data_field_groupe f ON u.uid=f.entity_id
JOIN taxonomy_term_data t ON t.tid=f.field_groupe_tid
WHERE t.name=:grpname

field_data_field_groupe is a custom user field linked to a taxonomy_vocabulary.

In the WHERE-clause i am using a parameter ":grpname" wich let me specify the group. Then it will export all submissions of users associated to the ":grpname" parameters.

Then i can use the url below to set up button or anything i need to download all user's submissions associated to my parameter.

http://localhost/drupal-7.54/reports/rapport360.xls?t.name=group1

Here the file format is xls and the parameter is: "group1" as you can see.

tips:

  1. You can use tokens for the parameters.
  2. Evoid using a string as parameter to evoid transliterate problems.
  • Thank you for sharing this answer also! Maybe you should improve your answer to also explain that ":grpname" in your WHERE clause? – Pierre.Vriens Feb 28 '17 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.