Every 300th Row has Corrupt or Merged Cell Values When an Analysis is Exported to Excel

APPLIES TO: 

Business Intelligence Server Enterprise Edition - Version 11.1.1.7.131017 and later Information in this document applies to any platform.

SYMPTOMS

In Column Properties -> Column Format tab -> Value Suppression,you checked Suppress,

and export the analysis to Excel.　But every 300th row of the export contains corrupt  or merged cells.

This is happens in both Excel 2003 and Excel 2007.

 

CAUSE

According to unpublished bugs:

Bug 18454210 - BLANK CELLS FOR EVERY 300 ROWS IN A DOWNLOADED EXCEL FILE FOR A SINGLE REPORTBug 17644375 - THE COLUMN IS MERGED INTO THE 300 LINE-BY-LINE WHEN DOWNLOADING TO EXCEL

This is expected behavior as the BI Publisher renders the Excel data in chunks, and the default chunk size is 299 rows.

While exporting from BI presentation service, the number of rows to keep in memory follows above chunk size.

SOLUTION

Please use any of the following workarounds:

Add parameter RowsInWindowedMode to instanceconfig.xml, increase its default value (300) same as DefaultRowsDisplayedInDownload and restart OBIEE. For example:  <GridViews>  <RowsInWindowedMode>2500</RowsInWindowedMode>  </GridViews>   Edit analysis　-> Column Properties -> Column Format tab -> Value Suppression, check Repeat. 系统的chunks size的参数RowsInWindowedMode的默认值299行，Presentation Service会按(RowsInWindowedMode + 1=)300来描绘，这样就在导出excel时，就会每300行一分隔。  那么这个问题的对应方法是，适当的调整(增大)instanceconfig.xml的RowsInWindowedMode参数，或者按[Edit analysis　-> Column Properties -> Column Format tab -> Value Suppression, check Repeat.]的方式修改报表。 

补充：当您修改RowsInWindowedMode参数时，推荐和DefaultRowsDisplayedInDownload的大小一致。另外如果RowsInWindowedMode的值设定的过大的话，可能会造成内存的浪费并且导出excel生成的临时文件会过大，这一点一定要留意。 

BIEE12C:

<Views> <GridViews> <RowsInWindowedMode>2500</RowsInWindowedMode> </GridViews> </Views>