New Export Pivot Tables to Excel

Note: 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.

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%.

Note:

There is a 5 minute limitation on export, so that after 5 minutes, the browser times out and the download fails. During download, the browser locks while waiting for the file to complete download.