3 Replies Latest reply on Jun 26, 2007 3:29 PM by GeorgeWS

    datagrid totals

    GeorgeWS Level 1
      I have been looking at all the samples, (there are not many) about how to dynamicaly have a total at the bottom row in a grid so when its filtered the total row at the bottom will recalculate on the fly. I see the helpqlodhelp sample and the sample works I just cant make it work using my arrayCollection and datagrid. I need a sample that gets the data from a remoteObject puts it in an arrayCollection and outputs to a grid. Is there a sample like that? I have this - there is no totaling, just a RO, filter Process, and a Datagrid.

      Any Help would be great thanks

      George

      <mx:Application xmlns:mx=" http://www.adobe.com/2006/mxml" layout="absolute" creationComplete="initVars()" >
      <mx:Script>
      <![CDATA[

      import mx.collections.ArrayCollection;
      [Bindable]
      public var myData:ArrayCollection;

      public function clearAll():void{

      myData=new ArrayCollection();
      }

      public function handleQueryResult(event:ResultEvent):void{
      myData=event.result as ArrayCollection;
      myData.filterFunction=processFilter;
      }

      public function processFilter(item:Object):Boolean
      {
      var result:Boolean=false;
      var pattern:RegExp = new RegExp("^"+txtFilter.text,"i");
      if(!item[String(columnfilter.selectedItem.data)].length
      || item[String(columnfilter.selectedItem.data)].match(pattern))
      {
      result=true;
      }
      return result;
      }

      [Bindable]
      public var myfilter: Array = [ {label:"DBA", data:"CAD105"}, {label:"Customer", data:"Cusnsd"}, {label:"Address", data:"CAD205"}, {label:"City", data:"CAD405"}, {label:"Zip", data:"PCD105"}, {label:"Rep", data:"SLMNSD"} ];

      [Bindable]
      public var selectedItem:Object;

      public function initVars() : void {
      qAccInfo.AccInfo.send();
      }
      ]]>
      </mx:Script>


      <mx:RemoteObject
      id="qAccInfo"
      destination="ColdFusion"
      source="youngsmarket.programs.accinfo.accinfo.bin.accinfo"
      showBusyCursor="true" >
      <mx:method name="AccInfo" result="handleQueryResult(event)" fault="Alert.show(event.fault.message)">
      </mx:method>
      </mx:RemoteObject>

      <mx:TextInput width="75" id="txtFilter" change="myData.refresh()"/>
      <mx:ComboBox id="columnfilter" dataProvider="{myfilter}" width="125"
      close="selectedItem=ComboBox(event.target).selectedItem" change="myData.refresh()" />

      <mx:DataGrid id="myDataGrid" dataProvider="{myData}" width="100%" height="100%">
      <mx:columns>
      <mx:DataGridColumn dataField="Cusnsd" headerText="Cust" minWidth="40" />
      <mx:DataGridColumn dataField="CAD105" headerText="DBA" minWidth="120" />
      <mx:DataGridColumn dataField="CAD205" headerText="Address" minWidth="140"/>
      <mx:DataGridColumn dataField="CAD405" headerText="City" minWidth="75"/>
      <mx:DataGridColumn dataField="PCD105" headerText="Zip" width="50"/>
      <mx:DataGridColumn dataField="PHON05" headerText="Phone" minWidth="60"/>
      <mx:DataGridColumn dataField="SLMNSD" headerText="Rep" width="50"/>
      <mx:DataGridColumn dataField="DollarsL" headerText="Liquor" minWidth="50" textAlign="right"/>
      <mx:DataGridColumn dataField="DollarsW" headerText="Wine" minWidth="50" textAlign="right"/>
      <mx:DataGridColumn dataField="DollarsB" headerText="Beer" minWidth="50" textAlign="right"/>
      <mx:DataGridColumn dataField="DollarsO" headerText="Other" minWidth="50" textAlign="right"/>
      <mx:DataGridColumn dataField="DollarsT" headerText="Total" minWidth="50" textAlign="right"/>
      </mx:columns>
      </mx:DataGrid>
        • 1. Re: datagrid totals
          GeorgeWS Level 1
          Anyone?
          • 2. Re: datagrid totals
            BLXWebMaster Level 1
            I have a solution that comes to mind. One concern I have is how to handle sorting etc. If you add a row after you you calc the totals, it will be in the array and may look wierd when sorting.

            The code I created will easily show you how to get the totals for each column when the Array changes due to Filter or init.

            I would display column totals as text which this sample does. If you want in DataGrid, you can easily modify to use AddItem at the last location of the array and place the values in it.

            Or you could create a second array and datagrid that stays at the bottom, so it is basically static and not part of the array.

            <?xml version="1.0" encoding="utf-8"?>
            <mx:Application
            xmlns:mx=" http://www.adobe.com/2006/mxml"
            layout="absolute"
            creationComplete="initVars()"
            height="100%" width="100%">
            <mx:Script>
            <![CDATA[
            import mx.collections.ArrayCollection;
            import mx.rpc.events.*;
            import mx.events.*;
            import mx.controls.*;

            [Bindable]
            public var myData:ArrayCollection;

            public function clearAll():void{
            myData=new ArrayCollection();
            }

            private var myDataArray:Array =
            [
            {Cusnsd: "Cust A", CAD105:"DBA A", CAD205:"Address A", CAD405:"City A", PCD105:"Zip A", PHON05:"111-111-2222", SLMNSD:"Rep A", DollarsL:"5000.00", DollarsW:"5000.00", DollarsB:"5000.00", DollarsO:"5000.00", DollarsT:"20000.00"},
            {Cusnsd: "Cust B", CAD105:"DBA B", CAD205:"Address A", CAD405:"City A", PCD105:"Zip A", PHON05:"222-333-4444", SLMNSD:"Rep B", DollarsL:"6000.00", DollarsW:"6000.00", DollarsB:"6000.00", DollarsO:"6000.00", DollarsT:"24000.00"},
            {Cusnsd: "Cust C", CAD105:"DBA C", CAD205:"Address A", CAD405:"City A", PCD105:"Zip A", PHON05:"333-444-5555", SLMNSD:"Rep C", DollarsL:"7000.00", DollarsW:"7000.00", DollarsB:"7000.00", DollarsO:"7000.00", DollarsT:"28000.00"}
            ];

            public function handleQueryResult(event:ResultEvent):void{
            myData=event.result as ArrayCollection;
            myData.filterFunction=processFilter;

            }

            public function processFilter(item:Object):Boolean
            {
            var result:Boolean=false;
            var pattern:RegExp = new RegExp("^"+txtFilter.text,"i");
            if(!item[String(columnfilter.selectedItem.data)].length
            || item[String(columnfilter.selectedItem.data)].match(pattern))
            {
            result=true;
            }
            return result;
            }

            private function refreshData():void{
            myData.refresh();
            calcMyData();
            }

            [Bindable]
            public var myDataTotals:ArrayCollection;

            public function calcMyData():void
            {
            var DollarsL: Number = 0;
            var DollarsW: Number = 0;
            var DollarsB: Number = 0;
            var DollarsO: Number = 0;
            var DollarsT: Number = 0;
            var length : int = myData.length;
            for( var i : int = 0; i < length; i++ )
            {
            var item : Object = myData.getItemAt( i );
            var DollarsL_value : Number = item["DollarsL"];
            var DollarsW_value : Number = item["DollarsW"];
            var DollarsB_value : Number = item["DollarsB"];
            var DollarsO_value : Number = item["DollarsO"];
            var DollarsT_value : Number = item["DollarsT"];
            if( ! isNaN( DollarsL_value ))
            {DollarsL += DollarsL_value;}

            if( ! isNaN( DollarsW_value ))
            {DollarsW += DollarsW_value;}

            if( ! isNaN( DollarsB_value ))
            {DollarsB += DollarsB_value;}

            if( ! isNaN( DollarsO_value ))
            {DollarsO += DollarsO_value;}

            if( ! isNaN( DollarsT_value ))
            {DollarsT += DollarsT_value;}
            }
            label_DollarsL.text = DollarsL.toString();
            label_DollarsW.text = DollarsW.toString();
            label_DollarsB.text = DollarsB.toString();
            label_DollarsO.text = DollarsO.toString();
            label_DollarsT.text = DollarsT.toString();
            }

            [Bindable]
            public var myfilter: Array = [ {label:"DBA", data:"CAD105"}, {label:"Customer", data:"Cusnsd"}, {label:"Address", data:"CAD205"}, {label:"City", data:"CAD405"}, {label:"Zip", data:"PCD105"}, {label:"Rep", data:"SLMNSD"} ];

            [Bindable]
            public var selectedItem:Object;

            public function initVars() : void {
            myData = new ArrayCollection (myDataArray);
            myData.filterFunction=processFilter;
            calcMyData();
            // qAccInfo.AccInfo.send();
            }
            ]]>
            </mx:Script>

            <mx:RemoteObject id="qAccInfo" destination="ColdFusion" source="youngsmarket.programs.accinfo.accinfo.bin.accinfo" showBusyCursor="true" >
            <mx:method name="AccInfo" result="handleQueryResult(event)" fault="Alert.show(event.fault.message)"/>
            </mx:RemoteObject>

            <mx:Canvas x="0" y="0" width="100%" height="227">
            <mx:TextInput width="75" id="txtFilter" change="refreshData();" x="267"/>
            <mx:ComboBox id="columnfilter" dataProvider="{myfilter}" width="125" close="selectedItem=ComboBox(event.target).selectedItem" change="myData.refresh()" x="51"/>


            <mx:DataGrid id="myDataGrid" dataProvider="{myData}" width="100%" height="100%" x="10" y="30">
            <mx:columns>
            <mx:DataGridColumn dataField="Cusnsd" headerText="Cust" minWidth="40" />
            <mx:DataGridColumn dataField="CAD105" headerText="DBA" minWidth="120" />
            <mx:DataGridColumn dataField="CAD205" headerText="Address" minWidth="140"/>
            <mx:DataGridColumn dataField="CAD405" headerText="City" minWidth="75"/>
            <mx:DataGridColumn dataField="PCD105" headerText="Zip" width="50"/>
            <mx:DataGridColumn dataField="PHON05" headerText="Phone" minWidth="60"/>
            <mx:DataGridColumn dataField="SLMNSD" headerText="Rep" width="50"/>
            <mx:DataGridColumn dataField="DollarsL" headerText="Liquor" minWidth="50" textAlign="right"/>
            <mx:DataGridColumn dataField="DollarsW" headerText="Wine" minWidth="50" textAlign="right"/>
            <mx:DataGridColumn dataField="DollarsB" headerText="Beer" minWidth="50" textAlign="right"/>
            <mx:DataGridColumn dataField="DollarsO" headerText="Other" minWidth="50" textAlign="right"/>
            <mx:DataGridColumn dataField="DollarsT" headerText="Total" minWidth="50" textAlign="right"/>
            </mx:columns>
            </mx:DataGrid>
            </mx:Canvas>
            <mx:Label x="10" y="308" text="DollarsL" color="#ffffff"/>
            <mx:Label x="10" y="334" text="DollarsW" color="#ffffff"/>
            <mx:Label x="10" y="360" text="DollarsB" color="#ffffff"/>
            <mx:Label x="10" y="386" text="DollarsO" color="#ffffff"/>
            <mx:Label x="10" y="412" text="DollarsT" color="#ffffff"/>
            <mx:Label x="83" y="308" text="Label" id="label_DollarsL"/>
            <mx:Label x="83" y="334" text="Label" id="label_DollarsW"/>
            <mx:Label x="83" y="360" text="Label" id="label_DollarsB"/>
            <mx:Label x="83" y="386" text="Label" id="label_DollarsO"/>
            <mx:Label x="83" y="412" text="Label" id="label_DollarsT"/>
            </mx:Application>
            Allen
            • 3. Re: datagrid totals
              GeorgeWS Level 1
              Allen,
              Your the KING. This is just what I needed. I do have one strange problem. I added another set: just like the DollarsW, DollarsL etc. except CasesW, CasesL etc. I added the sets just like the Dollars that I have working perfectly. But when I run the app I get an error...
              Cannot access a property or method of a null object reference. Not sure what that means, the data fields are the same type and length on the SQL server. There are no NULL records. I have been messing with it all day.
              My grid id like this:
              <mx:Accordion id="accordion" width="100%" height="100%" headerHeight="5">
              <mx:VBox height="100%" >

              <mx:DataGrid id="myDataGrid" dataProvider="{myData}" width="100%" height="100%">
              <mx:columns>
              <mx:DataGridColumn dataField="Cusnsd" headerText="Cust" minWidth="40" />
              <mx:DataGridColumn dataField="CAD105" headerText="DBA" minWidth="120" />
              <mx:DataGridColumn dataField="CAD205" headerText="Address" minWidth="140"/>
              <mx:DataGridColumn dataField="CAD405" headerText="City" minWidth="75"/>
              <mx:DataGridColumn dataField="PCD105" headerText="Zip" width="50"/>
              <mx:DataGridColumn dataField="PHON05" headerText="Phone" minWidth="60"/>
              <mx:DataGridColumn dataField="SLMNSD" headerText="Rep" width="50"/>
              <mx:DataGridColumn dataField="SLM4SD" headerText="Key" width="50"/>
              <mx:DataGridColumn dataField="SLM5SD" headerText="Estate" width="50"/>
              <mx:DataGridColumn dataField="DollarsL" headerText="Liquor" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="DollarsW" headerText="Wine" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="DollarsB" headerText="Beer" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="DollarsO" headerText="Other" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="DollarsT" headerText="Total" minWidth="50" textAlign="right"/>
              </mx:columns>
              </mx:DataGrid>
              <mx:HBox width="100%">
              <mx:Label text="Totals" width="100%"/>
              <mx:Label text="Label" id="label_DollarsL" minWidth="50" textAlign="right"/>
              <mx:Label text="Label" id="label_DollarsW" minWidth="50" textAlign="right"/>
              <mx:Label text="Label" id="label_DollarsB" minWidth="50" textAlign="right"/>
              <mx:Label text="Label" id="label_DollarsO" minWidth="50" textAlign="right"/>
              <mx:Label text="Label" id="label_DollarsT" minWidth="50" textAlign="right"/>
              <mx:Label text="" width="6"/>
              </mx:HBox>
              </mx:VBox>
              <mx:VBox height="100%" >
              <mx:DataGrid id="myDataGrid2" dataProvider="{myData}" width="100%" height="100%">
              <mx:columns>
              <mx:DataGridColumn dataField="Cusnsd" headerText="Cust" minWidth="40" />
              <mx:DataGridColumn dataField="CAD105" headerText="DBA" minWidth="120" />
              <mx:DataGridColumn dataField="CAD205" headerText="Address" minWidth="140"/>
              <mx:DataGridColumn dataField="CAD405" headerText="City" minWidth="75"/>
              <mx:DataGridColumn dataField="PCD105" headerText="Zip" width="50"/>
              <mx:DataGridColumn dataField="PHON05" headerText="Phone" minWidth="60"/>
              <mx:DataGridColumn dataField="SLMNSD" headerText="Rep" width="50"/>
              <mx:DataGridColumn dataField="SLM4SD" headerText="Key" width="50"/>
              <mx:DataGridColumn dataField="SLM5SD" headerText="Estate" width="50"/>
              <mx:DataGridColumn dataField="CasesL" headerText="Liquor" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="CasesW" headerText="Wine" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="CasesB" headerText="Beer" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="CasesO" headerText="Other" minWidth="50" textAlign="right"/>
              <mx:DataGridColumn dataField="CasesT" headerText="Total" minWidth="50" textAlign="right"/>
              </mx:columns>
              </mx:DataGrid>
              <mx:HBox width="100%">
              <mx:Label text="Totals" width="100%"/>
              <mx:Label text="Label" id="label_CasesL" minWidth="52" textAlign="right"/>
              <mx:Label text="Label" id="label_CasesW" minWidth="52" textAlign="right"/>
              <mx:Label text="Label" id="label_CasesB" minWidth="52" textAlign="right"/>
              <mx:Label text="Label" id="label_CasesO" minWidth="52" textAlign="right"/>
              <mx:Label text="Label" id="label_CasesT" minWidth="52" textAlign="right"/>
              <mx:Label text="" width="8"/>
              </mx:HBox>
              </mx:VBox>
              </mx:Accordion>

              AND THE CODE YOU PROVIDED IS LIKE THIS:

              public function calcMyData():void
              {
              var DollarsL: Number = 0;
              var DollarsW: Number = 0;
              var DollarsB: Number = 0;
              var DollarsO: Number = 0;
              var DollarsT: Number = 0;
              var CasesL: Number = 0;
              var CasesW: Number = 0;
              var CasesB: Number = 0;
              var CasesO: Number = 0;
              var CasesT: Number = 0;
              var length : int = myData.length;
              for( var i : int = 0; i < length; i++ )
              {
              var item : Object = myData.getItemAt( i );
              var DollarsL_value : Number = item["DollarsL"];
              var DollarsW_value : Number = item["DollarsW"];
              var DollarsB_value : Number = item["DollarsB"];
              var DollarsO_value : Number = item["DollarsO"];
              var DollarsT_value : Number = item["DollarsT"];
              var CasesL_value : Number = item["CasesL"];
              var CasesW_value : Number = item["CasesW"];
              var CasesB_value : Number = item["CasesB"];
              var CasesO_value : Number = item["CasesO"];
              var CasesT_value : Number = item["CasesT"];
              if( ! isNaN( DollarsL_value ))
              {DollarsL += DollarsL_value;}

              if( ! isNaN( DollarsW_value ))
              {DollarsW += DollarsW_value;}

              if( ! isNaN( DollarsB_value ))
              {DollarsB += DollarsB_value;}

              if( ! isNaN( DollarsO_value ))
              {DollarsO += DollarsO_value;}

              if( ! isNaN( DollarsT_value ))
              {DollarsT += DollarsT_value;}

              if( ! isNaN( CasesL_value ))
              {CasesL += CasesL_value;}

              if( ! isNaN( CasesW_value ))
              {CasesW += CasesW_value;}

              if( ! isNaN( CasesB_value ))
              {CasesB += CasesB_value;}

              if( ! isNaN( CasesO_value ))
              {CasesO += CasesO_value;}

              if( ! isNaN( CasesT_value ))
              {CasesT += CasesT_value;}
              }
              label_DollarsL.text = DollarsL.toString();
              label_DollarsW.text = DollarsW.toString();
              label_DollarsB.text = DollarsB.toString();
              label_DollarsO.text = DollarsO.toString();
              label_DollarsT.text = DollarsT.toString();
              label_CasesL.text = CasesL.toString();
              label_CasesW.text = CasesW.toString();
              label_CasesB.text = CasesB.toString();
              label_CasesO.text = CasesO.toString();
              label_CasesT.text = CasesT.toString();
              }

              But like I said if I take out all the "Cases" parts the dollars part works perfect.

              Thanks for the help

              George