Another common request that usually comes up is how do we enable Pagination in Pivot Tables. Though this is not available out of the box, lets look at one approach today to have pagination in pivot tables. Lets start with a simple pivot table report as shown below containing 2 columns, DIMENSION_KEY and SALES ( ihave taken this report just as an example).
Now our aim is to add pagination to this report. In order to do this lets go back to the criteria tab and add one more column to this report(any column). Go to the formula window of this column and enter the formula shown below
CASE WHEN RCOUNT(1) < 11 THEN ’1-10′
WHEN RCOUNT(1) < 21 THEN ’11-20′
WHEN RCOUNT(1) < 31 THEN ’21-30′
ELSE ’30+’ END
So, what the above code basically does it creates repeating rows 1-10, 11-20 etc. You can modify the above to suit your needs.
Once this is done, drag and drop this column into the Pages section of your pivot table. Now you can paginate through your pivot table report.
Very simple but can be pretty handy in a lot of situations.
Posts
