Options

WebPivotTable use options internally to control its look and feels and behaviours. Change any of these options will trigger WebPivotTable to refresh and the changes will take effect immediately.

Setting these options is the major mechanism to customize WebPivotTable.

How To Set Options?

There are two ways to set options:

  • Passing options as attribute of web-pivot-table tag

     <web-pivot-table
       options='{"localeFilePath": "./lang/", "locale":"en", "leavePageWarning": 0}'>
     </web-pivot-table>

    Since attribute of HTML tag or custom element can only be a string, we need stringify options when we passing it this way. WebPivotTable will convert this string to javascript object and deeply merge it into default options.

    It is quite easy to make mistake when write options like this, so it might not a good idea to set complicate options this way. But if we are using WebPivotTable together with some javascript framework, like React and Vue, we can pass javascript object as props and in that case, passing complicate options this way is not an issue.

  • Calling setOptions API

    Instead of passing options as attribute of web-pivot-tab tag, we can also set options by calling setOptions API after WebPivotTable was created on page.

      var wpt = document.getElementsByTagName('web-pivot-table')[0];
    
      wpt.setOptions({
        localeFilePath: "./lang/",
        locale: "en",
        leavePageWarning: 0
      });

    Since we can pass javascript object as parameters of setOptions API and it will be deeply merged into default options, this is a better way to set complicate options.

`leavePageWarning`

OptionTypeDefaultDescription
leavePageWarningbool0When user navigate away from the web page which integrated WebPivotTable, whether we popup a warning dialog or not?

If we set it to true, it will give end users a chance to save their operations before they leave.

This dialog is a system dialog of browsers, different browsers may have different style of dialogs. The prompt message comes from language file Msg_LeavingPage.

`locale`

OptionTypeDefaultDescription
localestring'en'Current language locale, it should be set to one of availableLocales

setLocale API will change this option as well.

`localeFilePath`

OptionTypeDefaultDescription
localeFilePathstring'./lang'locale file path. This is a relative path from your lang directory to web page html file.

`availableLocales`

OptionTypeDefaultDescription
availableLocalesarraysee belowSelectable language locales for users to switch languages.

Default availableLocales:

  availableLocales: [{
    value: 'en', abbr: 'EN', label:'Lbl_English', attach:" ( english ) "
  },{
    value: 'zh-CN', abbr: 'CN', label:'Lbl_Chinese', attach:" ( 中文 ) "
  },{
    value: 'de', abbr: 'DE', label:'Lbl_German', attach:" ( deutsch ) "
  },{
    value: 'tr-TR', abbr: 'TR', label:'Lbl_Turkish', attach:" ( türk ) "
  },{
    value: 'es', abbr: 'ES', label:'Lbl_Spanish', attach:" ( español ) "
  },{
    value: 'pt', abbr: 'PT', label:'Lbl_Portuguese', attach:" ( português ) "
  },{
    value: 'it', abbr: 'IT', label:'Lbl_Italian', attach:" ( italiano ) "
  // },{
  //   value: 'fr', abbr: 'FR', label:'Lbl_French', attach:" ( français ) "
  // },{
  //   value: 'ar', abbr: 'AR', label:'Lbl_Arabic', attach:" ( العربية ) "
  // },{
  //   value: 'ru-RU', abbr: 'RU', label:'Lbl_Russian', attach:" ( русский ) "
  // },{
  //   value: 'nl', abbr: 'NL', label:'Lbl_Dutch', attach:" ( Nederlands ) "
  // },{
  //   value: 'el', abbr: 'EL', label:'Lbl_Greek', attach:" ( Ελληνικά ) "
  // },{
  //   value: 'he', abbr: 'HE', label:'Lbl_Hebrew', attach:" ( עִבְרִית ) "
  // },{
  //   value: 'hi', abbr: 'HI', label:'Lbl_Hindi', attach:" ( हिन्दीة ) "
  // },{
  //   value: 'hu', abbr: 'HU', label:'Lbl_Hungarian', attach:" ( magyar ) "
  // },{
  //   value: 'sv', abbr: 'SV', label:'Lbl_Swedish', attach:" ( svenska ) "
  // },{
  //   value: 'ko', abbr: 'KO', label:'Lbl_Korean', attach:" ( 한국어 ) "
  // },{
  //   value: 'ja', abbr: 'JA', label:'Lbl_Japanese', attach:" ( 日本語 ) "
  // },{
  //   value: 'vn', abbr: 'VN', label:'Lbl_Vietnamese', attach:" ( Tiếng Việt ) "
  }]
