Adding Pagination to Tables with Filters

Sometimes it's important to be able to access large amounts of data directly in a table. For chunks of data that can't be safely loaded in a browser, one option would be to split the results in multiple charts on the same dashboard, so the user could just view the different charts as different pages. Another option would be to set a radio-button filter to serve as a page changer. For this example, I'm using a type-in-your-names-and-values filter named PageFilter that is a radio button.

Filter Options:
Name: Page 1, Value: LIMIT 25000 
Name: Page 2, Value: LIMIT 25000 OFFSET 25000
Name: Page 3, Value: LIMIT 25000 OFFSET 50000

Then, in the chart, we'd use direct replacement notation to add the limit/offset statements directly into the query. The "Limit 25000" after the | makes the first page the default page.

Query Ending:
From...
Where...
Order by...
[PageFilter|LIMIT 25000]

Using something like this would eliminate the need for extra charts that might bog down the dashboard, but still give the users a way to see the rest of the results. 

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • This is a great option for datasets of a known size. Is there someway to apply a pagination filter onto a dataset of N size?

    Reply Like
    • aaron markey Yeah! If you provide a large number of monotonically increasing integers with radio buttons, or you want to use an 'Allow users to enter filter values" filter where the user just enters the page number, you can use that integer to multiply the offset. Where 0 is the default for the first page, the chart SQL might look like this: limit 500 offset 500*[pageFilter|0] 
      If you want it to be 1 indexed instead of 0 indexed, you can use this: limit 500 offset 500*([pageFilter|1]-1)

      This multiplication in the offset works in both Redshift and Postgres at least. :)

      Reply Like
  • Worked great for me thank you so much!

    Reply Like
Like4 Follow
  • 4 Likes
  • 1 mth agoLast active
  • 3Replies
  • 264Views
  • 3 Following