Menu
Download Free Trial
  1. API reference
  2. Welcome
    1. Component overview
    2. Quickstart
    3. System requirements
    4. Troubleshooting
    5. Installation troubleshooting
    6. Managing license keys
  3. Connecting to Data Source
    1. JSON
      1. Connecting to JSON
      2. Data types in JSON
    2. CSV
      1. Connecting to CSV
      2. Data types in CSV
    3. SQL database
      1. Connecting to SQL database
      2. Connecting to database with .NET
      3. Connecting to database with .NET Core
      4. Connecting to database with Java
      5. Connecting to database with PHP
    4. Microsoft Analysis Services
      1. Connecting to Microsoft Analysis Services
      2. Getting started with Accelerator
      3. Installing Accelerator as a Windows Service
      4. Configuring username/password protection
      5. Configuring secure HTTPS connection
      6. Troubleshooting
    5. Pentaho Mondrian
      1. Connecting to Pentaho Mondrian
      2. Getting started with Accelerator
      3. Configuring Mondrian roles
      4. Сonfiguring username/password protection
      5. Сonfiguring secure HTTPS connection
      6. Troubleshooting
    6. icCube
  4. Configuring report
    1. What is a report
    2. Data source
    3. Slice
    4. Options
    5. Number formatting
    6. Conditional formatting
    7. Set report to the component
    8. Get report from the component
    9. Date and time formatting
    10. Configuring global options
    11. Export and print
    12. Calculated values
    13. Custom sorting
  5. Integration with frameworks
    1. Available tutorials
    2. Integration with jQuery
    3. Integration with AngularJS
    4. Integration with Angular 2
    5. Integration with Angular 4
    6. Integration with React
    7. Integration with RequireJS
    8. Integration with TypeScript
    9. Integration with ASP.NET
    10. Integration with JSP
    11. Integration with PhoneGap
  6. Integration with charts
    1. Integration with Highcharts
    2. Integration with FusionCharts
    3. Integration with Google Charts
    4. Integration with any charting library
  7. Customizing
    1. Customizing toolbar
    2. Customizing appearance
    3. Localizing component
  8. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.3 to 2.4
    4. Migration guide from 2.2 to 2.3
  9. Older Versions
    1. Documentation 2.3
    2. Documentation 2.2
    3. API reference - Flex
Table of contents

Data source

Data source is required part of the report object. Web Pivot Table Component supports the data from OLAP data sources, SQL databases, CSV and JSON static files, inline JSON data. Each data source requires specific properties set inside dataSource section of report object. Read more in the following sections:

Data from OLAP data sources

OLAP data sources include MS Analysis Services, Mondrian and icCube. There are two ways to connect to OLAP cube using Flexmonster Pivot Table:

  1. via XMLA protocol – an industry standard for data access in analytical systems (for Microsoft Analysis Services, Mondrian and icCube).
  2. via our Data Speed Accelerator – special server-side utility developed by Flexmonster (for Microsoft Analysis Services and Mondrian).

Microsoft Analysis Services

Here is a list of dataSource properties used to connect to Microsoft Analysis Services:

  • catalog – String. The data source catalog name.
  • cube – String. Given catalog’s cube’s name.
  • dataSourceInfo (optional) – String. The service info.
  • dataSourceType – String. Type of data source. In this case it is "microsoft analysis services".
  • proxyUrl – String. The path to proxy URL.
  • binary (optional) – Boolean. Flag to use Data Speed Accelerator instead of XMLA protocol.
  • effectiveUserName (optional) – String. Use when an end user identity must be impersonated on the server. Specify the account in a domain\user format.
  • localeIdentifier (optional) – Number. Microsoft Locale ID Value for your language.
  • roles (optional) – String. Comma-delimited list of predefined roles to connect to a server or database using permissions conveyed by that role. If this property is omitted, all roles are used, and the effective permissions are the combination of all roles. Supported only via XMLA protocol.
  • subquery (optional) – String. The parameter to set the server-side filter which helps to decrease the size of the response from the OLAP cube. For example, to show reports only for one specific year set subquery the following way: "subquery": "select {[Delivery Date].[Calendar].[Calendar Year].&[2008]} on columns from [Adventure Works]".

Here is how the connection to MSAS via XMLA represented in dataSource:

{
    dataSource: {
        dataSourceType: "microsoft analysis services",
		// URL to msmdpump.dll 
        proxyUrl: "http://olap.flexmonster.com/olap/msmdpump.dll",
        catalog: "Adventure Works DW Standard Edition",
        cube: "Adventure Works",
        // Microsoft Locale ID Value for French
        localeIdentifier: 1036,
        // roles from MSAS
        roles: "admin,manager",
        subquery: "select 
            {[Delivery Date].[Calendar].[Calendar Year].&[2008]} 
            on columns from [Adventure Works]"
    }
}

Check out on JSFiddle.

Here is how the connection to MSAS via Data Speed Accelerator represented in dataSource:

{
    dataSource: {
        dataSourceType: "microsoft analysis services",
        proxyUrl: "http://localhost:50005",
        catalog: "Adventure Works DW Standard Edition",
        cube: "Adventure Works",
        binary: true,
        // Microsoft Locale ID Value for French
        localeIdentifier: 1036,
        subquery: "select 
            {[Delivery Date].[Calendar].[Calendar Year].&[2008]} 
            on columns from [Adventure Works]"
    }
}

You can read all the details about Accelerator here.

Mondrian

