Hello,

I am trying to create a chart of webform submissions following instructions from https://github.com/backdrop-contrib/charts#creating-charts-in-the-ui.

I started creating a view, then setting the format as Table, and selected a field with the data entered by users, consisting in two options. I set aggregation to grouping results together:

 

Then in the field Webform submissions: Sid I selected Count as aggregation function.

But the results are not aggregated and I obtain one row per submission.

And selecting the contextual link I don't see the aggregation in the field "Webform submission data: Valor".

I am writing to ask for any advice or suggestion about how to obtain the aggregation of results to make a summarizing table to use then in a chart as display format.

Thank you in advance!

Andrés

 

 

Accepted answer

Hello argiepiano,

thank you for your response. I will try this module.

About aggregation for charts, I was about to give up my search and I found this:

https://www.drupal.org/project/webform/issues/2097503#comment-8490501

that states "...When aggregating on submission data, you first need to add a relationship for the component you want to aggregate on. Then instead of using "Webform submission data: value" you should use "Webform submission data: value (raw)".

Then I selected the corresponding relationship, and configured my chart:

The view:

 

And obtained this graph:

And now the problem is how to change the labels 1 and 2 that should show text with the alternative options of the webform question.

I'll thank any suggestion about this.

Regards!

Andrés

   

 

 

Most helpful answers

Andrés, I was able to show the label rather than the numerical ID of the option.

I assume you are using a "Select options"  component and that you want to group by option selected.

Let's say I want to create a Webform with one component (Select option - "Favorite cartoon"), with two choices: Bug Bunny, and Tom & Jerry. I want to count how many people choose BB and how many T&J.

When creating the component, be sure to select "Customize keys" and enter the exact same text in the key as in the label. See image.

 

Then I created a View (please note that I'm using a regular "Page" since I haven't installed Charts, but the aggregation works the same way there).

Fields:

  • Webform Submissions: Serial Number
  • Webform Submission Data: Value (raw)

Relationships:

  • Webform Submission: Data
    • Settings: 
      • Component Data: Favorite cartoon (the name of the component)
      • Relate using: Component Form Key

Filters:

  • Just to be sure, I filtered by the NID of the Content that holds the Webform. Not sure if it's needed

Then I enabled aggregation. I chose COUNT for the first field, and Group results together for the second.

Result:

Then you can use Value (raw) as the source for your labels in the Chart. Let me know if that worked! 

(I have to clarify that even though the Column Header reads "Serial Number" in the image above, the numbers shown are actually COUNTS: I have 3 submissions with "Bugs Bunny" and only 1 with Tom & Jerry. Though I must confess I like T&J much better!!!)

EDIT: In the relationship, you can use any of the other options for "Relate using". It doesn't make a difference as long as the keys of your component are the same text as the label.

Hi aduhour.

The aggregation functionality of Views works by adding SQL aggregation functions to the database query, such as COUNT, GROUP BY and SUM. Unfortunately, because of the architecture of Webform (e.g. submitted webform component data are not stored in separate tables, but rather in one table, webform_submitted_data). This makes Views queries for Webform submissions very different from the types of queries that Views uses for "normal" Backdrop fields and entities. A lot of it, for example the separation of component data into different columns in the resulting View, is done post-query and pre-rendering. 

The authors of Webform managed to make available ways to include, sort and filter submissions, but I think they stopped short of making aggregation available, since SQL aggregation (with this architecture) is either impossible or very difficult.

I'd suggest you look into Views Aggregator Plus, which aggregates fields post-query. I've never tried it with webform, but it may allow you do bypass some of these limitations.

 

Comments

Hi aduhour.

The aggregation functionality of Views works by adding SQL aggregation functions to the database query, such as COUNT, GROUP BY and SUM. Unfortunately, because of the architecture of Webform (e.g. submitted webform component data are not stored in separate tables, but rather in one table, webform_submitted_data). This makes Views queries for Webform submissions very different from the types of queries that Views uses for "normal" Backdrop fields and entities. A lot of it, for example the separation of component data into different columns in the resulting View, is done post-query and pre-rendering. 

The authors of Webform managed to make available ways to include, sort and filter submissions, but I think they stopped short of making aggregation available, since SQL aggregation (with this architecture) is either impossible or very difficult.

I'd suggest you look into Views Aggregator Plus, which aggregates fields post-query. I've never tried it with webform, but it may allow you do bypass some of these limitations.

 

Hello argiepiano,

thank you for your response. I will try this module.

About aggregation for charts, I was about to give up my search and I found this:

https://www.drupal.org/project/webform/issues/2097503#comment-8490501

that states "...When aggregating on submission data, you first need to add a relationship for the component you want to aggregate on. Then instead of using "Webform submission data: value" you should use "Webform submission data: value (raw)".

Then I selected the corresponding relationship, and configured my chart:

The view:

 

And obtained this graph:

And now the problem is how to change the labels 1 and 2 that should show text with the alternative options of the webform question.

I'll thank any suggestion about this.

Regards!

Andrés

   

 

 

Andrés, great to know about the aggregation through relationship! If you figure out how to change the labels, please post here.

- Alejandro

Andrés, I was able to show the label rather than the numerical ID of the option.

I assume you are using a "Select options"  component and that you want to group by option selected.

Let's say I want to create a Webform with one component (Select option - "Favorite cartoon"), with two choices: Bug Bunny, and Tom & Jerry. I want to count how many people choose BB and how many T&J.

When creating the component, be sure to select "Customize keys" and enter the exact same text in the key as in the label. See image.

 

Then I created a View (please note that I'm using a regular "Page" since I haven't installed Charts, but the aggregation works the same way there).

Fields:

  • Webform Submissions: Serial Number
  • Webform Submission Data: Value (raw)

Relationships:

  • Webform Submission: Data
    • Settings: 
      • Component Data: Favorite cartoon (the name of the component)
      • Relate using: Component Form Key

Filters:

  • Just to be sure, I filtered by the NID of the Content that holds the Webform. Not sure if it's needed

Then I enabled aggregation. I chose COUNT for the first field, and Group results together for the second.

Result:

Then you can use Value (raw) as the source for your labels in the Chart. Let me know if that worked! 

(I have to clarify that even though the Column Header reads "Serial Number" in the image above, the numbers shown are actually COUNTS: I have 3 submissions with "Bugs Bunny" and only 1 with Tom & Jerry. Though I must confess I like T&J much better!!!)

EDIT: In the relationship, you can use any of the other options for "Relate using". It doesn't make a difference as long as the keys of your component are the same text as the label.

Hello Alejandro!

your comments were very helpful. Finally I could make the pie chart with the correct labels.

Because the form was already completed I changed directly in the database the values 1 and 2 with the text of the customized keys.

I used the following SQL query to replace the data stored as option '2' with the corresponding customized key (nid is the node id, cid is the component id):

UPDATE `webform_submitted_data` SET `data` = 'Tom & Jerry' WHERE `webform_submitted_data`.`nid` = '1234' AND `webform_submitted_data`.`cid` = 1 AND `webform_submitted_data`.`data` = '2'

and the chart looks like this:

And about cartoons I like T&J too!!

Thank you very much!

Andrés