Home » Adobe Flex

Category Archives: Adobe Flex

Advertisements

Drill Down Pivot Table in Adobe Flex

This has been a very demanding feature among most of the business users to display drill down Pivot Table. In our previous blog we have gone through about how to create a Pivot Table. Drill down Pivot table enable you to click on any cell and it would display the full result set for that summary data.



Previous blog: How to create simple pivot table.
Experience Previous Example
Download the Code

Creating Drill Down Pivot Table:                                                                                                                                                                                                                                                       We can devide the work in four following logical sections. Each one has its own specific purpose.   

1: Adding event listener on Cell Click.

2: Fetching Selected Cell information on Cell Click.

3: Make another service call for this selected information.

4: Display the received drilled data in a grid\popup window or anyother option of your choice.

 

1: Adding event listener on cell click:

We know that in flex OLAPDataGrid is the UI component to display the Pivot table and OLAPCube is the model object which holds information of Pivot Dimentions.

<mx:OLAPDataGridFONT-SIZE: 10pt; COLOR: #990000; FONT-FAMILY: ‘Courier New'”>myOLAPDG”  defaultCellString=”” visible=”true” itemClick=”olapClick(event)” selectionMode=”multipleCells” allowMultipleSelection=”false” dataProvider=”{dataSet}”      />  

OLAPData grid is very much similar to the dataGrid component. Default grid allows you to select a row at a time, so you will have to specify that your selection usnit is Cell by setting property selectionMode=”multipleCell”.

We can add an event listener by setting it’s itemClick property by specifying a event listener function. You can also do this by using AddEventListener api at on page load. Other properties you see in code snippet are the normal we can specify with any normal pivot table always like dataprovider etc.                                   

2: Fecthing cell data on cell click.
We have written a function by name olapClick and calling the same on pivot click, now lets look into this fucntion. Main job of this fucntion is to findout the clicked cell and its correcponding Row and Column field name and it’s value.

function olapClick(event:ListEvent){
pvtSlectedColNo = event.columnIndex -1 ; // row count starts from 0 , but colum starts from 1
pvtSelectedRowNo = event.rowIndex;
pivotRowField = “EMPLOYEE”;
pivotRowFieldDim = “EMPLOYEEDim”;
pivotColField = “DATE”;
pivotColFieldDim = “DATEDim”;

var op:OLAPAxisPosition = myOLAPDG.selectedItem as OLAPAxisPosition;
var pivotData:OLAPResult= myOLAPDG.dataProvider as OLAPResult;
var cell:OLAPCell = pivotData.getCell(pvtSelectedRowNo,pvtSlectedColNo) as OLAPCell;
var axis:IOLAPResultAxis = pivotData.getAxis(1) as IOLAPResultAxis;
var dim:IOLAPDimension = myMXMLCube.findDimension(pivotColFieldDim) as IOLAPDimension;
var att:IOLAPAttribute = dim.findAttribute(pivotColField) as IOLAPAttribute;
var rowList:IList = att.children;
var temp:IOLAPMember = rowList[pvtSlectedColNo];
pivotColSelected = temp.displayName
var colList:Array = myOLAPDG.columns;
dim = myMXMLCube.findDimension(pivotRowFieldDim) as IOLAPDimension;
att = dim.findAttribute(pivotRowField) as IOLAPAttribute;
var rowList:IList = att.children;
var temp:IOLAPMember = rowList[pvtSelectedRowNo];
pivotRowSelected = temp.displayName

Alert.show(pivotDataSet+pivotRowField+pivotRowSelected);
Alert.show(tempData+pivotColField+pivotColSelected);
setDrillData();
}

In this function we have tried to fetch the value of selectedRow , selectedColumn, and selected Cell data. Later this value would be used for expandable report or view. We can use this piece of information to make another AJAX call by httpService request and once we receive the resultset of the second call we can populate this drilled down information in any other grid or popup window.

yet to write on last two sections…

Experience Drill Down Pivot Table
Download the Code
Advertisements

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