How to create a chart from simple data without using the Views aggregation feature?

Question

I am trying to create a chart from really simple data. I am using the highcharts library and the Visualization API module and Views. It seems like the only type of graphs that I see examples of on the Internet involve aggregation. For example, someone wants to find the number of taxonomy terms used in the nodes of a particular content type and they use a pie chart to show the percentage (https://www.youtube.com/watch?v=ot2cFQVgzfA&spfreload=1).

The type of data I am trying to visualize is something like this.

  Year   Products
+------+----------+
| 2015 +    78    +
+------+----------+
| 2014 +    74    +
+------+----------+
| 2013 +   123    +
+------+----------+
| 2012 +   106    +
+------+----------+
| 2011 +    59    +
+------+----------+ 
|  ... +    ...   +

I want my x-axis to be Year and my y-axis to be Sales. My content type has each of these fields. In views I am only including the years field and the sales field, which are both integers.

If I view the data as an HTML Grid I see this at the bottom of the views page:

Title
Test

Content
2015
78

2014
74

2013
123

2012
106

2011
59

So it looks like view is showing both fields. But when I use Visualization (Highcharts) - Line Chart instead of HTML Grid I get:

The query produced by views is:

SELECT TOP(10) node.[nid] AS [nid], node.[created] AS [node_created], 'node' AS field_data_field_year_node_entity_type, 'node' AS field_data_field_products_node_entity_type
FROM 
{node} node
WHERE ( (( ([node].[status] = '1') AND ([node].[type] IN  ('year')) )) )
ORDER BY node_created DESC

This makes me think views cannot do a JOIN to get the results from both the node and the product field tables. Instead it is using node as a text value, not an integer, so the chart shows values of zero. Interestingly in views the HTML Chart format can get the values, just not the Visualization format.

How can I create a simple chart based on two columns of data using Highcharts and Visualization?

Here is the export of my actual view (some field names are different than my example):

$view = new view();
$view->name = 'test';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Test';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Test';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'field_year' => 'field_year',
  'field_agricultural_related_produ' => 'field_agricultural_related_produ',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'field_year' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'field_agricultural_related_produ' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
/* Field: Content: Year */
$handler->display->display_options['fields']['field_year']['id'] = 'field_year';
$handler->display->display_options['fields']['field_year']['table'] = 'field_data_field_year';
$handler->display->display_options['fields']['field_year']['field'] = 'field_year';
$handler->display->display_options['fields']['field_year']['label'] = '';
$handler->display->display_options['fields']['field_year']['element_type'] = '0';
$handler->display->display_options['fields']['field_year']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_year']['element_wrapper_type'] = '0';
$handler->display->display_options['fields']['field_year']['element_default_classes'] = FALSE;
$handler->display->display_options['fields']['field_year']['settings'] = array(
  'thousand_separator' => '',
  'prefix_suffix' => 1,
  'date_conditional_display_field' => '',
  'date_conditional_display_pre_text' => '',
  'date_conditional_display_post_text' => '',
  'linked_field' => array(
    'linked' => 0,
    'destination' => '',
    'advanced' => array(
      'title' => '',
      'target' => '',
      'class' => '',
      'rel' => '',
      'text' => '',
    ),
  ),
);
$handler->display->display_options['fields']['field_year']['field_api_classes'] = TRUE;
/* Field: Content: Products */
$handler->display->display_options['fields']['field_agricultural_related_produ']['id'] = 'field_agricultural_related_produ';
$handler->display->display_options['fields']['field_agricultural_related_produ']['table'] = 'field_data_field_agricultural_related_produ';
$handler->display->display_options['fields']['field_agricultural_related_produ']['field'] = 'field_agricultural_related_produ';
$handler->display->display_options['fields']['field_agricultural_related_produ']['label'] = '';
$handler->display->display_options['fields']['field_agricultural_related_produ']['element_type'] = '0';
$handler->display->display_options['fields']['field_agricultural_related_produ']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_agricultural_related_produ']['element_wrapper_type'] = '0';
$handler->display->display_options['fields']['field_agricultural_related_produ']['element_default_classes'] = FALSE;
$handler->display->display_options['fields']['field_agricultural_related_produ']['settings'] = array(
  'thousand_separator' => '',
  'prefix_suffix' => 1,
  'date_conditional_display_field' => '',
  'date_conditional_display_pre_text' => '',
  'date_conditional_display_post_text' => '',
  'linked_field' => array(
    'linked' => 0,
    'destination' => '',
    'advanced' => array(
      'title' => '',
      'target' => '',
      'class' => '',
      'rel' => '',
      'text' => '',
    ),
  ),
);
$handler->display->display_options['fields']['field_agricultural_related_produ']['field_api_classes'] = TRUE;
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'year' => 'year',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'testvisualization';

Answer 1

Part 1: From the Charts documentation

The tabular data for which you're trying to create a chart looks pretty similar to the tabular data of this variation (with it's corresponding area chart below it):

