DataTables with DataStore Source

  Disclaimer: only canada.ca and gc.ca domains are allowed to make cross origin requests.

Overview

DataTables is a popular open-source JavaScript library that enhances standard HTML tables with powerful interactive features. It allows for displaying large datasets in a user-friendly way without having to build table functionality from scratch. It turns a basic HTML table into a feature-rich, interactive data grid with minimal configuration.

DataTables can populate a table by making AJAX requests to a remote API instead of relying on data already present in the HTML. This allows it to display dynamic data from a database or web service and is especially useful for large or frequently changing datasets. The two common approaches are client-side processing and server-side processing.

Download & Install

DataTables offers a package builder which will help you gather all of the dependencies you need in your webpage.

DataTables Options

For detailed descriptions of the DataTables options, read their official Options guide, which will help you determine the settings for your desired table output. Note that different Plugins may add more options.

Client-side Processing 

With client-side processing, DataTables requests the data once from a remote API. After the data is downloaded, all searching, sorting, and pagination are performed in the user's browser. This approach is ideal when the dataset is relatively small (hundreds or a few thousand rows) and the API can return the entire dataset efficiently.

Resource: Open Government Portal Department List - Government of Canada Department List

Client Side DataTables w/ DataStore Search

javascript

Server-side Processing 

For very large datasets, DataTables can operate with server-side processing. Instead of downloading every record on page load, DataTables sends a request every time the user interacts with the table, such as paging, column ordering, or searching. This allows DataTables to efficiently work with datasets containing millions of records while transferring only a small amount of data for each interaction.

Known Limitations

Though the DataTables library is robust and offers a lot of functionality and plugins, not all of these are supported in the Open Data API. Here are some of the common limitations to the datastore_search API endpoint:

  • Maximum records per page is 32,000
  • Full text search is disabled for data over 100,000 records (q parameter in datastore_search, search parameter in DataTables)
  • Column filters are only exact match (filters parameter in datastore_search, ColumnControl search in DataTables)
  • Does not support querying like "greater than" or "less than", date ranges, or "starts with" or "ends with"
  • Does not support boolean querying like "does not contain"

Resource: Proactive Publication - Contracts - Contracts over $10,000

Server Side DataTables w/ DataStore Filters

javascript

Data Dictionary, Cell Types, & Cell Renderers 

DataStore resources may include a Data Dictionary that describes the fields in a dataset, including each field's name, labels, and data type. If a Data Dictionary has been added to a resource, this metadata can be used to determine how each column should be handled when displaying the data in DataTables.

By mapping the field types to DataTables column types, the table can apply appropriate sorting and searching behaviour. For example, treating integers and decimals as numeric values, dates as dates, and text fields as strings.

The field metadata can also be used to determine cell renderers. For example, URL fields can be rendered as clickable links, boolean values can be displayed as readable labels or icons, and date fields can be formatted into a more user-friendly format.

This allows a DataTables implementation to be dynamically generated from the DataStore schema, rather than requiring each dataset's columns and rendering rules to be manually configured.

For all of the supported data types in DataTables, view their official documentation.

Resource: Open Government Analytics - Number of Visits, Downloads

Client Side DataTables w/ Data Dictionary

javascript

Row Grouping & Summary Statistics Row 

The DataTables RowGroup extension adds the ability to organize table rows into groups based on the values of one or more columns. Instead of displaying a flat list of records, related rows can be visually grouped under a shared heading.

For example, a dataset could be grouped by Province, with each province displayed as a separate section containing its corresponding records.

RowGroup can also be used to add group headers and footers, allowing additional information such as group labels, counts, or summaries to be displayed.

It is particularly useful for datasets where records naturally belong to categories or hierarchical groups, making large tables easier to scan and understand.

Resource: Open Government Analytics - Open Maps Views
Filtered to show "Environment and Climate Change Canada" records, grouped by year. Has customized RowGroup renderers to render expand sections and summary rows for the groups.

Client Side DataTables w/ RowGroup & Custom Summaries

javascript