Looker Community

Pagination in Looker

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.

4 Likes

Love this solution. I was wondering how I might dynamically limit the number of pages that the user can enter to only valid page numbers. For example, I have a look with a region filter. If the table in my look has 20 rows for one region (west region) and 100 rows for another region (east region), and the user sets the number per page to 10, How can I limit the maximum page number to 2 for west coast and 10 for east coast?

Hmm, that’s a tough question. It’s really easy to inject values from a filter into the SQL statement here using Liquid-- It’s another thing to extract values from a SQL query into a filter dynamically.

There’s a parameter suggest_dimension that lets you take the suggestions for a standard dimension and apply them to any filter: {} field you choose. I wonder if you could rig up a dummy dimension to have something like

sql: (SELECT COUNT(*) / {{ number_per_page._parameter_value }} FROM table WHERE region = {{region._parameter_value}});;

which I suppose would just give you the maximum # of pages possible. You can’t return more than one result in that scalar subquery, though, so I’m not sure how you could tweak it to get you the list of all possible page numbers. I think you could leverage another derived table for that, but that might be overkill by that point.

I would be really interested to see it though! Hope that sets you in the right direction.