The above screenprint is from Preview 4: Area chart created with the views UI, which was created using the Charts module.

Preview 3 of that same community documentation page has another sample which shows a pie chart (created with either Google Charts or HighCharts as charting engine, which looks like so:

So if you're willing to consider using the Charts module it should be pretty forward to get this to work. If needed refer to its Community documentation for (way) more details.

If you still can't get it to work, then try the "bonus tip" mentioned in Steps to create charts using the views UI (= first create a tabular version of the data you want to chart, to make sure that the input for creating the chart is actually correct). That may help to refine your view before actually creating the chart.

Part 2: Online Charts demonstration

Example 1

Have a look at Single chart, not attached to a table, and without aggregation. Here is a quote from it:

The chart below (created using the "charts" module) shows all nodes of content type "Article", which has 2 fields with these "machine names" (+ values):

• "field_optionlist" (with some select list values, such as the "year")
• "field_free_format_text" (assumed to contain some numeric values)

I trust you agree it looks pretty close (identical?) to the chart you're trying to create. And in this case it does not (repeat: not) use any views aggregation facilities.

Here is the exported view that is used to create this (example 1) chart:

$view = new view();
$view->name = 'visualization_no_aggregation_ex1;
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Visualization no aggregation example1';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Single chart, not attached to a table, and without aggregation';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['style_plugin'] = 'chart';
$handler->display->display_options['style_options']['type'] = 'line';
$handler->display->display_options['style_options']['label_field'] = 'body';
$handler->display->display_options['style_options']['data_fields'] = array(
  'field_free_format_text' => 'field_free_format_text',
  'body' => 0,
);
$handler->display->display_options['style_options']['field_colors'] = array(
  'body' => '#2f7ed8',
  'field_free_format_text' => '#233a99',
);
/* Header: Global: Text area */
$handler->display->display_options['header']['area']['id'] = 'area';
$handler->display->display_options['header']['area']['table'] = 'views';
$handler->display->display_options['header']['area']['field'] = 'area';
$handler->display->display_options['header']['area']['content'] = 'The chart below (created using the "charts" module) shows all nodes of content type "Article", which has 2 fields with these "machine names" (+ values):

- "field_optionlist" (with some select list values, such as the "year")
- "field_free_format_text" (assumed to contain some numeric values)';
$handler->display->display_options['header']['area']['format'] = 'filtered_html';
/* Field: Content: Free Format Text */
$handler->display->display_options['fields']['field_free_format_text']['id'] = 'field_free_format_text';
$handler->display->display_options['fields']['field_free_format_text']['table'] = 'field_data_field_free_format_text';
$handler->display->display_options['fields']['field_free_format_text']['field'] = 'field_free_format_text';
$handler->display->display_options['fields']['field_free_format_text']['label'] = 'Products';
/* Field: Content: OptionList */
$handler->display->display_options['fields']['field_optionlist']['id'] = 'field_optionlist';
$handler->display->display_options['fields']['field_optionlist']['table'] = 'field_data_field_optionlist';
$handler->display->display_options['fields']['field_optionlist']['field'] = 'field_optionlist';
$handler->display->display_options['fields']['field_optionlist']['label'] = 'Year';
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'article' => 'article',
);
/* Filter criterion: Content: OptionList (field_optionlist) */
$handler->display->display_options['filters']['field_optionlist_value']['id'] = 'field_optionlist_value';
$handler->display->display_options['filters']['field_optionlist_value']['table'] = 'field_data_field_optionlist';
$handler->display->display_options['filters']['field_optionlist_value']['field'] = 'field_optionlist_value';
$handler->display->display_options['filters']['field_optionlist_value']['value'] = array(
  13 => '13',
  14 => '14',
  15 => '15',
);

