ASP.NET Core MVC Controls | ComponentOne
In This Topic
    PivotEngine Class
    In This Topic
    File
    wijmo.olap.js
    Module
    wijmo.olap

    Provides a user interface for interactively transforming regular data tables into Olap pivot tables.

    Tabulates data in the itemsSource collection according to lists of fields and creates the pivotView collection containing the aggregated data.

    Pivot tables group data into one or more dimensions. The dimensions are represented by rows and columns on a grid, and the data is stored in the grid cells.

    Constructor

    Properties

    Methods

    Events

    Constructor

    constructor

    constructor(options?: any): PivotEngine
    

    Initializes a new instance of the PivotEngine class.

    Parameters
    • options: any
    Optional

    JavaScript object containing initialization data for the field.

    Returns
    PivotEngine

    Properties

    allowFieldEditing

    Gets or sets a value that determines whether users should be allowed to edit the properties of the PivotField objects owned by this PivotEngine.

    If you set this property to false, the context menus shown by controls such as the **PivotGrid** and **PivotPanel** will not include an option for changing the field settings.

    The default value for this property is **true**.

    Type
    boolean

    async

    Gets or sets a value that determines whether view updates should be generated asynchronously.

    This property is set to true by default, so summaries over large data sets are performed asynchronously to prevent stopping the UI thread.

    The default value for this property is **true**.

    Type
    boolean

    autoGenerateFields

    Gets or sets a value that determines whether the engine should generate fields automatically based on the itemsSource.

    If you set this property to true, the engine will generate fields for each property of the items in the itemsSource. The binding property of the auto-generated fields is set to the property name, and their header property is set to a string obtained by capitalizing the first letter of the binding and adding spaces before each capital letter.

    For example, a 'customerName' property will cause the engine create a field with binding set to 'customerName' and header set to 'Customer Name'.

    When adding fields to one of the view lists using strings, you must specify the header, not the binding (unlike bindings, field headers must be unique).

    The default value for this property is **true**.

    Type
    boolean

    collectionView

    Gets the ICollectionView that contains the raw data.

    Type
    ICollectionView

    columnFields

    Gets the list of PivotField objects that define the fields shown as columns in the output table.

    Type
    PivotFieldCollection

    defaultFilterType

    Gets or sets the default filter type (by value or by condition).

    The default value for this property is **null**, which causes the engine to use **FilterType.Both** on the client or **FilterType.Condition** on the server.

    Type
    FilterType

    exclusiveValueSearch

    Gets or sets a value that determines whether the filter should include only values selected by the filterText property.

    The default value for this property is **true**, which matches Excel's behavior.

    Set it to **false** to disable this behavior, so searching only affects which items are displayed on the list and not which items are included in the filter.

    Type
    boolean

    fields

    Gets the list of PivotField objects exposed by the data source.

    This list is created automatically whenever the itemsSource property is set.

    Pivot views are defined by copying fields from this list to the lists that define the view: valueFields, rowFields, columnFields, and filterFields.

    For example, the code below assigns a data source to the PivotEngine and then defines a view by adding fields to the rowFields, columnFields, and valueFields lists.

    import { PivotEngine } from '@mescius/wijmo.olap';
    
    // create pivot engine
    let pe = new PivotEngine();

    // set data source (populates fields list) pe.itemsSource = this.getRawData();
    // prevent updates while building Olap view pe.beginUpdate();
    // show countries in rows pe.rowFields.push('Country');
    // show categories and products in columns pe.columnFields.push('Category'); pe.columnFields.push('Product');
    // show total sales in cells pe.valueFields.push('Sales');
    // done defining the view pe.endUpdate();

    Type
    PivotFieldCollection

    filterFields

    Gets the list of PivotField objects that define the fields used as filters.

    Fields on this list do not appear in the output table, but are still used for filtering the input data.

    Type
    PivotFieldCollection

    hasDeferredUpdates

    Gets a value that indicates whether the viewDefinition have been changed but the changes have not benn applied yet (deferUpdate mode)

    Type
    boolean

    isServer

    Gets a value that determines whether the engine is bound to a server itemsSource or is using local data.

    Type
    boolean

    isUpdating

    Gets a value that indicates whether the engine is currently being updated.

    Type
    boolean

    isViewDefined

    Gets a value that determines whether a pivot view is currently defined.

    A pivot view is defined if any of the valueFields, rowFields, or columnFields lists are not empty.

    Type
    boolean

    itemsSource

    Gets or sets the array or ICollectionView that contains the raw data to be analyzed, an object describing an SSAS cube service, or a string containing the URL for a ComponentOne DataEngine service.

    The first option (using an array or ICollectionView) is the simplest, but it limits the amount of raw data you can handle. Loading the raw data into an array can take a long time if the data set contains more than about 50,000 items or so.

    To use this option, simply set the itemsSource property to any JavaScript array containing the raw data to be analyzed. For example:

    import { PivotEngine } from '@mescius/wijmo.olap';
    let ng = PivotEngine({
        itemsSource = getDataArray(1000);
    });
    

    The second option (direct connection to OLAP SSAS cubes) allows the PivotEngine to connect directly to OLAP cubes provided by SSAS servers. This option removes the size limitations mentioned above and allows you to analyze data sets with millions or billions of records.

    To use this option, set the itemsSource property to an object that specifies how the component should access the service. For example:

    import { PivotEngine } from '@mescius/wijmo.olap';
    let ng = PivotEngine({
        itemsSource: {
            url: 'http://ssrs.componentone.com/OLAP/msmdpump.dll',
            cube: 'Adventure Works',
            catalog: 'AdventureWorksDW2012Multidimensional'
        }
    });
    

    The **catalog** property is optional, but **url** and **cube** are required.

    The preceding example works with SSAS servers that allow anonymous access. For servers that require Basic Authentication, you should also include the appropriate **user** and **password** members as part of the itemsSource object.

    When connecting directly to OLAP SSAS cubes, users will not be able to filter fields by value. They will still be able to filter by condition.

    The third option, ComponentOne data engine services, allows you to analyze large datasets on a server without downloading the raw data to the client. You can use our high-performance FlexPivot services or interface with Microsoft's SQL Server Analysis Services OLAP Cubes.

    To use ComponentOne data engine services, set the itemsSource property to a string containing the URL of the data service. For example:

    import { PivotEngine } from '@mescius/wijmo.olap';
    let ng = new wijmo.olap.PivotEngine({
        itemsSource: 'http://demos.componentone.com/ASPNET/c1webapi/4.5.20193.222/api/dataengine/cube'
    });
    

    The PivotEngine sends view definitions to the server, where summaries are calculated and returned to the client.

    When connecting ComponentOne DataEngine data sources, users will not be able to filter fields by value. They will still be able to filter by condition.

    For more information about the ComponentOne DataEngine services please refer to the online documentation.

    Our OlapServerIntro sample shows all options working with a single PivotEngine.

    Type
    any

    pivotView

    Gets the ICollectionView containing the output pivot view.

    Type
    ICollectionView

    rowFields

    Gets the list of PivotField objects that define the fields shown as rows in the output table.

    Type
    PivotFieldCollection

    serverMaxDetail

    Gets or sets the maximum number of records the getDetail method should retrieve from the server.

    The default value for this property is **1,000**, which is a reasonable amount of detail in many scenarios. If you want to allow more detail records to be retrieved, increase the value of this property.

    Type
    number

    serverPollInterval

    Gets or sets the amount of time, in milliseconds, that the engine should wait before polling the server for progress status while retrieving results.

    The default value for this property is **500** milliseconds, which causes the engine to poll the server for a status update every half second.

    Type
    number

    serverTimeout

    Gets or sets the maximum amount of time, in milliseconds, that the engine should wait for the results to come back from the server.

    The default value for this property is **60,000** milliseconds, or one minute. If you expect server operations to take longer than that, set the property to a higher value.

    Type
    number

    showColumnTotals

    Gets or sets a value that determines whether the output pivotView should include columns containing subtotals or grand totals.

    The default value for this property is **ShowTotals.GrandTotals**.

    Type
    ShowTotals

    showRowTotals

    Gets or sets a value that determines whether the output pivotView should include rows containing subtotals or grand totals.

    The default value for this property is **ShowTotals.GrandTotals**.

    Type
    ShowTotals

    showZeros

    Gets or sets a value that determines whether the Olap output table should use zeros to indicate missing values.

    The default value for this property is **false**.

    Type
    boolean

    sortableGroups

    Gets or sets a value that determines whether the engine should sort groups when sorting the value fields (measures) or whether it should keep the group order and the data only within each group.

    The default value for this property is **true**.

    Type
    boolean

    sortOnServer

    Gets or sets a value that indicates whether the summary data received from the server is already sorted.

    If this property is set to true, the PivotEngine will not sort the data it receives from the server.

    This property should be used only in conjunction with custom servers that return the aggregated data properly sorted, typically using custom logic not available in the standard PivotEngine.

    The default value for this property is **false**.

    Type
    boolean

    totalsBeforeData

    Gets or sets a value that determines whether row and column totals should be displayed before or after regular data rows and columns.

    If this value is set to true, total rows appear above data rows and total columns appear on the left of regular data columns.

    The default value for this property is **false**.

    Type
    boolean

    valueFields

    Gets the list of PivotField objects that define the fields summarized in the output table.

    Type
    PivotFieldCollection

    viewDefinition

    Gets or sets the current pivot view definition as a JSON string.

    This property is typically used to persist the current view as an application setting.

    For example, the code below implements two functions that save and load view definitions using local storage:

    // save/load views
    function saveView() {
      localStorage.viewDefinition = pivotEngine.viewDefinition;
    }
    function loadView() {
      pivotEngine.viewDefinition = localStorage.viewDefinition;
    }
    

    Type
    string

    Methods

    beginUpdate

    beginUpdate(): void
    

    Suspends the refresh processes until next call to the endUpdate.

    Returns
    void

    cancelPendingUpdates

    cancelPendingUpdates(): void
    

    Cancels any pending asynchronous view updates.

    Returns
    void

    deferUpdate

    deferUpdate(fn: Function): void
    

    Executes a function within a beginUpdate/endUpdate block.

    The control will not be updated until the function has been executed. This method ensures endUpdate is called even if the function throws an exception.

    Parameters
    Returns
    void

    editField

    editField(field: PivotField): void
    

    Shows a settings dialog where users can edit a field's settings.

    Parameters
    Returns
    void

    endUpdate

    endUpdate(): void
    

    Resumes refresh processes suspended by calls to beginUpdate.

    Returns
    void

    getDetail

    getDetail(item: any, binding: string): any[]
    

    Gets an array containing the records summarized by a property in the pivotView list.

    If the engine is connected to a PivotEngine server, the value returned is an ObservableArray that is populated asynchronously.

    Parameters
    • item: any

      Data item in the pivotView list.

    • binding: string

      Name of the property being summarized.

    Returns
    any[]

    getDetailView

    getDetailView(item: any, binding: string): ICollectionView
    

    Gets an ICollectionView containing the records summarized by a property in the pivotView list.

    Parameters
    • item: any

      Data item in the pivotView list.

    • binding: string

      Name of the property being summarized.

    Returns
    ICollectionView

    getKeys

    getKeys(item: any, binding: string): any
    

    Gets an object with information about a property in the pivotView list.

    The object returned has two properties, 'rowKey' and 'colKey'. Each of these contains two arrays, 'fields' and 'values'. Together, this information uniquely identifies a value summarized by the PivotEngine.

    For example, calling getKeys against a pivot view with two row fields 'Product' and 'Country', and a single column field 'Active' would return an object such as this one:

    ``` { rowKey: { fields: [ 'Product', 'Country'], values: [ 'Aoba', 'Japan' ] }, colKey: { fields: [ 'Active' ], values: [ true ] } } ```

    The object identifies the subset of data used to obtain one summary value. In this case, this value represents all data items for product 'Aoba' sold in Japan with Active state set to true.

    Parameters
    • item: any

      Data item in the pivotView list.

    • binding: string

      Name of the property being summarized.

    Returns
    any

    invalidate

    invalidate(): void
    

    Invalidates the view causing an asynchronous refresh.

    Returns
    void

    onError

    onError(e: RequestErrorEventArgs): boolean
    

    Raises the error event.

    Parameters
    Returns
    boolean

    onItemsSourceChanged

    onItemsSourceChanged(e?: EventArgs): void
    

    Raises the itemsSourceChanged event.

    Parameters
    Optional
    Returns
    void

    onUpdatedView

    onUpdatedView(e?: EventArgs): void
    

    Raises the updatedView event.

    Parameters
    Optional
    Returns
    void

    onUpdatingView

    onUpdatingView(e: ProgressEventArgs): void
    

    Raises the updatingView event.

    Parameters
    Returns
    void

    onViewDefinitionChanged

    onViewDefinitionChanged(e?: EventArgs): void
    

    Raises the viewDefinitionChanged event.

    Parameters
    Returns
    void

    refresh

    refresh(force?: boolean): void
    

    Summarizes the data and updates the output pivotView.

    Parameters
    Returns
    void

    removeField

    removeField(field: PivotField): void
    

    Removes a field from the current view.

    Parameters
    Returns
    void

    Events

    error

    Occurs when there is an error getting data from the server.

    Arguments
    RequestErrorEventArgs

    itemsSourceChanged

    Occurs after the value of the itemsSource property changes.

    Arguments
    EventArgs

    updatedView

    Occurs after the engine has finished updating the pivotView list.

    Arguments
    EventArgs

    updatingView

    Occurs when the engine starts updating the pivotView list.

    Arguments
    ProgressEventArgs

    viewDefinitionChanged

    Occurs after the view definition changes.

    Arguments
    EventArgs