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.

5 6 8,359
6 REPLIES 6

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.

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?

dj200
Participant I

Does this only work for straight selection from view?

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

I’m trying exactly this with google bigquery and I do not get the correct results.

It looks like big query limits the results before you can apply a filter to the data resulting in datasets that are incorrect.

Is there another way to perform pagination without the limit and offset ending up in the derived table sql which will create a query like this:

WITH hello_world_paginated AS (SELECT * FROM `helloworld tables`

     LIMIT 500

     OFFSET 1500

            )

SELECT

 fields [...]

FROM hello_world_paginated

WHERE (hello_world_paginated.amount ) > 100

GROUP BY

    1,

    [...]

ORDER BY

    1

LIMIT 1000

but instead a query that is like this:

WITH hello_world_paginated AS (SELECT * FROM `helloworld tables`    

            )

SELECT

 fields [...]

FROM hello_world_paginated

WHERE (hello_world_paginated.amount ) > 100

GROUP BY

    1,

    [...]

ORDER BY

    1

 LIMIT 500

  OFFSET 1500

This latter query will give the correct results.

Hi

I am brand new to Looker. I have a requirement and I am trying to replicate this solution for pagination, but I am stuck at a point where I am not able to pass changing page numbers to the buttons. Could you please share any inputs to this.

Thanks in advance 🙂

Top Labels in this Space
Top Solution Authors