/* Display: Chart */
$handler = $view->new_display('page', 'Chart', 'page');
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'chart';
$handler->display->display_options['style_options']['type'] = 'column';
$handler->display->display_options['style_options']['xaxis_title'] = 'Year';
$handler->display->display_options['style_options']['yaxis_title'] = 'Nr of Products';
$handler->display->display_options['style_options']['label_field'] = 'field_optionlist';
$handler->display->display_options['style_options']['data_fields'] = array(
  'field_free_format_text' => 'field_free_format_text',
  'field_optionlist' => 0,
);
$handler->display->display_options['style_options']['field_colors'] = array(
  'field_free_format_text' => '#7b0d49',
  'field_optionlist' => '#8bbc21',
);
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['path'] = 'single_chart_with_no_aggregation_not_attached_to_table';

Example 2

But wait, there is more ... i.e. another example. Actually a slightly enhanced version of the previous one. Have a look at Table with attached charts, and without aggregation. It's similar to the previous example, but on the same page it also includes that tabular view of the data in the chart, and it actually includes 2 variations of such chart (a line chart and a column chart).

Here is the exported view that is used to create this (example 2) chart:

$view = new view();
$view->name = 'visualization_no_aggregation_ex2';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Visualization no aggregation ex2';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Table with attached charts, and without aggregation';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['style_plugin'] = 'chart';
$handler->display->display_options['style_options']['type'] = 'line';
$handler->display->display_options['style_options']['label_field'] = 'body';
$handler->display->display_options['style_options']['data_fields'] = array(
  'field_free_format_text' => 'field_free_format_text',
  'body' => 0,
);
$handler->display->display_options['style_options']['field_colors'] = array(
  'body' => '#2f7ed8',
  'field_free_format_text' => '#233a99',
);
/* Footer: Global: Text area */
$handler->display->display_options['footer']['area']['id'] = 'area';
$handler->display->display_options['footer']['area']['table'] = 'views';
$handler->display->display_options['footer']['area']['field'] = 'area';
$handler->display->display_options['footer']['area']['content'] = 'The above table shows all nodes of content type "Article", which has 2 fields with these "machine" names (and their values):
- field_optionlist (with some select list values, such as the "year")
- field_free_format_text (assumed to contain some numeric values)';
$handler->display->display_options['footer']['area']['format'] = 'filtered_html';
/* Field: Content: OptionList */
$handler->display->display_options['fields']['field_optionlist']['id'] = 'field_optionlist';
$handler->display->display_options['fields']['field_optionlist']['table'] = 'field_data_field_optionlist';
$handler->display->display_options['fields']['field_optionlist']['field'] = 'field_optionlist';
$handler->display->display_options['fields']['field_optionlist']['label'] = 'Year';
/* Field: Content: Free Format Text */
$handler->display->display_options['fields']['field_free_format_text']['id'] = 'field_free_format_text';
$handler->display->display_options['fields']['field_free_format_text']['table'] = 'field_data_field_free_format_text';
$handler->display->display_options['fields']['field_free_format_text']['field'] = 'field_free_format_text';
$handler->display->display_options['fields']['field_free_format_text']['label'] = 'Products';
/* Sort criterion: Content: OptionList (field_optionlist) */
$handler->display->display_options['sorts']['field_optionlist_value']['id'] = 'field_optionlist_value';
$handler->display->display_options['sorts']['field_optionlist_value']['table'] = 'field_data_field_optionlist';
$handler->display->display_options['sorts']['field_optionlist_value']['field'] = 'field_optionlist_value';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'article' => 'article',
);
/* Filter criterion: Content: Free Format Text (field_free_format_text) */
$handler->display->display_options['filters']['field_free_format_text_value']['id'] = 'field_free_format_text_value';
$handler->display->display_options['filters']['field_free_format_text_value']['table'] = 'field_data_field_free_format_text';
$handler->display->display_options['filters']['field_free_format_text_value']['field'] = 'field_free_format_text_value';
$handler->display->display_options['filters']['field_free_format_text_value']['operator'] = 'not empty';

