Exceeding maximum number of rows in Excel

[doaaashour]

Dear jpaliuka,

I'm trying to construct a pivot table out of a flat table that has more than 1,048,576 records. The excel has this limit for the number of rows. Any idea for a workaround?

Thanks in advance.

[jbaliuka]

There was an error — your comment cannot be blank.

Workaround is to split report to multiple files but I would suggest to use PowerPivot extension instead of template tools for large data sets, PowerPivot supports very large data sets

[kalarikkalbabu]

is there any option to create pivot directly from sql query without writing datasheet, somehng like smartxls is doing?

[jbaliuka]

We have no pivot cache implementation. Workaround is to use SQL Grouping Sets + Pivot most of modern databases support reporting queries. It is also possible to format this output using undocumented syntax e.g. https://github.com/jbaliuka/x4j-analytic/blob/master/samples/src/test/resources/samples/RollupReport.xlsx but it is complicated to maintain such reports with scripting templates.

[kalarikkalbabu]

Is it possible to create pivot for huge files in single file with multiple datasheets?

[jbaliuka]

datasheets are static but you can simply group results by dimension columns to summarize datasheet if you do not want details. It is always possible to make datasheet as small as pivot after SQL level grouping and to use Excel pivot for formatting