PropertyTypeDescription
valuestringlocale code
abbrstringabbreiation to display on header
labelstringmessage name in language files to show as label
attachstringattach to label use original language translation

Please reference Internationalization for more details.

`server`

OptionTypeDefaultDescription
server.fileProxyEnabledbool1enable/disable file Proxy
server.fileProxystring'https://demo.webpivottable.com/wpt/fileProxy'file Proxy
server.fileTimeoutnumber30000file Proxy Timeout
server.xmlaProxyEnabledbool1enable/disable xmla Proxy
server.xmlaProxystring'https://demo.webpivottable.com/wpt/xmlaProxy'xmla Proxy
server.xmlaTimeoutnumber30000xmla Proxy Timeout
server.basicAuthEnabledbool0enable/disable Basic Authentication
server.usernamestring'username'Basic Authentication username
server.passwordstring'password'Basic Authentication password
server.rolesEnabledbool0enable/disable roles control
server.rolesstring'test'comma separate roles list

`limit`

OptionTypeDefaultDescription
limit.sourceDataMaxRowsnumber100000maximum supported rows of source data
limit.sourceDataMaxColumnsnumber1000maximum supported columns of source data
limit.sourceDataMaxDataCellsnumber10000000maximum supported cells of source data
limit.sheetLayoutMaxRowsnumber1000sheet layout maximum rows
limit.sheetLayoutMaxColumnsnumber1000pivot layout maximum columns
limit.sheetLayoutMaxDataCellsnumber10000sheet layout maximum data cells
limit.olapDrillThroughMaxRowsnumber1000Max return rows for each Olap drill through call

`smallScreenBreakpoint`

OptionTypeDefaultDescription
smallScreenBreakpointnumber768with < 768 Small Screen device (phone)

`mediumScreenBreakpoint`

OptionTypeDefaultDescription
mediumScreenBreakpointnumber1080768 <= width <= 1080 (tablet)

`decimalPoint`

OptionTypeDefaultDescription
decimalPointstring'.'decimal point charactor: '.', ','

`thousandsSep`

OptionTypeDefaultDescription
thousandsSepstring','thousands separator: ',' '.', ' '

`defaultToDistinctCount`

OptionTypeDefaultDescription
defaultToDistinctCountbool0Default statistic function of String field to distinct count

`drillThroughBySingleClick`

OptionTypeDefaultDescription
drillThroughBySingleClickbool0dblclick/click to drill through pivot grid or pivot charts

`deferLayoutExcludeFilter`

OptionTypeDefaultDescription
deferLayoutExcludeFilterbool0exclude field sort/filter from defer layout update

`defaultValueFormat`

OptionTypeDefaultDescription
defaultValueFormat.categorystringWptConstants.VALUE_FORMAT_CATEGORY_NUMBERDefault format for negative number
defaultValueFormat.negativestringWptConstants.VALUE_NEGATIVE_RED_MINUSDefault format for negative number

`uiFlags`

OptionTypeDefaultDescription
uiFlags.dropPromptbool1show/hide prompt text for drop file
uiFlags.pivotPromptbool1show/hide prompt text for select fields
uiFlags.connectSourcebool1show/hide Connect to source button
uiFlags.openbool1show/hide Open wpt file button
uiFlags.savebool1show/hide Save button
uiFlags.resetbool1show/hide Reset button
uiFlags.sourcebool1show/hide Data source button
uiFlags.exportbool1show/hide Export button
uiFlags.fullScreenbool1enable/disable "Full Screen" mode
uiFlags.localeSwitchbool1show/hide header locale switch
uiFlags.settingbool1show/hide header Setting button/menu
uiFlags.aboutbool1show/hide header About button/menu Pro Edition only
uiFlags.loadFromCsvbool1show/hide Load data from csv file
uiFlags.loadFromExcelbool1show/hide Load data from excel file
uiFlags.loadFromGssbool1show/hide Load data from "Google Spreadsheet"
uiFlags.loadFromWsbool1show/hide Load data or wpt from "Web Service"
uiFlags.loadFromOlapbool1show/hide Load data or wpt from OLAP cube
uiFlags.saveToLocalbool1show/hide Save WPT dialog "Save to local" Tab
uiFlags.saveToServerbool1show/hide Save WPT dialog "Save to Server" Tab

