Oracle BI 10.1.3.3/2 – Pagination in Pivot Tables

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.

Oracle BI EE 10.1.3.3/2 – Total Number of Rows and Pagination

Let us look at something very simple today. If you have used pagination in BI EE table views, you would have noticed that there is no way you can find out the total number of records which might sometimes be essential for an end user. It is always desired that the end user knows how many number of pages he is scrolling through.

      

Now in order to have the total records displayed to the end user, there can be a lot of ways. But lets look at a simple approach. Create an extra column in the criteria and put the following formula in the formula tab.

MAX(RCOUNT(1)

This will give us the total number of records as part of each and every row.

      

      

In my case, there are totally 48 records out of which 25 are displayed. Now hide this new calculated column and go to narrative view. Refer this column in the narrative view as @n, give some message so that the end users know. Also restrict the number of records to 1. Now include this narrative view to the compond layout and arrange it in such a fashion that it is just below the table view.

      

      

      

Very simple but again can be useful in quite a lot of situations