How to create column totals in XPages view and repeat controls

In a Notes view it is fairly easy to add a column total, but how is this done in an XPages view control, or a repeat control ? It took me a trip to Lotusphere 2011 to find out.

Example:

We need the totals to: 

1. stay right-aligned under the column that is being totalled, even when the table is being resized.
2. to show the total of the values from the documents that are being displayed on the page

1. For this example we need to add a panel to the <xp:this.facets> tag of the view or repeat control:

<xp:viewPanel id="viewPanel1">

     <xp:this.facets>

          <xp:panel xp:key="footer" id="totals1">

               <xp:tr>

                   <xp:td colspan="7"></xp:td>

                   <xp:td>

                        <xp:text escape="true" id="computedField1">

                    </xp:td>

                   <xp:td colspan="2"></xp:td>

                   <xp:td>

                        <xp:text escape="true" id="computedField2">

                    </xp:td>

                   <xp:td>

                        <xp:text escape="true" id="computedField3">

                    </xp:td>

                 </xp:tr>

             </xp:panel>

       </xp:this.facets>

The trick is in the xp:key property of the panel. Setting this to "footer", as is done for a pager control that you wish to have appear in the footer, allows you to add the extra row. Into this last row of the table that forms the view or repeat control, we place the computed fields. (Note that this example assumes that there are 7 columns before the column titled 'Value')

2. Lets assume that we provide a combo box on the XPage to allow the user to filter the documents that will display in the view or repeat control.

With Firebug, we can see that XPages creates a dynamic id and name for each component. For the combo box it is view:_id1:_id2:_id94:comboBox1.

But notice that all components on a custom control are allocated the same prefix of 'view:_id1:_id2:_id94:' to the name we gave the component:

So we can add the following client-side Javascript code to the 'On Change' event of our combo box:

   var fcast = 0;

   var i = 0;

   var id_prefix = "#{id:comboBox1}".split("comboBox1")[0];

   while (dojo.byId(id_prefix+"repeat1:"+i+":inputText3")) {

       var it3= dojo.byId(id_prefix+"repeat1:"+i+":inputText3").innerHTML;

       it3 = it3.replace("$","").replace(/,/gi,"");

       fcast = fcast + parseFloat(it3);

       i++;

   }

   var tot = formatCurrency(fcast);

   dojo.byId(id_prefix+"computedField1").innerHTML = tot;

The third line uses "#{id:comboBox1}" to return the full id allocated to the comboBox1 field at run time. Please note that this code does not work if placed in a script library.

The while loop starts from the 0 row (first) and loops for every row on the view/repeat control where the inputText3 field exists.

Firebug shows that the values are rendered as span tags, so we need to use .innerHTML to get and set values.

The $ and commas need to be removed from the strings using the replace method, so the totals can be accumulated.

Finally, the formatCurrency function rounds to two decimal places and reformats the total with the $ and the commas. (This is a custom function, not shown here.)

Similar code can be created for computedField2 & computedField3.

So, if your customers need a report that shows column totals, or even need a report with filters that show totals, you can make use of these techniques.

Shout out to Paul Hannan and Marie Kehoe for their being the catalyst for this solution.