New Export Pivot Tables to Excel
  • 18 May 2022
  • 3 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

New Export Pivot Tables to Excel

  • Dark
    Light
  • PDF

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 don't 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 1.5 million cells spread across columns and rows

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.

Performance

  • The new Export to Excel supports up to 1.5 million cells.
  • 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.



Was this article helpful?