New Export Pivot Tables to Excel

Enabling and Configuring the New Export to Excel

When exporting a Pivot widget via the New Export to Excel, there is now a higher modifiable limit to the number of rows and columns that you can export, which you can define for yourself.

• ElastiCube - The Export to Excel limit for EC was previously defined by the Pivot settings for Pivot Query Limit and the Maximum # of columns to display. There are now separate settings for the Pivot visualizations in the dashboard (under the System Configuration settings, in the Pivot Table section) and Export to Excel (under the System Configuration settings, in the Exporting section). The default Export settings are 0 (use Pivot Query Limit and Maximum # of columns to display, for the rows and columns respectively).

  Important: Downloading the data to Excel is still limited by the query timeout and browser timeout limits. Pivot structure and complexity factors affect the time it takes to export, but up to 4 million cells (200k rows, 20 columns) are expected to download successfully in most scenarios.

  The following image displays the System Configuration settings for Limits for Pivot queries for displaying data on dashboards.

  

  The following image displays the System Configuration settings for Limits for Pivot queries for exporting data to Excel. To enable the new Export to Excel service, switch on the Export to Excel V2 toggle.

  

• Live - Performance is affected by the data sources. Both the Pivot Query Limit setting for data visualization on dashboards and Export to Excel V2 Row Limit setting will be capped by the Query Settings Result Limit defined for a Live model on which a particular Pivot widget is based. The maximum Result Limit = 50k rows. Effectively:

  • For displaying results in a dashboard, the lower value of the Pivot Query Limit and Live Query Limit is applied.

  • For exporting to Excel, the lower value of the Export Row Limit and Query Result Limit is applied.

  Note: If you require exporting more than 50k rows on Live (not recommended), contact support to change your maximum setting.

Export Pivot Table to Excel

You can download Pivot Tables to Excel files directly from your Sisense dashboard. The downloaded Excel files retain most of the formatting and design applied to the dashboard's Pivot table, as described below.

Use Cases

Here are some examples of use cases for the Export to Excel functionality:

• Follow-up Excel analysis: Mesh with other files/ deep diving into a specific data snapshot/ ad-hoc data enrichment
• Archive file: To serve some internal policy/ regulation
• Share a fully functional report for users that do not have Sisense permissions

Excel vs CSV export:

Sisense enables export to both Excel and CSV formats. This is what should be considered when deciding which format to use when exporting the Pivot table:

Excel	CSV
With all formatting meant for human read	Uses raw data with no formatting meant for machine read, such as transferring the data to another workflow in a different system or database
Heavier to generate because of all the formatting	Quicker to generate
Limited to ~4 million cells spread across columns and rows for EC, and 50k rows for Live	Unlimited up to 1 billion rows

Here's what you need to think about

When you anticipate the pivot will be exported frequently to xls, we recommend to keep the following in mind when designing it:

• Sisense allows for robust formatting options, giving designers the full freedom to create the experience they want and need for their users. However, some advanced formatting options can have an impact over the raw data and are not natively supported outside of Sisense. Users can expect to see a 1:1 view when exporting a pivot to excel, it's on the designer to ensure their users' experience is clearly transferable when exporting.
• Each cell or column format is a combination of styles (e. g. color, numFmt) and conditional formatting rules.

Formatting the Pivot Table

The new Export to Excel service includes some significant upgrades in terms of export formatting. The service must be manually enabled through configuration.

Implementation of the formatting is done by making multiple specific decisions as detailed below:

Column styles

All column styles are applied before row rendering to ensure every added row is formatted the same (including rows added manually, over the exported file).

Collecting conditional formatting rules

Conditional formatting rules are applied by order of calculated priorities; a cell rule has higher priority than a column rule.

Subtotal formatting

All subtotal cells (cells that have '<cell value> Total' format) are transformed into string values during Excel generation. This is the same behavior of native excel subtotal calculation functionality.

Supported Date Formats

Year Level

Format	Supported/Not supported
default - 1970	Supported
yyyy - 1970	Supported
yy - 70	Supported
yyyp - 1969	Previous year - not supported
yp - 69	Previous year - not supported

Quarter Level

Format	Supported/Not supported
default - Q1	Value evaluated as a string. Format is not native to Excel so cannot be used.
Q - Q1	Value evaluated as a string. Format is not native to Excel so cannot be used.
QQ	Value evaluated as a string. Format is not native to Excel so cannot be used.

Month Level

Format	Supported/Not supported
default - 01 or 12	Supported
M - 1 or 12	Supported
MM - 01 or 12	Supported
MMM - Jan	Supported
MMMM - January	Supported

Week Level

Format	Supported/Not supported
default - 01 or 52[54]	Value evaluated as a string. Format is not native to Excel so cannot be used.
w - 1	Value evaluated as a string. Format is not native to Excel so cannot be used.
ww - 01	Value evaluated as a string. Format is not native to Excel so cannot be used.
pattern	Value evaluated as a string. Sisense changes the value of date to 1st day of week. When exporting, the original date is shown as the value but follows the pattern.

Day Level

Format	Supported/Not supported
default - 01 or 31	Supported
d	Supported
dd	Supported
EEE	Supported
EEEE	Supported

Hour Level

Format	Supported/Not supported
default - 00-23	Supported
HH	Supported
hh	Supported
a - am/pm	Supported
kk - 01-24	Not supported - replaced with HH

Minute Level

Format	Supported/Not supported
default - 00-59	Supported
mm - 00-59	Supported

Supported Number Formats

Numbers are parsed as native Excel types. For abbreviated numbers (aka K, M, B, T), the feature applies conditional formatting rules.

Auto

Value	Dashboard	Excel
1.234	1.23	1.23
1.23	1.23	1.23
1.2	1.2	1.2
1	1	1.0

Abbreviations

Value	Dashboard	Excel
12345678	12.35M	12.35M
123456789	123.5M	123.46M

Percentage

Value	Dashboard	Excel
0.12	12.00%	12.0%
0.1	10.0%	10.0%

Limitations

• If number formatting is not specified in the Pivot table, it uses Excel default number formatting.
  • This is identical to the dashboard auto formatting, except:
    • For numbers that are too large (>= 1e+11), or too small (<= 1e-10), Excel uses scientific format.
    • Decimal point is enforced by Excel. And so, 1 in dashboard will present as 1 and in Excel as 1.0
• Thousands separator and decimals point style are defined by the system locale definitions. For example, the number 1,000,000 can be printed both as 1 000 000 and 1,000,000 depending on system locale.
• Currently, both Export to Excel services (previous and new) are supported. The new service described in this article does not support formatting for non-default fiscal year and first day of week definitions. Therefore, the previous Excel Export service is set as the default engine. If you are not using fiscal year or changing the first day of the week, it is recommended to use this new service for improved formatting, data consistency, and performance.
• The data bars and color range formatting are not supported in Export to Excel.

Performance

• The new Export to Excel supports up to ~4 million cells for EC and 50k rows for Live.
• For a pivot table with 10k rows and 70 columns, average export time is of approx 2.5 minutes.
• Exporting while applying sorting by 2 columns can drive export time up by an avg of 20%.