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
Ben said
Neat trick. Is it also possible somehow to modify default row per page setting (25) ?
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)?
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
Sumant Sarkar said
sorry for a basic question.. how do i get to the dialog box (5th screenshot in this article) ?
Venkatakrishnan J said
@Sumant – The screenshot points to the narrative view.
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?
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>
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