/* Display: Table */
$handler = $view->new_display('page', 'Table', 'page');
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'field_free_format_text' => 'field_free_format_text',
  'field_optionlist' => 'field_optionlist',
);
$handler->display->display_options['style_options']['default'] = 'field_optionlist';
$handler->display->display_options['style_options']['info'] = array(
  'field_free_format_text' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'field_optionlist' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['defaults']['header'] = FALSE;
/* Header: Global: Text area */
$handler->display->display_options['header']['area']['id'] = 'area';
$handler->display->display_options['header']['area']['table'] = 'views';
$handler->display->display_options['header']['area']['field'] = 'area';
$handler->display->display_options['header']['area']['content'] = 'The table below shows all nodes of content type "Article", which has 2 fields with these "machine names" (+ values):

- "field_optionlist" (with some select list values, such as the "year")
- "field_free_format_text" (assumed to contain some numeric values)';
$handler->display->display_options['header']['area']['format'] = 'filtered_html';
$handler->display->display_options['defaults']['footer'] = FALSE;
$handler->display->display_options['path'] = 'table_with_attached_charts_and_no_aggregation';

/* Display: Line Chart */
$handler = $view->new_display('attachment', 'Line Chart', 'attachment_1');
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'chart';
$handler->display->display_options['style_options']['type'] = 'line';
$handler->display->display_options['style_options']['label_field'] = 'field_optionlist';
$handler->display->display_options['style_options']['data_fields'] = array(
  'field_free_format_text' => 'field_free_format_text',
  'field_optionlist' => 0,
);
$handler->display->display_options['style_options']['field_colors'] = array(
  'field_optionlist' => '#008040',
  'field_free_format_text' => '#ff8000',
);
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['defaults']['header'] = FALSE;
/* Header: Global: Text area */
$handler->display->display_options['header']['area']['id'] = 'area';
$handler->display->display_options['header']['area']['table'] = 'views';
$handler->display->display_options['header']['area']['field'] = 'area';
$handler->display->display_options['header']['area']['content'] = 'Below is a <strong>chart</strong> about the very same data (as in the table above), in the format of the "<strong>line chart</strong>", and which is "<strong>attached</strong>" to the tabular view above.';
$handler->display->display_options['header']['area']['format'] = 'filtered_html';
$handler->display->display_options['defaults']['footer'] = FALSE;
$handler->display->display_options['displays'] = array(
  'page' => 'page',
  'default' => 0,
);
$handler->display->display_options['attachment_position'] = 'after';

/* Display: Column Chart */
$handler = $view->new_display('attachment', 'Column Chart', 'attachment_2');
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'chart';
$handler->display->display_options['style_options']['type'] = 'column';
$handler->display->display_options['style_options']['label_field'] = 'field_optionlist';
$handler->display->display_options['style_options']['data_fields'] = array(
  'field_free_format_text' => 'field_free_format_text',
  'field_optionlist' => 0,
);
$handler->display->display_options['style_options']['field_colors'] = array(
  'field_optionlist' => '#2f7ed8',
  'field_free_format_text' => '#233a99',
);
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['defaults']['header'] = FALSE;
/* Header: Global: Text area */
$handler->display->display_options['header']['area']['id'] = 'area';
$handler->display->display_options['header']['area']['table'] = 'views';
$handler->display->display_options['header']['area']['field'] = 'area';
$handler->display->display_options['header']['area']['content'] = 'Below is a <strong>yet another chart</strong> about the very same data (as in the table above), in the format of the "<strong>column chart</strong>", and which is "<strong>attached</strong>" (also) to the tabular view above.';
$handler->display->display_options['header']['area']['format'] = 'filtered_html';
$handler->display->display_options['defaults']['footer'] = FALSE;
$handler->display->display_options['displays'] = array(
  'page' => 'page',
  'default' => 0,
);
$handler->display->display_options['attachment_position'] = 'after';

For both examples above (the exported views): these are the changes you need to make before you'll be able to successfully import those views in your environment:

1. Adapt the machine names of the 2 field names to match your field names (which I saw in your exported view), ie:

   • change field_optionlist to field_year.
   • change field_free_format_text to field_agricultural_related_produ.

2. Adapt the content type's machine name from article to year (that's your machine name I saw in your exported view).

3. Pretty sure you do want to delete (remove) the line with /* Filter criterion: Content: OptionList (field_optionlist) */, and 8 lines following that line containing values like 13, 14 and 15 (I added that extra filter to limit my results to only 3 relevant "articles").

Disclosure: I'm a co-maintainer of the Charts module.