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);

Jez

April 4,

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

Anthony

April 4,

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>

Jez

April 5,

<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>

April 5,

Im assuming thats Anthony's reply? I away until Wed, So Ill try your example then, Thanks in advance!!

Jez

April 7,

No, that's not mine. There's someone here who posts without identifying him or herself.

Anthony

April 7,

Ok.. Well I try the example anyway, it looks good.

Jez

Jez

April 7,

This topic is archived.

Calculated columns (cell formula)

See also:

Search

New Data Grid

Version 2