Business Intelligence – Oracle

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

Posted by Venkatakrishnan J on January 23, 2008

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

About these ads

9 Responses to “Oracle BI EE 10.1.3.3/2 – Total Number of Rows and Pagination”

  1. Ben said

    Neat trick. Is it also possible somehow to modify default row per page setting (25) ?

  2. Tina said

    to Ben,
    You can modify the page setting in the table view properties.

    to Venka,
    Can we also find a method to show the total pages?
    Any way to get the page setting parameters when not default setting(25)?

  3. Venkatakrishnan J said

    Tina,

    In order to get the total pages, we need to get the value of rowsPerPage variable. This can be achieved using Java Script.

    -Venkat

  4. Sumant Sarkar said

    sorry for a basic question.. how do i get to the dialog box (5th screenshot in this article) ?

  5. Venkatakrishnan J said

    @Sumant – The screenshot points to the narrative view.

  6. Ben said

    Thanks for the tip for overwriting the default 25 row per page setting for a specific table.

    Now I’m wondering if it is possible to change somewhow the system default, so I do not need to edit all of my table view properties?

  7. Alan said

    Hi, Ben

    Adding the settings following to instanceconfig.xml
    Then the presentation server got the default row per page settings.
    ====setting====

    <WebConfig>
    <ServerInstance>
    <Views>
    <Table>
    <DefaultRowsDisplayed>50</DefaultRowsDisplayed>
    </Table>
    </Views>
    </ServerInstance>
    </WebConfig>

  8. Samori said

    Venkat,

    This is a great suggestion for adding the total number of rows. But we discovered that it is also a performance hog because it uses ROW_NUMBER OVER PARTITION and a triple nested query which really hurt query speed.

    Besides using a different query to produce the total number of rows for the query, is there another less performance intensive way of producing the count?

    Thanks,

    Samori

  9. gopinath said

    Hi Venkat,
    Is it possible to restrict the rows in PIVOT table view without pagiation?
    is there any work arounds?

    Regards,
    Gopinath

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: