Home » Adobe Flex » Create Pivot Table in Adobe Flex

Create Pivot Table in Adobe Flex

Pivot Table Basic Inroduction:

Pivot table is a very useful tool for data anlysis in multiple dimention. This is one of the most usefull utility in Excel file to transform the tabular data into more useful and analyticl fashion. We can see below in s simple usecase how we can transform tabular data in to pivot table view.

Suppose we have given this source data, it would be little difficult to analys the day basis cost analysis for the employees.

pvt1 

 

Better solution is to transfor the given tabulare data into Pivot table for the given dimentions.

Pivot View: 

pvt1form

Creating Pivot in Adobe Flex:

Working with DataGrid comonent.

Adobe flex provides us many wiidgets and components for data presenation and data visualisation like table, grid, progressbas, list, pivot table, charts etc…

Befor going into pivot table, it is desired that developer should be comfortable with datagrid component of flex. 

To read more on adobe flex datagrid follow adobe live docs, it is explained in detail there:

http://livedocs.adobe.com/flex/3/html/help.html?content=dpcontrols_6.html

We will cover a small overview of datagrid comonent.

DataGrid definition in MXML part:

<mx:DataGrid id=”myDataGrid” dataProvider=”{pivotDataSet}” >
 <mx:columns>
       <mx:DataGridColumn headerText=”EMPLOYEE” ataField=”EMPLOYEE” />
       <mx:DataGridColumn headerText=”DATE” dataField=”DATE”/>
  <mx:DataGridColumn headerText=”COST” dataField=”COST”/>
  <mx:DataGridColumn headerText=”AMOUNT” dataField=”AMOUNT”/>
 </mx:columns>
</mx:DataGrid> 

Those who has worked datagrid would be familiar that we need to define a data provide for this grid and need to populate data through this data provider.

Filling data in dataprovider, we can have a httpService ajax call to fetch data from the servers and load into this data provider. We are not going in that topic as it is a separate section, so we assume that user has written required code to lad data into data provider.Looking at the abstract view of onDataReceive function, which you call upon receiving data from the httpService request.

private function onDataRcv(event:ResultEvent):void
{
 //fetch data from the result set data, as per your return value

//data structure
   gridData = dataRcvdFromResulSet;

Having code ready till this level we assume that you datagrid is created and data is also populated.

Creating Pivot Table:

Pivot table compponent has two associated objects.

  1. OLAPDataGrid: This object is used to display pivot table on your page, just like dataGrid component.

<mx:OLAPDataGrid id=”myOLAPDG”  temClick=”olapClick(event)”
selectionMode=”multipleCells” allowMultipleSelection=”false”/>

   2.   OLAPCube: Main object, like a model and holds full pivot defenition, inculuding pivot dimentions and hierarchy.

 

  <mx:OLAPCube name=”FlatSchemaCube”
        dataProvider=”{pivotDataSet}”
        id=”myMXMLCube”
        complete=”runPvtQuery();”>
    
        <mx:OLAPDimension name=”EMPLOYEEDim”>
            <mx:OLAPAttribute name=”EMPLOYEE” dataField=”EMPLOYEE”/>
            <mx:OLAPHierarchy name=”EMPLOYEEHier” hasAll=”true”>
                <mx:OLAPLevel attributeName=”EMPLOYEE”/>
            </mx:OLAPHierarchy>
        </mx:OLAPDimension>
   
        <mx:OLAPDimension name=”PRODUCTDim”>
            <mx:OLAPAttribute name=”PRODUCT” dataField=”PRODUCT”/>
            <mx:OLAPHierarchy name=”PRODUCTHier” hasAll=”true”>
                <mx:OLAPLevel attributeName=”PRODUCT”/>
            </mx:OLAPHierarchy>
        </mx:OLAPDimension>

        <mx:OLAPDimension name=”DATEDim”>
            <mx:OLAPAttribute name=”DATE” dataField=”DATE”/>
            <mx:OLAPHierarchy name=”DATEHier” hasAll=”true”>
                <mx:OLAPLevel attributeName=”DATE”/>
            </mx:OLAPHierarchy>
        </mx:OLAPDimension>
   
        <mx:OLAPMeasure name=”AMOUNT”
            dataField=”AMOUNT”
            aggregator=”SUM”/>
    </mx:OLAPCube>

 

 3:   runPvtQuery:  If you see in the above OLAPCude defenition, a function is specified on creation complete = runPvtQuery. Since we have already specified the dataStructure of the Pivot table and if you notice we have also specified the data source also, then what does this function do?.

This functions job is to run a query on this cube for the specified data set to fetch the desired out put.  You can imagine a group by kind of SQL query which gives you data in grouped format, this is also doing something like that.

private function runPvtQuery():void {
            var cube:IOLAPCube = myMXMLCube;
            var query:IOLAPQuery = getQuery(cube);
            var token:AsyncToken = cube.execute(query);
            token.addResponder(new AsyncResponder(showResult, showFault));
}

 4:   getQuery(): getQuery function actually performs the query on the given data set on the basis of specified dimensions. We cann see here that we have specifed row domension as EMPLOYEE and column dimension as DATE, so it will rearrand the data for the given dimensions and return the data set which can be feeded to the OLAPDataGrid.

 private function getQuery(cube:IOLAPCube):IOLAPQuery {
     var rwId = “EMPLOYEE”;
     var rwDim = “EMPLOYEEDim”;
     var colId = “DATE”;
     var colDim = “DATEDim”;
     var query:OLAPQuery = new OLAPQuery;
     var rowQueryAxis:IOLAPQueryAxis = query.getAxis    (OLAPQuery.ROW_AXIS);
    var RowSet:OLAPSet = new OLAPSet;
    RowSet.addElements(cube.findDimension(rwDim).findAttribute(rwId).children);
   rowQueryAxis.addSet(RowSet);
   var colQueryAxis:IOLAPQueryAxis = query.getAxis (OLAPQuery.COLUMN_AXIS);        
   var DATESet:OLAPSet= new OLAPSet;
    DATESet.addElements(cube.findDimension(colDim).findAttribute(colId).children);
   colQueryAxis.addSet(DATESet);
   return query;      
  }

 

Experience this Example
Download the Code
Advertisements

2 Comments

  1. […] me of follow-up comments via email. Create Pivot Table in Adobe Flex RSS […]

  2. arkilis says:

    Really Good, Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: