Getting a summary count in a view export?

Question

I need to export a view to excel. I have already found some code somewhere and it is working great in xpages. Now the user wants to add a summary of totals. I will explain.

I have a document that contains a user name, project ID and Equipment ID. What I need to do is export all documents with a particular project ID, showing the user name and equpment id in the export but after the view is exported, show a summary total of the similar equipment IDs.

Something like this:

       User              Selected Equipment
       Jonh Smith        C1
       Salley Johnson    C2
       Fred Days         C1


       Summary
       C1   2
       C2   1

What I thought of doing is using a categorized view first on project ID then on Equipment ID with a sum column, do a getAllDocumentsByKey from a NotesViewEntryCollection to get all of the documents for the selected project then leverage the category line to get my needed totals. But when I do only get one document for the getAllDocumentsByKey. If I remove the Equipment column and only categorize on Project ID then I get all expected documents.

Another thought I had was to have an object that I could store in a sessionscope varible and just sort on project id once againn using getalldocumentsbykey. The object would have an equipment ID variable and a total. As I work though the view, I would update the total for the current equipment id. Then at the end of the export of the view jut export this sessionscope table. But since I am new to javascript, I really don't know where to get started on this. Is this the right direction? If so could someone get me started?

Any other ideas?

P.S. The user would rather not have to use the Subtoal features of Excel.

score 4 · Accepted Answer · answered Feb 22 '13 at 02:25

Bruce, make your live easier and use a ViewNavigator. The ViewNavigator has entries for the categories and when you set a value to be summed up that value is in the view navigator. It also has methods to jump from category to category, so you have less data to read. For example I use this function to get summary data from a categorized view (one or two levels):

function getCategoryData(viewName, dataColumn, getChildren) {
    var v = database.getView(viewName);
    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;
 }

Note: it returns a JSON String, not a JSON object. I needed the string, so you might want to alter that. You could easily amend the function to first select a category based subset:

function getCumulativeCategoryData(viewName, key, dataColumn, fetchChildren) {
var v = database.getView(viewName);

var nav = v.createViewNavFromCategory(key);
var ve = nav.getFirst();
    ...
 }

Where key would be a String or a Vector. You need to be a little bit careful with data types. If a category in the view is a number make sure you use Number() for your key otherwise it won't match.

Thanks! I forgot all about createViewNavFromCategory. It certianly made the job easier. What I ended up doing was looping trhorugh the results twice. Once for the detail and a second time for the totals. — Bruce Stemplewski, Feb 25 '13 at 15:45

score 0 · Answer 2 · edited May 23 '17 at 12:04

You are on the right track in your last paragraph but you don't need to store the values in a session scope. A standard javascript object will do. Assuming you are generating your report by looping through the documents in the view, the quickest and easiest solution in my mind would be to count each equipment id as you loop through the documents and store the counts in an object:

equipId[equipId1] equipId[equipId2] . . equipId[equipIdx]

Then after you have looped through all your docs, loop through your equipment ID object values as you have in your sample report above.

Looping through javascript objects: How do I loop through or enumerate a JavaScript object?

Sorting a javascript object: Sorting JavaScript Object by property value

Getting a summary count in a view export?

2 Answers2

Linked