Calculated columns (cell formula)

Using the below link I am using this method in my grid which adds up a Nett & Disc row into a Total. This works great going accros the grid and I now have lots of flexiablty with calcuations across the grid.

http://www.activewidgets.com/javascript.forum.20911.6/calculated-columns-cell-formula.html

I now need to be able to show column totals.

I have really tried and have managed to get the column total into function and then display the result of the function in a varailable which I can then display in a totals block.

However this total dosen't refresh when the grid changes.

Please help!!

function sum01(col, row){ 

    var cell1 = this.getCellValue(3, 0); 

    var cell2 = this.getCellValue(3, 1); 

    var cell3 = this.getCellValue(3, 2); 

    var cell4 = this.getCellValue(3, 3); 

    var cell5 = this.getCellValue(3, 4); 

    var cell6 = this.getCellValue(3, 5); 

    

    return cell1 + cell2 + cell3 + cell4 + cell5 + cell6;

}



obj.setCellData(sum01,5, 6); 



var nett = obj.getCellValue(5, 6);

Are you using row footers for this? http://www.activewidgets.com/aw.ui.grid/footer-template.html

No I am using a separate HTML fieldset to show the totals..

Here's Most of the page/code.. Hopefully it's all there and make sense?

Ideas here would be good? Maybe if I could use calculated columns method, but I can not work out how to use that going down the grid,
which is why I have created a function to calculate the total. But I need this total to refresh when the grid changes.

<Html>

<Body>

<fieldset id="SLI_Inv_Details">

 <legend>Invoicing Details</legend>

  <script type="text/javascript">

   var myHeaders = ['Line Description', 'Nett', 'Disc', 'Total', 'Centre']; 

   var myCells = [ 

        ["Line One Sales" ,300.00,0.00],  

        ["Line Two Sales",500.00,0.00],  

        ["Line Three Sales",10750.00,0.00],  

        ["Line Four Sales",250.00,0.00],  

        ["Line Five Sales",350.00,0.00],  

    ] 



   var str = new AW.Formats.String; 

   var num = new AW.Formats.Number; 

   var disc = new AW.Formats.Number; 

   num.setTextFormat('####.##'); 

   disc.setTextFormat('#.##'); 



   var obj = new AW.UI.Grid; 

   obj.setHeaderText(myHeaders); 

   obj.setCellData(myCells); 

   obj.setCellFormat([str, num, disc, num, str, num]); 

      



   //set number of rows/columns

   obj.setColumnCount(5);  

   obj.setRowCount(5);  

   obj.setSize(760, 150);

   obj.setColumnWidth(250, 0); 

   obj.setColumnWidth(100, 1);

   obj.setColumnWidth(100, 2);		

   obj.setColumnWidth(100, 3);		  

   obj.setColumnWidth(200, 4);		  



  obj.setCellEditable(true); 

  obj.setCellEditable(false, 4);

  obj.setCellFormula('(((column(1)/100)*column(2))-column(1))*-1',    3); // calc total in column-3 after disc



    function sum01(col, row){ 

    var cell1 = this.getCellValue(3, 0); 

    var cell2 = this.getCellValue(3, 1); 

    var cell3 = this.getCellValue(3, 2); 

    var cell4 = this.getCellValue(3, 3); 

    var cell5 = this.getCellValue(3, 4); 

    var cell6 = this.getCellValue(3, 5); 

    

    return cell1 + cell2 + cell3 + cell4 + cell5 + cell6;

    }



   obj.setCellData(sum01,5, 6);

   var net = obj.getCellValue(5, 6);



   document.write(obj); 

</script>

                        



