Making a Pivot Table Paginate

Wouldn't it be nice if you could have your pivot table split across several 'virtual' pages.

I must admit, it's not an everyday requirement and probably only applicable where your pivot table runs down and off the page.

So, how can we create a paging mechanism, as this is not native to pivot tables, and will only work under a closely defined set of circumstances.

In answers we create a new column, and this will take some fiddling around to get the optimal number, edit the column formula to something like

TRUNCATE((RCOUNT(1)-1)/12, 0) +1

and rename the column to something like page. This will give us an increasing number every 12 rows. I am assuming here that each year has 12 months and we are ordering our report year - month.

In the pivot table drag the 'page' column into the pages section. You should then see a drop down in the pages section that mimics the action of the next/previous buttons on tabular reports.

Now, not all reports - especially those that we will display as pivot tables align themselves so conveniently, where 'pages' can be grouped equally (12 months in a year). In that case you should think about a grouping using the BY clause.