Pagination in Looker

[bryce_heltzel]

Some old-school reporting use-cases may require viewing more than 5,000 records. Since Looker sets row limits for browser performance, we can leverage pagination to display these styles of reports.

Many databases including Snowflake, BigQuery, Redshift, and SQL Server support an OFFSET function to paginate through data. Using Looker’s Derived Tables and Parameters, we can select the fields from a table while only getting the number of results we specify from each page. Here’s the LookML for Snowflake’s syntax:

view: order_items {
  derived_table: {
    sql:
    SELECT * FROM PUBLIC.ORDER_ITEMS
    LIMIT {{ number_per_page._parameter_value }}
    OFFSET {{ number_per_page._parameter_value | times: page._parameter_value | minus: number_per_page._parameter_value }} ;;
  }

  parameter: page {
    type: number
  }

  parameter: number_per_page {
    type: number
  }

We can set the Page and Number of Records Per Page as user inputs via Filters.

You might even embed this report for a customer and use your own custom buttons to allow users to page through a report using the dashboard:filters:update JavaScript event.