Question

Pagination in Looker

  • 23 November 2019
  • 4 replies
  • 1331 views

Userlevel 2

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 replies

HI Guys,

 

Pagination is really a key functionality if the data is more to view, The above solution suggest dimension might work without filtering, If the customer is slicing the data by filters, then the page count will also change dynamically.

 

Is there any better approach like infinite scrolling, so customers scroll thru the data?

Also, in case if customer wants to know the total number of records, how it can be supported?

 

Userlevel 7
Badge +1

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.

Does this only work for straight selection from view?

Does it work if I have dimensions / measures or even includes joins from explore?

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?

Reply