Search This Blog

Friday, September 11, 2015

How to implement "Totals" with Data View control


Earlier I posted a question about how to implement totals with Data View control.
Thank's everyone for help.
Eventually I managed to do it.

So, let me show you how I did it

1) Let's imagine we have a "Test" form with three fields "a", "b" and "c".

2) Then we create a simple Notes View with three categorized columns, which categorized columns for "a", "b" and "c" correspondingly. Important condition: each nested category column should contain a value of a parent category via some specific separator. I chose "###" as a separator.
You can use also this trick to make it working faster.
So, eventually we get something like this:
(just for clarification: the first document in the view has fields [a]="aaa", [b]="bbb" and [c]="ccc")

The first column with totals serves just for making the demo easier - I do not use it with Xpages so you may not create it.

3) Now let's create XPage with Data View, define data source and define three categorized columns there. Here is a code of categorized column "value" property:

getCategoryValueAndCount(this, viewEntry, 3)

Parameters description:
this - data source
viewEntry - variable name of a data row 
3 - column 's position in the Notes view

4) Here is SSJS function that does my magic:

function getCategoryValueAndCount(dataView, viewEntry, colIndex) {
try {

var = dataView;
var = vc.getData();
var viewName =  vcds.getViewName();
var ve:NotesViewEntry = viewEntry;
var veColumns = ve.getColumnValues();
var categoryValue = veColumns.elementAt(colIndex);
var categoryValueToDisplay = @RightBack("###" + categoryValue, "###");
var db:NotesDatabase = null;
if (vcds.getDatabaseName()) {
db = session.getDatabase(@Left(vcds.getDatabaseName(), "!!"), @Right(vcds.getDatabaseName(), "!!"))
else {
db = database;
var view:NotesView = db.getView(viewName);
var allCategories = categoryValue.split("###");
var allCategoriesKey = "";
for (i = 0; i < allCategories.length; i++) {
if (i == 0) {
allCategoriesKey = allCategories[i];
} else {
allCategoriesKey = allCategoriesKey + "\\" + @Subset(allCategories, i + 1).join("###");
var nav:NotesViewNavigator = view.createViewNavFromCategory(allCategoriesKey);

//the block below can be replaced with counter = nav.getCount() if your view has only one categorized column
var counter = 0;
var nve = nav.getFirst();
while (nve) {
if (nve.isDocument()) {
counter = counter + 1;
nve = nav.getNext(nve);

return categoryValueToDisplay + " (" + counter.toString() + ")" 

} catch(e) { 
             //error handler

Here is what I see after all.

The nice thing about such solution is that the totals are real, they take into account READERS fields, so the user sees a number of documents which he/she really has access to.


Some good guy suggested me to try CategoryRow facet from DataView control to implement the totals. Unfortunately it didn't work for me: facet keys "categoryRow1", "categoryRow2" and so on didn't work for me, only "categoryRow" worked - however I could do something wrong so may be it is still an alternative option. Thank you everyone for help anyways.

For couple of years later I have got rid of this solution because it was bad from performance standpoint.

No comments:

Post a Comment