Here is a list of dataSource properties used to connect to Mondrian:

  • catalog – String. The data source catalog name.
  • cube – String. Given catalog’s cube’s name.
  • dataSourceInfo – String. The service info.
  • dataSourceType – String. Type of data source. In this case it is "mondrian".
  • proxyUrl – String. The path to proxy URL.
  • binary (optional) – Boolean. Flag to use Data Speed Accelerator instead of XMLA protocol.
  • roles (optional) – String. Comma-delimited list of predefined roles to connect to a server or database using permissions conveyed by that role. If this property is omitted, all roles are used, and the effective permissions are the combination of all roles.

Here is how the connection to Mondrian via XMLA represented in dataSource:

{
    dataSource: {
        dataSourceType: "mondrian",
		// URL to XMLA provider 
        proxyUrl: "http://olap.flexmonster.com:8080/mondrian/xmla",
        dataSourceInfo: "MondrianFoodMart",
        catalog: "FoodMart",
        cube: "Sales"
    }
}

Try the example on JSFiddle.

Here is how the connection to Mondrian via Data Speed Accelerator represented in dataSource:

{
    dataSource: {
        dataSourceType: "mondrian",
        proxyUrl: "localhost:50006",
        dataSourceInfo: "MondrianFoodMart",
        catalog: "FoodMart",
        cube: "Sales",
        binary: true,
        // Mondrian roles
        roles: "California manager"
    }
}

You can read all the details about Accelerator here.

icCube

Here is a list of dataSource properties used to connect to icCube:

  • catalog – String. The data source catalog name.
  • cube – String. Given catalog’s cube’s name.
  • dataSourceInfo (optional) – String. The service info.
  • dataSourceType – String. Type of data source. In this case it is "iccube".
  • proxyUrl – String. The path to proxy URL.
  • subquery (optional) – String. The parameter to set the server-side filter which helps to decrease the size of the response from the OLAP cube. For example, to show reports only for one specific year set subquery the following way: "subquery": "select {[Delivery Date].[Calendar].[Calendar Year].&[2008]} on columns from [Adventure Works]".

Here is how the connection to icCube via XMLA represented in dataSource:

{
    dataSource: {
        dataSourceType: "iccube",
        /* URL to XMLA service */
        proxyUrl: "http://olap.flexmonster.com:8282/icCube/xmla",
        /* Catalog name */
        catalog: "Sales",
        /* Cube name */
        cube: "Sales"
    }
}

See it on JSFiddle. Read more: Connecting to icCube.

SQL databases

Here is a list of dataSource properties used to connect to SQL databases via Compressor:

  • dataSourceType – String. Type of data source. In this case it is "ocsv".
  • filename – String. The URL to the server-side script which generates data.

Our Flexmonster Data Compressor returns files in OCSV (Optimized CSV) format and you need to define dataSourceType explicitly:

{
    dataSource: {
        dataSourceType: "ocsv",
        /* URL to the Data Compressor .Net, Java or PHP */
        filename: "http://localhost:55772/api/flexmonster/get"
    }
}
Read here more about our Data Compressor.

CSV data sources

CSV data source can be:

  • file from the local file system
  • remote static file
  • data generated by the server-side script

Here is a list of dataSource properties used to connect to CSV data sources:

  • browseForFile – Boolean. Defines whether you want to load the file from the local file system (true) or not (false). Default value is false.
  • dataSourceType – String. Type of data source. In this case it is "csv".
  • fieldSeparator – String. Defines specific fields separator to split CSV row. There is no need to define it if CSV fields are separated by , or ;. This property is used if another char separates fields. For example, if you use TSV, where tab char is used to separate fields in the row, fieldSeparator parameter should be defined as "\t".
  • filename – String. The URL to the file or to the server-side script which generates data.
  • ignoreQuotedLineBreaks (starting from v2.1) – Boolean. Indicates whether the line breaks in quotes will be ignored (true) in CSV files or not (false). Default value is true, which makes CSV parsing faster. Set it to false only if your data source has valuable for you line breaks in quotes. Please note that this might slow down CSV parsing a little bit.
  • recordsetDelimiter – String. Defines which char is used in CSV to denote the end of CSV row. Default value is "↵".

In the following example data is taken from CSV file where colon char (:) is used to separate fields in the row. Line breaks in quotes are not ignored:

{
    dataSource: {
        /* The URL to CSV file or local path */
        filename: 'colon-data.csv',
        fieldSeparator: ':',
        ignoreQuotedLineBreaks: false,
    }
}

If data is generated by the server-side script, dataSourceType should be defined explicitly:

{
    dataSource: {
        dataSourceType: "csv",
        filename: "script_which_returns_csv_file"
    }
}

JSON data sources

JSON data source can be:

  • file from the local file system
  • data generated by the server-side script
  • inline JSON

Here is a list of dataSource properties used to connect to JSON data sources:

  • browseForFile – Boolean. Defines whether you want to load the file from the local file system (true) or not (false). Default value is false.
  • data (starting from v2.2) – Property to set JSON data if it is already on the page.
  • dataSourceType – String. Type of data source. In this case it is "json".
  • filename – String. The URL to the file or to the server-side script which generates data.

JSON data set through the file from the local file system:

{
    dataSource: {
        /* Path to the local JSON file */
        filename: "data.json"
    }
}

If data is generated by the server-side script, dataSourceType should be defined explicitly:

{
    dataSource: {
        dataSourceType: "json",
        filename: "script_which_returns_json_file"
    }
}

Inline JSON:

{
    dataSource: {
        /* jsonData variable contains JSON data */
        data: jsonData
    }
}

Change data source using Toolbar

Please use Connect to choose another data source or Open to load another report in run time. Use Save to save the report with the current data source.

dataSource

Data in pivot table will be updated and will be saved within the report.

Data source via API

API calls connectTo(), load() and open() are used to change data source in run time. API call save() is used to save the report.