To create pagination, we need to use the INDEX() function, which I think of as being synonymous with “row number”, to display a subset of rows that are mapped to each selection in the Page Number parameter. Now for the calculation that makes pagination in Tableau go. Tips for Formatting Text Tables in Tableau This table can be created with any combination of dimensions and measures, but for the purposes of illustration, I’ll create a crosstab that contains all of the measures in the Sample – Superstore dataset by the Sub-Category dimension. Since the default current value of the Page Number parameter is 1, we are seeing the 1 on the view get classified as True and colored one way while everything else is classified as False and colored a second way.
This will always color the page number that has been selected in the control sheet.
Then place this calculated field on the Color Marks Card of your control sheet. To highlight the current page, create a Boolean calculated field with this formula: However, one nice touch to add would be to highlight the page that the user is viewing, implying that clicking a different number will navigate them to a different page. You can stop here because this control sheet will work as is for the user’s page selection experience. To create the control sheet, place this new dimension on the Columns Shelf and Text Marks Card then hide the first set of numbers by right-clicking any column header and deselecting “Show Header”. Note the tick marks around the numbers which ensure the data type of this calculated field matches the data type of the Page Number parameter I set up earlier. This will open a calculated field dialog box where you can rename the field to “Page Number” and create this calculation: Once the copy shows up, edit it by right-clicking on the copy and choosing “Edit…”. To do so, duplicate the Region dimension by right-clicking on it and choosing “Duplicate”. If I can convert those dimension members into the selections I need, I can use the new dimension to create the page selector / control sheet. While I don’t have a dimension containing the members 1, 2, 3, and 4, I do have a dimension called Region which I know contains four members: Central, East, South, and West. Instead of connecting to a second data source, I’ll share another trick that allows you to create a dimension with custom dimension members. This makes it easier to create control sheets because you can create a dimension containing any custom values (i.e. I don’t have any fields in my dataset with the dimension members of “1”, “2”, “3”, and “4”, so one option is to use the trick I’ve shown before that involves connecting to a separate data source with custom parameter values. Page one will eventually display rows 1-5, page two will display rows 6-10, page three will display rows 11-15, and the final page will show the remaining two rows. I want the user to see five rows at a time, and there are 17 sub-categories, so I need four pages. Next, create a control sheet with the page number options you need. strings aren’t aggregated) and the allowable values of all means you can scale the number of page options for your table indefinitely (i.e. The data type of String will help prevent confusion with aggregation in future steps (i.e. Clicking a page number on the control sheet at the bottom of the table takes the user to a different subset of five sub-categories.įirst, create a parameter with a data type of String and allowable values of All.
How to let users flip between ‘pages’ of crosstab rows in Tableauīy the end of this tutorial, you will be able to show five rows of sub-categories at a time in the Sample – Superstore dataset. I will also share a trick for creating control sheets with custom dimension members without the need for a separate data source – and you’ll see how to use pagination to create better commenting systems on your dashboards. In this first of three posts, you will learn how to limit the number of rows being shown on a table until the user clicks a button to “turn the page” to the next set of rows.
Innovative Tableau contains 15 different ‘innovation’ series including “3 Innovative Ways to Use Text”, and this new series will share three more of my favorite tactics. To celebrate the release of Innovative Tableau: 100 More Tips, Tutorials, and Strategies, I’m releasing some bonus content to show you how to get more out of text in Tableau.