Business Intelligence Drupal

Drupal: Filters for External Data Views

A Drupal View that uses Views Database Connector (VDC) to show external database tables will not have all features of a “normal” View. For example, select filters are only available for list fields, references and taxonomy terms. In this post I use two modules to improve the exposed filters in my external data View. In my previous post on using Drupal as Business Intelligence (BI) platform, I used VDC to display data from an external database table. I also created a text box filter on the City field. This is the only type of filter I could create. A text box filter is efficient but not user friendly for two main reasons:

  • User has to know what possible values are stored in column.
  • User has to spell the value correctly.

It is better to give the user a list of distinct values to select from or allow the user to search for the value they would like to filter. I installed two modules that gave me the required functionality. The Views Selective Filter and Views Autocomplete Filters modules.

Views Selective Filter

Quoting from the module home page, “the Views Selective Filter works by selecting a distinct list of items from a field included in the display”. I would not advise you to use this module if you are querying a field with hundreds of distinct values. The auto complete or search filter discussed further down is a better option.

Putting this module to use was straight forward. First I downloaded and and enabled the module. The module has no dependencies besides Views. I then clicked on the Add filter button. For each field there were now two options for adding a filter. For the City field I had the option to add a normal City filter or City(Selective) filter. I chose the selective filter.

Views Selective Filter

Each Views Selective Filter is exposed by default. There are options to sort the values and limit the number of values included as select options. I left the default values. If you have a field with hundreds of possible values I would advise you not to use this module. It does no make sense to have a user scroll through hundreds of options using a select box. The SQL query along with the time it will take to render a select list with hundreds of options will result in a very slow response times for the page.

Views Selective Filter View

Views Autocomplete Filters

Installation of the modules was also straight forward with no dependencies besides Views. Once enabled I didn’t have to create a new filter. I modified my existing text filter which now had a new option, Use Autocomplete. The auto complete the filter worked quite fine on the VDC external database table View.

Views Autocomplete Options

This filter enables the user to search for values to filter on. Once you start typing the filter kicks into gear and displays possible matches. This is better than a text box filter which forces the user to have to know the values they would like to filter on beforehand. Below is a image illustrating the auto complete filter in sue.

Views Autocomplete Filter

Drawing coming to a conclusion based on data at hand is hard enough without having to struggle to get the data. If getting the data is a challenge in the first place then forget about having a successful BI implementation. Users will not use the data if they are forced to manually fill in filter values. Adding a selecting prompt or auto complete prompt will help with the usability of your BI implementation and go along wards towards making you Drupal BI solution a success.

Leave a Reply