Options

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

How To Set Options?

Setting options is the major mechanism to customize WebPivotTable. 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 can only be a string, we need stringify options when we passing it this way. WebPivotTable will convert this string to javascript object and merge it into default options.

  • Calling setOptions API

    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 merged into default options, this is a better way to set complicate options.

Most options are Boolean type, we can set their values to boolean value true or false, we can also set them to number value 1 or 0. But be careful not set them to string value "true" , "false", "1" or "0".

leavePageWarning

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

Set it to true give users a chance to save wpt 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.fileProxyEnabledBoolean1enable/disable file Proxy
server.fileProxyString"https://demo.webpivottable.com/wpt/fileProxy"file Proxy
server.fileTimeoutNumber30000file Proxy Timeout
server.xmlaProxyEnabledBoolean1enable/disable xmla Proxy
server.xmlaProxyString"https://demo.webpivottable.com/wpt/xmlaProxy"xmla Proxy
server.xmlaTimeoutNumber30000xmla Proxy Timeout
server.basicAuthEnabledBoolean0enable/disable Basic Authentication
server.usernameString"username"Basic Authentication username
server.passwordString"password"Basic Authentication password
server.rolesEnabledBoolean0enable/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
defaultToDistinctCountBoolean0Default statistic function of String field to distinct count

drillThroughBySingleClick

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

deferLayoutExcludeFilter

OptionTypeDefaultDescription
deferLayoutExcludeFilterBoolean0exclude field sort/filter from defer layout update

defaultValueFormat

OptionTypeDefaultDescription
defaultValueFormat.categoryStringConstants.valueFormatCategory.NUMBERDefault format for negative number
defaultValueFormat.negativeStringConstants.negativeValueFormat.RED_MINUSDefault format for negative number

uiFlags

OptionTypeDefaultDescription
uiFlags.dropPromptBoolean1show/hide prompt text for drop file
uiFlags.pivotPromptBoolean1show/hide prompt text for select fields
uiFlags.connectSourceBoolean1show/hide Connect to source button
uiFlags.openBoolean1show/hide Open wpt file button
uiFlags.saveBoolean1show/hide Save button
uiFlags.resetBoolean1show/hide Reset button
uiFlags.sourceBoolean1show/hide Data source button
uiFlags.exportBoolean1show/hide Export button
uiFlags.fullScreenBoolean1enable/disable "Full Screen" mode
uiFlags.localeSwitchBoolean1show/hide header locale switch
uiFlags.settingBoolean1show/hide header Setting button/menu
uiFlags.aboutBoolean1show/hide header About button/menu Pro Edition only
uiFlags.loadFromCsvBoolean1show/hide Load data from csv file
uiFlags.loadFromExcelBoolean1show/hide Load data from excel file
uiFlags.loadFromGssBoolean1show/hide Load data from "Google Spreadsheet"
uiFlags.loadFromWsBoolean1show/hide Load data or wpt from "Web Service"
uiFlags.loadFromOlapBoolean1show/hide Load data or wpt from OLAP cube
uiFlags.saveToLocalBoolean1show/hide Save WPT dialog "Save to local" Tab
uiFlags.saveToServerBoolean1show/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

OptionTypeDefaultDescription
customHandlers.connectSourceBoolean0enable/disable custom "Connect to source" handler
customHandlers.openWptBoolean0enable/disable custom "Open wpt" handler
customHandlers.saveWptBoolean0enable/disable custom "Save wpt" handler
customHandlers.drillThroughBoolean0enable/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.enabledBoolean1enable 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
        }
      }
    }
  }
}