Business Intelligence – Oracle

Oracle BI – Pagination in Pivot Tables

Posted by Venkatakrishnan J on January 17, 2008

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.

7 Responses to “Oracle BI – Pagination in Pivot Tables”

  1. sree said

    Like above the case statement can i create on character ?
    Can you pls suggest me


  2. Venkatakrishnan J said

    @Sree – I am not sure i understand your question. Can you explain. This case statement does not refer to any columns. As far as you have atleast one other column in your report, this will work.

  3. Sridhar Mandala said

    Hi Venkatakrishnan,

    I tried to achieve this Pagination but facing with some issue here. Can you please guide me in this…

    I used “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
    ” in the column formula.

    Here is the Error that I am getting.

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near : Syntax error [nQSError: 26012] . (HY000)
    SQL Issued: {call NQSGetQueryColumnInfo(‘SELECT 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, Periods.”Current Date”, Periods.Week FROM Paint’)}

    Thanks in Advance

  4. Sridhar Mandala said

    Hi Venkat,

    I got it!!

    Thanks and Regards
    Sridhar Mandala

  5. Juan said

    Hi Venkat,
    I get the same error as Sridhar, but he doesn´t say how did he solve it.
    Do you know what I am I doing wrong?


  6. Sridhar said

    Hi Juan,

    Instead of copy and paste of the given formula, I wrote it manually and it worked….

    This is the formula I used… in quotations

    “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”.

    Of course there is no difference between what Venkat gave and what I used… its just that I typed it manually….

    Thanks and Regards,
    Sridhar Mandala

  7. Ahimsa said

    This is excellent. But when I try to Print it gives only the page displayed.

    Is there a way I can print all the records and or download all the records.

    Data Download is not an option as I will loose my Formatting and other bells and whistles.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: