How to add pagination to Looker Dashboard?

Pagination is an important feature of many data applications, including Looker. With pagination, users can easily navigate through large data sets without overwhelming their browser or network connection.

In this blog post, we’ll explore how to implement pagination in Looker, including the LookML code and dashboard configuration necessary to create a custom pagination control.

How Pagination Works in Looker

Looker uses two query parameters, limit and offset, to implement pagination. limit specifies the maximum number of results to return, while offset specifies the number of results to skip before returning the next set of results. By adjusting these parameters, you can control which subset of data to display on a page-by-page basis.

For example, suppose you have a data set with 1,000 rows, and you want to display 25 rows per page. To display the first page, you would set limit to 25 and offset to 0. To display the second page, you would set limit to 25 and offset to 25, and so on.

Adding Pagination to Looker

To add pagination to a Looker dashboard, you’ll need to modify the LookML code for your explore view, and create a custom HTML element to display the pagination control. Here’s a step-by-step guide to implementing pagination in Looker:

Step 1: Define Pagination Parameters in LookML

The first step is to define two parameters in your explore view: page and per_page. These parameters will determine which subset of data to return based on the current page number and the number of results per page.

explore: my_table {
  # Other parameters here

  # Pagination parameters
  parameter: page {
    type: number
    default_value: 1
  }
  parameter: per_page {
    type: number
    default_value: 25
  }

  # Fields, joins, and filters here
}

In this example, we define page and per_page as number parameters with default values of 1 and 25, respectively. You can adjust these values to suit your specific use case.

Step 2: Create a Custom HTML Element for Pagination

The next step is to create a custom HTML element that displays the current page number and allows the user to navigate to other pages. You can use the liquid templating language to dynamically generate the page links based on the total number of pages in your data set.

Here’s an example of what the custom HTML element might look like:

{% assign total_pages = explore_name.total / explore_name.parameters.per_page %}
{% if total_pages > 1 %}
  <div class="pagination">
    {% if explore_name.parameters.page > 1 %}
      <a href="{{ url_with_new_parameters({ 'page': explore_name.parameters.page | minus: 1 }) }}">« Previous</a>
    {% endif %}

    {% for page_number in (1..total_pages) %}
      {% if page_number == explore_name.parameters.page %}
        <span class="current-page">{{ page_number }}</span>
      {% else %}
        <a href="{{ url_with_new_parameters({ 'page': page_number }) }}">{{ page_number }}</a>
      {% endif %}
    {% endfor %}

    {% if explore_name.parameters.page < total_pages %}
      <a href="{{ url_with_new_parameters({ 'page': explore_name.parameters.page | plus: 1 }) }}">Next »</a>
    {% endif %}
  </div>
{% endif %}

This code generates a pagination control that includes links to the previous and next pages, as well as links to individual page numbers. The url_with_new_parameters() the function is a Looker utility function that generates a new URL with updated query parameters. In this case, we use it to generate URLs with the page parameter set to the appropriate value.

Step 3: Include the Custom HTML Element in Your Dashboard

Once you’ve created the custom HTML element, you can include it in your Looker dashboard by adding an HTML widget to the dashboard and pasting the code into the widget’s configuration.

To do this, navigate to your dashboard, click the Edit button to enter edit mode, and add a new HTML widget to the dashboard. Then, paste the custom HTML element code into the widget’s configuration. Be sure to replace explore_name with the name of your explore view, and adjust the HTML and CSS as needed to match your dashboard’s design.

Step 4: Update Your Explore Query to Use Pagination Parameters

The final step is to update your explore query to use the page and per_page parameters defined in your LookML code. To do this, add the following sql parameter to your explore view:

explore: my_table {
  # Other parameters here

  # Pagination parameters
  parameter: page {
    type: number
    default_value: 1
  }
  parameter: per_page {
    type: number
    default_value: 25
  }

  # Use pagination parameters in query
  sql: |
    SELECT *
    FROM my_table
    LIMIT {{ per_page }}
    OFFSET {{ (page - 1) * per_page }}

  # Fields, joins, and filters here
}

In this example, we use the LIMIT and OFFSET SQL keywords to limit the number of results returned based on the per_page and page parameters. Note that we subtract 1 from the page parameter to convert it to a zero-based index for the OFFSET clause.

Conclusion

Pagination is an important feature for any data application that deals with large data sets. By implementing pagination in Looker, you can provide your users with a more responsive and manageable user experience. With the steps outlined in this blog post, you can easily add pagination to your Looker dashboards, allowing your users to navigate through large data sets with ease.

Ref- Looker Docs

More Imp Topics

How Looker Writes SQL?

How to calculate percent of total in looker?

Table Calculations in Looker

What are Derived tables in Looker?

How to set conditional formatting in LOOKER?

Looker Interview Questions And Answers

New Looker Performance Recommendations Dashboard

Looker Git Version Control: How To Revert To A Specific Commit In Looker, No Git Commands Necessary

________________________________________________________________________________________

Reference :

About Me:-
I am Om Prakash Singh – Data Analytics Consultant, Looker Consultant, and Solution Architect.
I am a Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation, and visualization. Furnish insights, analytics, and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us if you are interested to evaluate if Looker is right for you or any other BI solution.