One of the more powerful tools in OBIEE is the pivot view. If you have large amounts of data you sometime run out of rows and or columns or even worse out of cells.
You can control these values by two ways:
1. Limit rows from sever side
2. Limit rows from presentation side
For limiting rows from server side you have to modify instanceconfig.xml file
This file is usually found in ....\OracleBI\instance\instance1\config\OracleBIPresentationServer
If you open instanceconfig.xml in notepad you get something like these
Now here pivot table is looking as follows:
<Pivot>
<DisableAutoPreview>false</DisableAutoPreview>
<MaxCells>1920000</MaxCells>
<MaxVisibleColumns>5000</MaxVisibleColumns>
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>25</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<DefaultRowsDisplayed>50</DefaultRowsDisplayed>
</Pivot>
The behavior of the pivot view can be altered with the following parameters:
- [MaxVisibleColumns] Sets the maximum number of columns to be displayed in a Pivot View default{5000}
- [MaxVisiblePages] Sets the maximum number of page choices (or pages in PDF) to be displayed in a Pivot View default{25000}
- [MaxVisibleRows] Sets the maximum number of rows to be displayed in a Pivot View. Default{25}
- [MaxVisibleSections] Sets the maximum number of sections to be displayed in a Pivot View. Default{5000}
Here you can changes value as per your requirement or as u want to display on page & start BI services.
For limiting rows from Presentation side do following steps:
1. Create an analysis using pivot table view.
2. Click on Edit button of pivot table.
3. Select pivot table property from top title bar:
4. Select top page control from drop down list:
5. Enter the rows as per your requirement:
6. Click on OK & save analysis.
But it will work only for one analysis if u want same thing for multiple analysis then better option is do it from server side.