`styles`

OptionTypeDefaultDescription
styles.fontSizestring'14px'font size
styles.windowHeaderColorstring'lightblue'header/ dialog header background color
styles.toolBarColorstring'aliceblue'toolbar background color
styles.highLightColorstring'rgb(250, 224, 177)'highlight background color
styles.messageColorstring'orangered'message text color
OptionTypeDefaultDescription
fileLinksarray[]Samples link
  [
    {
      type: Constants.sourceType.WPT,
      url: "https://webpivottable.com/testfiles/file9.wpt",
      label: "Test file 9 (olap mode)"
    },{
      type: Constants.sourceType.WPT,
      url: "https://webpivottable.com/testfiles/file8.wpt",
      label: "Test file 8 (memory mode)"
    },{
      type: Constants.sourceType.CSV,
      url: "https://webpivottable.com/testfiles/example.csv",
      label: "USA Selection Poll Data (Total 4,000 records)"
    },{
      type: Constants.sourceType.CSV,
      url: "https://webpivottable.com/testfiles/sales.csv",
      label: "Sales Sample Data (Total 2,823 records)"
    },{
      type: Constants.sourceType.CSV,
      url: "https://webpivottable.com/testfiles/FL_insurance.csv",
      label: "Florida Insurance Data (Total 36,634 records)"
    },{
      type: Constants.sourceType.EXCEL,
      url: "https://webpivottable.com/testfiles/example.xls",
      label: "USA Selection Poll Data (Total 4,000 records)"
    },{
      type: Constants.sourceType.EXCEL,
      url: "https://webpivottable.com/testfiles/sales.xls",
      label: "Sales Sample Data (Total 2,823 records)"
    },{
      type: Constants.sourceType.EXCEL,
      url: "https://webpivottable.com/testfiles/FL_insurance.xls",
      label: "Florida Insurance Data (Total 36,634 records)"
    },{
      type: Constants.sourceType.EXCEL,
      url: "https://webpivottable.com/testfiles/example.xlsx",
      label: "USA Selection Poll Data (Total 4,000 records)"
    },{
      type: Constants.sourceType.EXCEL,
      url: "https://webpivottable.com/testfiles/sales.xlsx",
      label: "Sales Sample Data (Total 2,823 records)"
    },{
      type: Constants.sourceType.EXCEL,
      url: "https://webpivottable.com/testfiles/FL_insurance.xlsx",
      label: "Florida Insurance Data (Total 36,634 records)"
    },{
      type: Constants.sourceType.WSWPT,
      url: "https://webpivottable.com/testfiles/sample91.wpt",
      label: "Predefined small CSV WebPivotTable file"
    },{
      type: Constants.sourceType.WSDATA,
      url: "https://demo.webpivottable.com/wpt/wsSample",
      label: "Simple Sample Web Service"
    },{
      type: Constants.sourceType.GSS,
      url: "https://docs.google.com/spreadsheet/pub?key=0Alkl5EEsxBwBdDFLV2Q4a1NWMmw1TXZBRlFMZ1Rxd0E&output=html",
      label: "USA Selection Poll Data (Total 4,000 records)"
    },{
      type: Constants.sourceType.GSS,
      url: "https://docs.google.com/spreadsheet/pub?key=0Alkl5EEsxBwBdHJMOTh4Sm1BSFlDYXRwVW5lc0xuMVE&output=html",
      label: "Sales Sample Data (Total 2,823 records)"
    },{
      type: Constants.sourceType.OLAP,
      url: "http://olap.flexmonster.com/olap/msmdpump.dll",
      label: "Sample Microsoft Analysis Service"
    },{
      type: Constants.sourceType.OLAP,
      url: "http://sampledata.infragistics.com/data/msmdpump.dll",
      label: "Sample Microsoft Analysis Service 1"
    },{
      type: Constants.sourceType.OLAP,
      url: "http://52.4.22.157:8080/mondrian/xmla",
      label: "Sample Mondrian OLAP Server"
    },{
      type: Constants.sourceType.OLAP,
      url: "http://52.4.22.157:8282/icCube/xmla",
      label: "Sample icCube OLAP Server"
    }
  ]

