1.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
  • Microsoft Analysis Services
  • Mondrian
  • icCube
• SQL databases
• CSV data sources
• JSON data sources
• Change data source using Toolbar

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.

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.