Wednesday, 21 December 2011

OBIEE 11g / 12c Row Settings Table and pivot Instanceconfig.

December 21, 2011 OBIEE 11G Row Settings Table and Pivot

OBIEE 11g / 12c Row Settings Table and pivot

When you have a large number of rows in a report Table or Pivot to be viewed .

To implement the solution, please do:

1. Change instanceconfig.xml file in <biee11g_install>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 as following

<Views>
<Pivot>

<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>

<DisableAutoPreview>false</DisableAutoPreview>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxVisibleColumns>5000</MaxVisibleColumns>
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Pivot>
<Table>

<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Table>
</Table>
</Views>

3. Note the new elements added are the following to both <Pivot> and <Table>, the Pivot part controls

Pivot view, and Table part controls Table View, my testing was done with Table View.
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxCells>5000000</MaxCells>
4. Restart Presentation Server from EM, then run your request, do pdf export to verify.

Default rows displayed = incremental button on dashboard

Max visible rows = Display max rows per page or goto end of report -- if stable number can be high

Set them to the same value to remove the Max rows per page down arrow

Element	Description	Default Value	Applicable Views
DefaultRowsDisplayed	Specifies the default number of rows to display in views in analyses and dashboards. This number should not exceed the number that is specified for the MaxVisible Rows element.	25	Narrative, Pivot Table, Table
MaxCells	Specifies the maximum number of cells to be displayed in a view. This number should not exceed the product of MaxVisibleColumns times MaxVisibleRows, which is what the system attempts to render.	50000	Pivot Table, Table
MaxRecords	Specifies the maximum number of records that can be included in the view.	40000	Narrative, Ticker
MaxVisibleColumns	Specifies the maximum number of columns to be displayed in a view.	300	Graph, Pivot Table
MaxVisibleRows	Specifies the maximum number of rows to be displayed in a view. The value of DefaultRowsDisplayed should not exceed this value. For tables and pivot tables, specifies the following: The number of rows that is displayed on the tooltip for the Display Maximum Rows per Page paging control button. The uppermost value to specify for the Maximum Number of Rows to Download and the Maximum Number of Rows Per Page to Include options in Fusion Middleware Control. For information, see Section 7.3.3, "Using Fusion Middleware Control to Set Configuration Options for Data in Tables and Pivot Tables."	500	Graph, Pivot Table, Table
MaxVisiblePages	Specifies the maximum number of view prompts (or pages in PDF) to be displayed in a view.	1000	Graph, Pivot Table, Table
MaxVisibleSections	Specifies the maximum number of sections to be displayed in a view. This element does not apply when a slider is in place for a graph. The SectionSliderDefault and SectionSliderLimit elements apply to limit section values when a slider is in place. See Table 18-2.	25	Graph, Pivot Table, Table

If MAX Cells is not set
Error
View Display Error
Maximum total number of cells exceeded (Configured Limit: 50000).
Error Details
Error Codes: EY692ZW9
　
　

Tried and Tested

<Pivot>

<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload>

<DisableAutoPreview>false</DisableAutoPreview>
<MaxVisibleColumns>1000</MaxVisibleColumns>
<DefaultRowsDisplayed>10000</DefaultRowsDisplayed>
<MaxVisiblePages>500</MaxVisiblePages>
<MaxVisibleRows>100000</MaxVisibleRows>
<MaxVisibleSections>50</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Pivot>
<Table>

<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload>
<DefaultRowsDisplayed>10000</DefaultRowsDisplayed>
<MaxVisiblePages>100</MaxVisiblePages>
<MaxVisibleRows>100000</MaxVisibleRows>
<MaxVisibleSections>50</MaxVisibleSections>
<MaxCells>5000000</MaxCells>
</Table>