`customHandlers` Pro edition only.

OptionTypeDefaultDescription
customHandlers.connectSourcebool0enable/disable custom "Connect to source" handler
customHandlers.openWptbool0enable/disable custom "Open wpt" handler
customHandlers.saveWptbool0enable/disable custom "Save wpt" handler
customHandlers.drillThroughbool0enable/disable custom "drill Through" handler

This custom Handlers only available in Pro Edition.

These options only enable/disable custom handlers, the real custom handler should be passing in event listener. Please see How to listen To Events for more details.

`filestack`

OptionTypeDefaultDescription
filestack.enabledbool1enable to use filestack to load data
filestack.keystring''filestack application key

key: "A4bieoUsyR4yBrNPkFIvrz" //demo.webpivottable.com

`sheet`

{
  showGrid: 1,
  expandRows: 1, // Expand/collapse All rows
  expandCols: 1, // Expand/Collapse All columns
  showRowTotals: 1,
  showColTotals: 1,
  showRowSubtotals: 1,
  showColSubtotals: 1,

  grid: {
    showSigns: 1,
    showEmptyAsZero: 0,
    compactForm: 1,
    rowHeaderWidth: 200,
    cellWidth: 100,
    cellHeight: 20,
    theme: "wpt-default",
    noFixedColumns: 0,
    cellStyle: {
      columnHeader: {
        textAlign: "center",
        backgroundColor: "#bfd6eb",
        fontWeight: "bold"
      },
      compoundColumn: {
        verticalAlign: "top",
        textAlign: "left",
        backgroundColor: "#bfd6eb",
        fontWeight: "bold"
      },
      rowHeader: {
        textAlign: "left",
        backgroundColor: "#bfd6eb",
        fontWeight: "bold"
      },
      compoundRow: {
        verticalAlign: "top",
        textAlign: "left",
        backgroundColor: "#bfd6eb",
        fontWeight: "bold"
      },
      totalCell: {
        textAlign: "right",
        backgroundColor: "#72d2df"
      },
      subtotalCell: {
        textAlign: "right",
        backgroundColor: "#d2e9e9"
      },
      dataCell: {
        textAlign: "right",
        backgroundColor: "#eee"
      },
      noDataCell: {
        backgroundColor: "#ddd"
      }
    }
  },

  chart: {
    width: 600,
    height: 500,
    combined: true,

    high: {
      theme: "default", //default, grid, gray, skies, drak-blue, drak-green
      chart: {
        type: "column", //column, bar, line, spline, area, areaspline, pie
        options3d: {
          enabled: false,
          alpha: 15,
          beta: 15,
          depth: 50,
          viewDistance: 25
        }
      },
      credits: {
        enabled: false
      },
      exporting: {
        enabled: false
      },
      navigation: {
        buttonOptions: {
          align: "right", // left, center, right
          verticalAlign: "top" // top, middle, bottom
        }
      },
      legend: {
        enabled: true,
        floating: false,
        layout: "vertical", //horizontal, vertical
        align: "right", // left, center, right
        verticalAlign: "middle", // top, middle, bottom
        reversed: false
      },
      tooltip: {
        enabled: true,
        shadow: true
      },
      plotOptions: {
        series: {
          stacking: null, //null, 'normal', 'percent'
          dataLabels: {
            enabled: false,
            align: "center", // left, center, right
            rotation: 0 // 0 -- 360
          }
        }
      },
      xAxis: {
        labels: {
          enabled: true,
          align: "left", // left, center, right
          rotation: 45 // 0 -- 360
        }
      }
    }
  }
}