Thursday, 9 June 2011

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.

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:panel xp:key="footer" id="totals1">
                   <xp:td colspan="7"></xp:td>
                        <xp:text escape="true" id="computedField1">
                   <xp:td colspan="2"></xp:td>
                        <xp:text escape="true" id="computedField2">
                        <xp:text escape="true" id="computedField3">
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);
   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.


  1. Trying to create a view in XPages with main response documents, it has categories and sub-categories as well; plus, I skip the first column because it's filtered by category name. In Notes, the view looks good, the category rows contain totals, etc. In XPages/Web I don't see sub-totals or totals. I don't even know if it's possible. Do you?

    Regards, Sjef

  2. Sorry, I got my assumptions wrong. Of course it doesn't work in Notes, it is a hierarchical view so I can't have sub-totals...

    My bad.


  3. Peter,
    This was a good find for me today as i have a requirement to do something like this....but ?

    The web report I would like to create with the XPage would be like this but include more subtotals and a final total line at the bottom.

    So it would kinda look like this.

    detail columns
    detail columns
    detail columns
    subtotal columns

    detail columns
    detail columns
    detail columns
    subtotal columns

    detail columns
    detail columns
    detail columns
    detail columns
    subtotal columns
    final total columns

    Using the "footer" as you describe in your article, is it possible to use multiple footers in that way to simulate the multiple subtotals and totals lines I need ?
    just wondering ?