categoryPicker filterColumn for AnnotatedTimeLine

[Dan]

Hello,

I was trying to use the categoryPicker dashboard item on an AnnotatedTimeLine. It doesn't lend itself very well to this time of graph however because for the annotatedtimeline data the columns are always only going to contain either a date or a numeric value. The categories that I want to filter on are in fact the column headers but this isn't an option.

I think I'm left with two choices:

1. return the datatable where the categories are listed in a column, and then create a separate dataview for the annotatedtimeline which has the correct format it expects.

2. As I am using the Query Language and getting the data from an MSSQL database, create my own catagoryPicker which alters the query depending on what is selected and then requeries and redraws.

Option 2 seems to have the benefit of only getting the data from the database required, however does need to re-query which option 1 does not have to do, just filter and redraw.

Have you got any advice on the best way to go with this, or do you think that a future version of the dashboard controls will allow for filtering on the Column headers rather than the values of a single columns (which would seem particularly useful for the annotated timeline).

Thanks,
Dan

[asgallant]

You can create your own filter for this using standard HTML elements (dropdowns, checkboxes, whatever), and create a DataView of the DataTable, selecting columns according to what the user selects with your control.

[Dan]

As I thought I won't be able to use the dashboard categoryPicker.

The question is if I use my own filter is it better that this alters the query and returns a new datatable OR query all the data and then use the filter to create a view with only the selected columns.

Not sure which approach is best?

[asgallant]

There is no one "best" approach.  It probably depends on the size of your data set, what the initial state will be, and how users are likely to use the control.  If you are initializing the chart with all columns, then use a view to filter.  Otherwise, if you have a mid-to-large size data set with 10 columns, and users only want 1 or 2 at a time (or something similar), then requerying is probably faster and less resource intensive.  Small data sets are probably better to query once and filter with a view.

[asgallant]

The frequency of querying also comes into play.  If users are going to end up viewing most, if not all, of the columns in a single session, then query once and be done with it.

[Riccardo Govoni ☢]

I understand you're trying to perform column-based filtering on the series your annotated timeline will display. 

This is not directly supported by the 'controls' API (which was initially designed for row-based filtering) but there are workarounds for this.

Have a look at this thread where I discussed the same issue: https://groups.google.com/d/topic/google-visualization-api/fhwUv_QKfWA/discussion and proposed a working example.

Also consider that the 'controls' API at the moment works fully client-side (you have to load the entire table and the controls take care of selecting the right views based on user input ). 

We are currently collecting usage feedback while the api is still marked as 'experimental' , so if you have a specific scenario, feel free to describe it (as said, we're already aware that column filtering would be nice to have).

-- R.

On 23 September 2011 17:29, asgallant <drew_g...@abtassoc.com> wrote:

The frequency of querying also comes into play.  If users are going to end up viewing most, if not all, of the columns in a single session, then query once and be done with it.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/RW5HHfEIl8EJ.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[Dan]

Thanks Riccardo,

I'll take a look at the thread. I'm still undecided whether filtering or re-querying is the best method for me. I'm hoping to replicate something like the google finance graph where you can search for a stock and add to the graph which I guess does a re-query each time.

If I can get the workaround to work I may do a hybrid depending on what filter is being applied. Using the dashboard for small filtering and re-query if the dataset would change much more.

[Dan]

Hi Riccardo,

I can see what you have done, but think this only works for using the slider. You mention in the other thread that you have to specify a filterColumnIndex as it is mandatory but it won't be used. However as I want to use the controlPicker control and this will make use of the filterColumnIndex value, as all the values in the dropdown box will still be that of the column rather than the contents of the header row.

I think I'll have to build my own control until the ability to use control filters to filter columns rather than just rows is available. Do you think this is something that will be available in a later release?

One other thing, I should probably create a new thread for it. Is the Query Language likely to be able to join between multiple tables in the future?

Thanks,

Dan

[Riccardo Govoni ☢]

When you said "The categories that I want to filter on are in fact the column headers" I thought  you wanted to use a CategoryFilter to decide which columns to retain of the input DataTable. Is that correct ?

If so, can't you operate this way: Given the input datatable,

- extract all column headers from it

- create a CategoyFilter and use the extracted column headers for its 'values' option. This will let you pick among column headers.

- forget the filterColumnIndex parameter as said in the other thread.

- listen for statechange events. Once one comes in, use getState() to get which column headers the user has picked, map them to column positions, create a suitable view and feed it to the Annotated timeline as shown in the other thread.

Granted, it's still a workaround that only partially uses the API, so it's debatable whether it's better or not than rolling your own custom thing, but doesn't it do the job?

Or are you trying to do something different by populating the CategoryFilter with all the contents of a specific column?

-- R.

Thanks,

Dan

--

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/qKupNYKfJ-sJ.

[Dan]

Thanks again. I have this working. For anyone interested in doing the same for an annotated timeline this is what I have done:

function handleQueryResponse(response) {

    if (response.isError()) {

      alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

      return;

    }

    var data = response.getDataTable();

    var headerView = new google.visualization.DataView(data);

    headerView.hideColumns([0]);

    var custs = new Array();

    for (var i = 0; i < headerView.getNumberOfColumns(); i++) {

        custs.push(headerView.getColumnLabel(i));

    }

    var categoryPicker = new google.visualization.ControlWrapper({

    'controlType': 'CategoryFilter',

    'containerId': 'control1',

    'options': {

      'filterColumnIndex': 0,

      'values': custs,

      'ui': {

        'allowMultiple': true,

        'selectedValuesLayout': 'belowStackeD'

      }

    },

    // Define an initial state, i.e. a set of metrics to be initially selected.

    'state': {'selectedValues': ['XXXXI']},

    'dataTable': data

  });

  var timeChart = new google.visualization.ChartWrapper({

    'chartType': 'AnnotatedTimeLine',

    'containerId': 'timechart_div',

    'dataTable': data

  });

  function onControlStateChange() {

       var filterCusts = [0];

       var columns = categoryPicker.getState();

       console.log(columns);

       for (var i = 0; i < columns.selectedValues.length; i++) {

           for (var j = 0; j < data.getNumberOfColumns(); j++) {

               console.log(data.getColumnLabel(j));

               console.log(columns.selectedValues[i]);

               if (data.getColumnLabel(j) == columns.selectedValues[i]) {

                   filterCusts.push(j);

               }

           }

       }

       console.log(filterCusts);

       timeChart.setView({'columns': filterCusts});

       timeChart.draw();

   }

   google.visualization.events.addListener(categoryPicker, 'statechange', onControlStateChange);

   timeChart.draw();

   categoryPicker.draw();