Pivot functionality in excel template does not work when the number of data rows exceed 65K

MeghaR

We are using excel template pivot functionality to develop a costing report. Oracle supports only xls excel template and this is causing issue when the number of data rows exceed 65k limit. Is there a workaround to achieve pivot functionality with filters and drill down option without using excel template.

Manu R-Oracle

Hi Megha,

This is so because Excel has a limit of 65,536 rows per worksheet. This limit is imposed by the amount of memory that Excel can allocate to a single worksheet.

Few Alternatives/workarounds could be:

1.Split the data into multiple worksheets. If you have a large amount of data, you can split it into multiple worksheets, each with no more than 65,536 rows. Then, you can create a pivot table for each worksheet.

2.See if the number of records in the output can be reduced. Re-validate the query for duplicates or see if it could be split into different reports controlled by different parameters.

Hope this helps.

Thanks

Manu

Mallikarjuna Kuppauru-Oracle

Hi @MeghaR

You can try below MOS doc -

Scheduled Job of BIP Report of Excel Template Failed by Error 'oracle.xdo.XDORuntimeExeption: Your data is more than 65531 rows in a sheet' (Doc ID 2982923.1)

Excel Template Properties

Property Name : Enable Scalable Mode

Description : When set to true, large reports that use Excel template run without out of memory issues.

       Data overflows automatically into multiple sheets if a group of data in a sheet exceeds 65000 rows.

       This overcomes the Microsoft Excel limitation of 65000 rows per sheet.

    When set to false, large reports that use Excel template can cause out of memory issues

Regards,

Arjun

deepmehta1-Oracle

I am reaching out to inquire about the best practices for handling pivot data ranges when dealing with more than one data source sheet in a BI Publisher Excel template. Specifically, I am seeking guidance on how to manage the data range efficiently to ensure accurate reporting.

It would be greatly appreciated if you could provide insights, detailed steps, or resources that address this scenario.

Gianni Ceresa

Hi @deepmehta1-Oracle ,

Your question isn't really related to the topic of this thread, is it?

The question here is about the Excel limit of 65k rows, while you look for "best practices" on a more generic report, not really impacted by the 65k rows limit.

If your question isn't about the 65k rows limit topic, post a new question please (providing all the details that somebody not seeing your screen need to understand what you are working with and what you would like to get as answer).

If your question is about the 65k rows limit topic, then you may want to reformulate it with something explicit about this topic?

Thanks