xpages: how to get the total of the categorized column

Question

I have a categorized view, the first column is categorized and ascending.The last column calculates the total. Imagine the view name is called view1 and it looks like the following:

1st column(categorized)  | 2nd column   | 3rd column (total)
Australia                |              |   2
                         |  Alex        |   1
                         |  Bill        |   1
Brazil                   |              |   3
                         |  Alan        |   1
                         |  Alice       |   1
                         |  Tom         |   1
Chile                    |              |   1
                         |  John        |   1
Denmark                  |              |   2
                         |  Susan       |   1
                         |  Tim         |   1
                         |              |   8

I would like to retrieve the total of each categorized column, for example, the total for Australia is 2, the total for Brazil is 3. I browse on the internet and write some code but the result is not success.

Here is my first attempt: I intend to use @DbLookup, for loop and @Count.

var value = sessionScope.Value;
var lookupValue = (@DbLookup(@DbName(),"view1",value,1));

for(var a = 0; a < lookupValue.length; a++)
{
  //try to display the lookupValue and the relevant total
   writer.write("<tr><td>"+lookupValue[a]+"<td> "+ @Count(lookupValue[a]) + "</td></tr>");  
}

When I run the code, I get the following result:

Australia 1
Australia 1
Brazil    1
Brazil    1
Brazil    1
Chile     1
Denmark   1
Denmark   1

After the first attempt, I start the second attempt: (please note, most of the code in this part I get from the internet and I just modify a little bit to suit my case)

var myView:NotesView = database.getView('view1');
var vec:NotesViewEntryCollection = myView.getAllEntries();
var viewEnt:NotesViewEntry = vec.getFirstEntry();

writer.write("<table>");
while (viewEnt != null)
{
   // try to retrieve the total in the 3rd column
    writer.write("<tr><td> "+ 'viewEnt.getColumnValues()[2]' +"</td></tr>");

    var tmp = vec.getNextEntry(viewEnt);
    viewEnt.recycle();
    viewEnt = tmp;
}
writer.write("</table>");
writer.endDocument();            

I run the code and the result is empty. I do not get any total of the categorized column.

I review the code from attempt 1 and attempt 2, I don't understand why I cannot get the total.

I search on the internet again and I found this similar post: Getting a summary count in a view export?

I try to apply the solution in the application:(please note, most of the code in this part I get from that post and I just add the view name in the function)

function getCategoryData(viewName, dataColumn, getChildren) {
var v = database.getView(view1); // change viewName to view1
var nav = v.createViewNav();
var ve = nav.getFirst();
var isFirst = true;
var result = "[";
    while (ve) {
if (!ve.isTotal()) {
    var curData = ve.getColumnValues();
    if (!isFirst) {
        result += ",";
    }
    result += "{label : \"";
    result += curData[0];
    result += "\", value : ";
    result += curData[dataColumn];
    /* for 2 level categories we fetch additional data */
    if (getChildren) {
        var childve = nav.getChild();
        var firstChild = true;
        result += ", children : [";
        while (childve) {
            var childData = childve.getColumnValues();
            if (!firstChild) {
                result += ",";
            }
            result += "{label : \"";
            result += childData[1];
            result += "\", value : ";
            result += childData[dataColumn];
            result += "}";          
            firstChild = false;
            childve = nav.getNextSibling(childve);
        }
        result += "]"
    }
    result += "}";          
    isFirst = false;
}       
ve = nav.getNextSibling(ve);
}
result += "]";
return result;
 }

I run the code and I get the result like this:

[{label : "Australia", value : undefined},{label : "Brazil", value : undefined},{label : "Chile", value : undefined},{label : "Denmark", value : undefined}]

After the third attempt, I think the reason that I cannot get the total is I misunderstand the concept about how to get the total in the view.

In fact, what I would like to do is get the total of each categorized column in view1. Therefore, imagine the result will look like this:

Australia 2
Brazil    3
Chile     1
Denmark   2

Actually, I think the first attempt is very close to the result. I feel I missed something in the code (perhaps I guess in the for loop part).

How can I get the total of each categorized column in view?

Grateful for your advice on this issue please. Thank you very much.

Answer 1

You are on the right track with your third attempt. You definitely want to use NotesViewNavigator.

Here is a working example based on your requirements:

/*
 * @param viewName      Name of the view to navigate
 * @param labelColumn   Number of the column in the view containing the label
 *                      Note: column count start at 0
 * @param dataColumn    Number of the column in the view containing
 *                      totals. Note: column count starts at 0
 * @return              String representing a javascript array of objects containing label and value
 */
function getCategoryData(viewName, labelColumn, dataColumn) {
    var v:NotesView = database.getView(viewName);
    var nav:NotesViewNavigator = v.createViewNav();

    var ve:NotesViewEntry = nav.getFirst(); // first category
    var isFirst = true;
    var result = "[";

    while (ve) {

        var curData = ve.getColumnValues();

        if (curData[labelColumn] != "") { // skip the total at the bottom of the totals column
            if (!isFirst) {
                result += ",";
            }

            result += "{label : \"";
            result += curData[labelColumn];
            result += "\", value : ";
            result += curData[dataColumn];
            result += "}";   
        }

        isFirst = false;    
        var tmpentry:NotesViewEntry = nav.getNextCategory();
        ve.recycle();
        ve = tmpentry;
    }

    result += "]";

    nav.recycle();
    v.recycle();

    return result;
}

Based on the description of your view and assuming the name of your view is "countries" you would call this function like this:

getCategoryData("countries", 0, 2);

Note: I did not test this function with multiple categorized columns.