How to Do Pagination in Tableau

Limit Table Rows for Better Dashboard Design and User Experience

Learn an elegant solution for avoiding unwieldy text table rows that take up valuable dashboard real estate. You will learn how to use calculated fields and parameter actions to “flip” through one page of limited rows at a time.

Calculated field to limit the number of rows being shown:

CASE [Parameters].[Page Number]
WHEN ‘1’ THEN INDEX() <= 5
WHEN ‘2’ THEN INDEX() > 5 AND INDEX() <= 10
WHEN ‘3’ THEN INDEX() > 10 AND INDEX() <= 15
WHEN ‘4’ THEN INDEX() > 15
END

Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you how to do pagination in Tableau. By the end of this video, you’ll be able to make a similar table to what you see on the screen right now. This one was made with all of the measure values broken down by sub-category in the Sample – Superstore dataset. By default, there would be 17 different rows in this table. But as you can see at the bottom, there is a selector that’s allowing the user to look at up to five rows at a time.

And as they flip through, they see a different set of five rows until they get to that fourth page, which includes the remaining two sub-categories. So that you can follow along using the Sample- Superstore dataset, I have built– I have re-created this table over in Tableau Desktop. Of course, you can make this table out of any fields that are relevant for your business. But again, the example that we’re working with today, we’ve got 17 sub-categories, therefore we have 17 rows.

And we are pretending that we only want to see five rows at a time. It’s also up to you on that. You can look at one row at a time. You could look at 10 rows at a time. Later in the video, simply plug in the number of rows that you want to see in your use case. But for me, I’m going to go with five rows at a time.

The first step to creating pagination in Tableau is to create a parameter. To create a parameter, I personally like to right click in any blank space on the data pane and click the second option, Create Parameter. And I will call this my Page Number Parameter. And the data type for this is going to be string.

This is going to help us avoid some confusion later on. Even though our page numbers are technically measures, so they’re numbers, either an integer or a float, I guess it would only be an integer in this case. But even though they’re integers, I am going to avoid aggregating them by choosing a data type of string. And then I’m going to make this a very specific list of allowable values.

And for my use case, I’ve got four different options, one, two, three, four. For more flexibility, you could also simply leave this as All. That would allow you to use this same parameter at times for up to four pages or up to 400 pages if your business needs to scale that widely. But I’m just going to show you the list of allowable values to show you exactly what’s happening as we go here.

So I have four allowable values. I will click, OK. And the next thing I need to do is lay the foundation of my Page Selector Control sheet. So I have started this new sheet called Page Selector. And I need to, essentially, make the four columns with each of my page numbers inside of them. To do so, I could use a control sheet created from a secondary data source that has those numbers in it, one, two, three, four.

I show you how to use secondary data sources for better parameter actions on a separate video. I’ll share that in the related content. But I’m also going to show you a shortcut that will help you avoid using a secondary data source.

If you have an existing field in your data set that has the same number of allowable choices, you can make a calculated field to, essentially, force those selections into a calculation. Let me show you what I mean by that. While I don’t have any dimensions called page number, and I don’t have any dimensions that have the dimension values of 1, 2, 3, 4, I do know that in the Sample – Superstore dataset we’ve got this dimension called Region. And if I describe that dimension, I can see that it has four dimension members.

So what I can do is make a calculated field and assign one of my four page numbers to each of those four discrete regions. To do so, I’m going to right-click on the Region dimension, hover over Create, and click Calculated Field. And I will call this Page Number. We have Page Number parameter. This is my Page Number calculated field.

And I could use CASE WHEN logic here. But just to better illustrate what’s happening, I’ll use IF THEN logic. And I’m just going to, on each line, assign a number for each of the four regions. So when it’s central, then we’re going to assign the number ‘1’. And notice, I’m using tick marks, because I want the data type to be string so that it matches my parameter value later on.

Next line, ELSEIF Region equals the next region, which is East. I will assign that a ‘2’. ELSEIF one more time, region equals the next region, which is South. And that’ll get a ‘3’. And then the final classification will be if the region equals West. And that’ll get a ‘4’. And then this type of logic always concludes with the word, “END.”

So we just made Page Number. This is a shortcut to avoiding using a secondary data source. We’re making up a dimension that has our appropriate allowable values that will be used for our Page Selector. I will click, OK. I have a new dimension called Page Number. That’s what I’m going to use as the foundation for this control sheet.

I’m going to drag Page Number onto the Columns Shelf. And I’m also going to drag Page Number onto the Text Marks card. I will format this text to give this a little bit of a bolder look, maybe Arial Black, click Apply and see how that’s looking, because I want these page numbers to show up a little bit better. I’ll call that good for now.

There’s some cleanup I can do. Obviously, those page numbers at the top on the header are repetitive. I’ll right-click on the header and deselect Show Header. Maybe I will center those numbers and right-click on it one more time and click Format to get rid of my lines. This is all optional. Those are my row dividers that are causing those extra lines in there.

So there is the foundation of my Page Selector sheet. Two more calculated fields to kind of polish up this page selector. One nice touch would be the highlight which page is being selected. I can do that with a simple boolean calculated field. I’ll call it Page Selected Highlight. And the entire formula would be my Page Number calculated field that I just created equals the Page Number parameter from step one.