<fieldset id="SLI_T_Details">

 <legend>Totals</legend>

   <table>

     <tr><td>Nett:</td><td>

      <script type="text/javascript">

        var num = new AW.Formats.Number; 

        num.setTextFormat('####.##'); 

        var nett = new AW.UI.Input;

        nett.setControlFormat(num);	

        nett.setControlText(nett);

        nett.refresh();

        document.write(nett);

      </script></tr>

     <tr><td>Disc:</td><td><input maxlength="10" name='Disc' type='text' size='10' value='0.00'></tr>

     <tr><td>VAT:</td><td><input maxlength="10" name='VAT' type='text' size='10' value='0.00'></tr>

     <tr><td>Total:</td><td><input maxlength="10" name='Total' type='text' size='10' value='0.00' align="right"></tr>

   </table>

</fieldset>

</body>		

</HTML>

<Html> 

<Body> 

<fieldset id="SLI_Inv_Details"> 

 <legend>Invoicing Details</legend> 

  <script type="text/javascript"> 

   var myHeaders = ['Line Description', 'Nett', 'Disc', 'Total', 'Centre'];  

   var myCells = [  

        ["Line One Sales" ,300.00,0.00,"","",""],   

        ["Line Two Sales",500.00,0.00,"","",""],   

        ["Line Three Sales",10750.00,0.00,"","",""],   

        ["Line Four Sales",250.00,0.00,"","",""],   

        ["Line Five Sales",350.00,0.00,"","",""]   

    ]  



   var str = new AW.Formats.String;  

   var num = new AW.Formats.Number;  

   var disc = new AW.Formats.Number;  

   num.setTextFormat('####.##');  

   disc.setTextFormat('#.##');  



   var obj = new AW.UI.Grid;  

   obj.setHeaderText(myHeaders);  

   obj.setCellData(myCells);  

   obj.setCellFormat([str, num, disc, num, str]);  

       



   //set number of rows/columns 

   obj.setColumnCount(5);   

   obj.setRowCount(6);   

   obj.setSize(760, 150); 

   obj.setColumnWidth(250, 0);  

   obj.setColumnWidth(100, 1); 

   obj.setColumnWidth(100, 2);         

   obj.setColumnWidth(100, 3);           

   obj.setColumnWidth(200, 4);           



  obj.setCellEditable(true);  

  obj.setCellEditable(false, 4); 

 obj.setCellFormula('(((column(1)/100)*column(2))-column(1))*-1', 3); // calc total in column-3 after disc 



    function sum01(col, row){  

    var cell1 = this.getCellValue(3, 0);  

    var cell2 = this.getCellValue(3, 1);  

    var cell3 = this.getCellValue(3, 2);  

    var cell4 = this.getCellValue(3, 3);  

    var cell5 = this.getCellValue(3, 4);  

 //   var cell6 = this.getCellValue(3, 5);  

 //   return cell1 + cell2 + cell3 + cell4 + cell5 + cell6; 



       return cell1 + cell2 + cell3 + cell4 + cell5 ; 

    } 



   obj.setCellData(sum01,3, 5); 

   var net = obj.getCellValue(3, 5); 



 obj.onCellValueChanged = function(value, col, row){ 

 if(col==1 || col==2){

    obj.setCellData(sum01,3, 5); 

   var net2 = obj.getCellValue(3, 5); 

   nett.setControlText(net2); 

   nett.refresh(); 

   }

 }

 

   document.write(obj);  

</script> 

                         



<fieldset id="SLI_T_Details"> 

 <legend>Totals</legend> 

   <table> 

     <tr><td>Nett:</td><td> 

      <script type="text/javascript"> 

        var num = new AW.Formats.Number;  

        num.setTextFormat('####.##');  

        var nett = new AW.UI.Input; 

        nett.setControlFormat(num);     

        nett.setControlText(net); 

        nett.refresh(); 

        document.write(nett); 

      </script></tr> 

     <tr><td>Disc:</td><td><input maxlength="10" name='Disc' type='text' size='10' value='0.00'></tr> 

     <tr><td>VAT:</td><td><input maxlength="10" name='VAT' type='text' size='10' value='0.00'></tr> 

     <tr><td>Total:</td><td><input maxlength="10" name='Total' type='text' size='10' value='0.00' align="right"></tr> 

   </table> 

</fieldset> 

</body>         

</html>

Forum search