Have you ever tried to paginate long reports in Tableau and become annoyed at the number of steps you have to follow to add basic functionalities? If yes, then you have come to the right page.
In this blog, you will learn an easy way to Paginate in Tableau with a dynamic carousel of page numbers and navigation buttons (like the one shown below).
Dataset
You can use the orders table of the latest (2019-2023) superstore dataset to follow along. No data tweaks are needed for this one.
Step 1: Creating a Basic Pagination
Any chart with multiple rows can be paginated, but to understand the technique, you can build a simple bar chart with Customer ID (it is a hidden field by default) in Rows and the SUM(Sales) in the Columns as in the image below.
With a total of 804 rows, manually scrolling through them would be a highly cumbersome task.
To start with the pagination, you will need two parameters. The first one is “Page Size,” which allows you to select the number of rows you want to see at one time. To keep things simple, you can set a step size of 10 within the range of 10-100. Note that this value can also be hard-coded to a fixed value. In that case, there is no need to create this parameter.
The second parameter, known as the “Current Page,” is used to select the desired page for viewing. You can configure it as follows.
Next, create the following calculation to obtain the “Page Number” for each of the rows based on the Page Size. For example, if the Page Size is set to 10, the first 10 rows will be assigned a value of 1.
//Page Number
INT((INDEX()-1)/[Page Size])+1
The key to achieving pagination in Tableau is to display only the rows where the Page Number matches the Current Page. To accomplish this, create a calculation called “Page Filter.”
//Page Filter
[Current Page] = [Page Number]
Show the two parameters and add the Page Filter in the Filters card. Make sure that the “True” option is selected. This is what you should have once you complete it.
With this, you have a bare-bones version of pagination in Tableau. Play around with the Page Size and Current Page parameters to see how the chart changes.
Step 2: Building the Navigation Menu
Create a calculation named “Last Page” that calculates and returns the value of the last page based on the specified Page Size.
//Last Page
CEILING({COUNTD([Customer ID])}/[Page Size])
Since a Fixed LOD is used to get the total number of unique customers, if you have some filters that must be applied on the paginated chart, you will have to promote them to context filters.
Now, to build the navigation buttons with the dynamic carousel of page numbers from the introduction, you will need a placeholder field with 9 values — 4 navigation buttons and 5 page numbers. In this case, you can use the different months of the year from the Order Date field.
While this technique will work in almost all of the cases (as every data source will have some fields whose values don’t change over time), there may be instances where you need to introduce a separate table linked to your primary table in the data source pane in order to obtain a placeholder field.
Using the placeholder (you can use DATEPART to obtain the months as it is easier to write the Case statement), create a calculation named “Label.”
//Label
CASE DATEPART('month', [Order Date])
WHEN 1 THEN "|◄"
WHEN 2 THEN "◄"
WHEN 3 THEN STR([Current Page] - 2)
WHEN 4 THEN STR([Current Page] - 1)
WHEN 5 THEN STR([Current Page])
WHEN 6 THEN STR([Current Page] + 1)
WHEN 7 THEN STR([Current Page] + 2)
WHEN 8 THEN "►"
ELSE "►|"
END
On a new sheet, Drag this field on the text. Right-click on the Label pill and sort it as shown.
Next, show the Current Page parameter and set the value to 10. You will get this.
Play around with the parameter (at this point, since no actions are set, the navigation buttons won’t do anything) and see how the carousel gets updated dynamically based on the Current Page.
However, notice that when the Current Page is 1, the first two values are incorrect.
Also, when the Current Page is the Last Page, the last two values will be incorrect.
You can fix this issue by incorporating some logic for the carousel values in the calculated field of Label, like shown below.
//Label
CASE DATEPART('month', [Order Date])
WHEN 1 THEN "|◄"
WHEN 2 THEN "◄"
WHEN 3 THEN STR(IF [Current Page] <= 3 THEN 1 ELSEIF [Current Page] >= [Last Page] - 2 THEN [Last Page] - 4 ELSE [Current Page] - 2 END)
WHEN 4 THEN STR(IF [Current Page] <= 3 THEN 2 ELSEIF [Current Page] >= [Last Page] - 2 THEN [Last Page] - 3 ELSE [Current Page] - 1 END)
WHEN 5 THEN STR(IF [Current Page] <= 3 THEN 3 ELSEIF [Current Page] >= [Last Page] - 2 THEN [Last Page] - 2 ELSE [Current Page] END)
WHEN 6 THEN STR(IF [Current Page] <= 3 THEN 4 ELSEIF [Current Page] >= [Last Page] - 2 THEN [Last Page] - 1 ELSE [Current Page] + 1 END)
WHEN 7 THEN STR(IF [Current Page] <= 3 THEN 5 ELSEIF [Current Page] >= [Last Page] - 2 THEN [Last Page] ELSE [Current Page] + 2 END)
WHEN 8 THEN "►"
ELSE "►|"
END
Due to the If part of the If-Else statement, the carousel will remain fixed and display only the first five pages when the Current Page is set to any of the first three pages.
Because of the Elseif part of the If-Else statement, when the Current Page is set to any of the last three pages, the carousel will be fixed and will show only the last five pages. The Else part is the same as the first iteration of the Label calculation. This ensures that only values between 1 and the Last Page are on the carousel.
Step 3: Setting up the Dashboard Actions
Without implementing any actions, the navigation menu from the previous step is useless. Here, you can get the desired outcome by setting up a parameter action that changes the value of the Current Page based on which of the 9 interactive elements is clicked. To configure the value that gets passed in the parameter, create the following calculation.
//Value
CASE [Label]
WHEN "|◄" THEN 1
WHEN "◄" THEN IIF([Current Page]=1,1,[Current Page] - 1)
WHEN "►" THEN IIF([Current Page]=[Last Page],[Last Page],[Current Page] + 1)
WHEN "►|" THEN [Last Page]
ELSE INT([Label])
END
IIF statements for the previous and next buttons will ensure that Tableau does not add values beyond the limits of 1 and the Last Page in the parameter. Additionally, the Else portion of the Case statement will make the numbers on the carousel interactive.
Next, Ctrl+drag the Label Pill in Columns and untick Show Header. Convert the Value calculation to a Dimension and drag it on Detail. This is what you will have.
Now, go to Worksheet -> Actions -> Add Actions -> Change Parameter and do the following.
Hit OK on both menus, and this is what you should get.
Boom! Although it looks exactly the same as before, you now have pagination! Click on the values and watch how the parameter changes.
Step 4: Formatting the Navigation Menu
To format the navigation menu, first, create the following calculation.
//Color
[Current Page] = INT([Label])
Drag this calculation in both the Color and Shape. Format the sheet to your liking until you have something like this.
Although things work as expected, you might have noticed, as you were using the controls, the highlighting makes a mess. To fix this, create the following calculation.
//Highlight Disabler
“x”
Add this calculation in Detail, and then from the top toolbar, do the following.
This will fix the highlighting. Try it out!
Next, you can add the two sheets on a dashboard (it is called Pagination) and change the Source Sheets for the actions (highlight and parameter) from the dashboard.
Achieving the desired result will require some adjustments to the size of the shape and sheet size until it aligns. Your final dashboard should look like this.
Conclusion
There you have it! A simple yet feature-rich take on pagination in Tableau using just one sheet and one parameter action!
If you want more information on Pagination in Tableau, contact our team of experts!