Page Number is orange, because that’s a field in the dataset. Page Number parameter is purple, because it’s a parameter and the dataset. Click OK. And once we have that, drag it to the Color Marks card. The current value of the Page Number parameter is one. That number one is being fed to the Page Selected Highlight. The number one is classified as TRUE. It gets one color. Two, three, and four are classified as FALSE. They all get a second color.

I’ll go in here and maybe make the FALSE gray and the TRUE red just to kind of create a quick little highlight there. One more calculated field. And this is the one that really makes this whole thing go, is we need to make a calculated field that filters our table with 17 rows to only 5 at a time. I will call this, how about, 5 Rows at a Time. 5 Rows at a Time Filter.

And in most cases, you can either use IF THEN or CASE WHEN logic. I used IF THEN logic with the first calculated field. So this time I’ll use CASE WHEN logic just to show you a slight variation. And I’m going to say, “CASE Page Number Parameter.” And on each line, I type a WHEN statement that tells Tableau what to filter when each of those four allowable values is selected.

WHEN it’s the number 1, I want to show index scores less than or equal to 5. And then CASE WHEN logic always concludes with the word, “END” as well. I like to type one line of logic first, and then go ahead and close it out with that in statement, just to make sure I’m on the right track before I keep moving forward. That way if something goes wrong, I only have to troubleshoot one line so far instead of multiple rows.

INDEX, I’ve always thought of as being synonymous with row number. So, so far what we have said is, WHEN the number 1 is selected in this Page Number parameter, filter– this portion of our logic is Boolean, so we’re saying filter those 17 rows to the rows that have an INDEX of 5 or less. So in our case, that would be 1, 2, 3, 4, 5 to get us to 5 rows.

I now need to go in and make the filters for my other page numbers. When we’re on page number 2, we’re showing index scores that are greater than 5. And the INDEX or row number is less than or equal to 10 this time. That will get me rows 6, 7, 8, 9, 10. The next set, so for page 3, we’re going to say, if the INDEX is greater than 10 and the index is less than or equal to 15.

And then finally, when it’s page 4, the INDEX needs to be greater than 15. In our case, with 17 rows, page number 4 is going to show only rows 16 and 17. I will share this in the related content. But of course, you need to apply this logic to your own use case.

Perhaps you want to see more than 5 rows at a time. Perhaps you have a different catch-all than those final two. So you have to tailor this a little bit to your own use case. But for what we have so far, when the number 1 is selected, we’re going to see the first 5 rows. When the number 2 is selected, we’re going to see rows 6 through 10. When number 3 is selected, we’re going to see rows 11 through 15. And when page 4 is selected, we will see rows 16 and 17.

I’ll go ahead and click, OK. We have a new calculated field. The most important thing, or place to put this calculated field, is on the Filters Shelf of your table. So I’ll go back to my table and drag that 5 Rows at a Time Filter to the Filters Shelf, and click True.

And if we did this correctly, once I click OK, we should see 12 of these 17 rows disappear. Let’s see how we did. Click OK. Because the number 1 is the current value of this parameter that is feeding this calculated field that says, when that number 1 is selected, show row numbers 5 and down. So in other words, the first 5 rows in my table. So far so good.

But now I want to connect these two things together. And you can achieve that by putting both the Page Selector as well as the tables that it’s influencing onto a dashboard and then adding a Parameter Action. So I will start a new dashboard, drag my table onto the view. I’ll make this floating so that we can kind of give this a nice layout. Let’s make this fit entire view, center this a little bit more.

And then I’ll drag my Page Selector underneath the table, get rid of my highlighter for just a moment, the highlighter color legend, that is. Kind of put that on the right. And of course, you can clean this up, get rid of the titles as needed. But just to show you how they’re both working here together, we’ve added both sheets as floating objects. One last step is I need to add a Parameter Dashboard Action, which you can do by clicking Dashboard in the top navigation and clicking Actions.

And this is going to be a Parameter Action. So I will click Add Action, Change Parameter. And I’ll call this my Page Selected. I want this to run on select, which is the default. That’s synonymous with click. So, so far so good there. But I only want it to run when somebody clicks a number on the Page Selector sheet. So I will deselect Table.

I also need to choose what the Target Parameter is. That’s my Page Number Parameter. And I also need to choose what value or field I’m passing. There’s only one field on that Page Selector. So that was an easy one. I’ll choose Page Number there as well. Note the aggregation is none. The data type is string. That’s part of the reason I wanted that parameter to be a data type of string.

Slightly less efficient for Tableau to process strings versus integers. But in this case, it makes it much easier and elegant to just have the data type be string. That way I never have to worry about the aggregation. I’ll click, OK. Click OK, again. You don’t see anything change. But we’ve just coded a user experience by adding a Parameter Dashboard Action.

Now, if I click the number 2 on my Page Selector, you see the rows shift. We went from rows 1 through 5 to rows 6 through 10 based on all the logic that we set up. And if I click the number 3, we see the next set of 5 rows. And then in my use case, page number 4 was a catch all for my final 2 rows, rows 16 and 17 for the 17 sub-categories and the Sample – Superstore dataset. And that has been pagination in Tableau.

I’m Ryan with Playfair Data TV – thanks for watching!