EXTJS in XPages #12 – Counting categories with Grouped columns

In this article I will demonstrate how grouping can be added to the EXTJS grid within your XPage and how the number of rows within that group can be totaled and displayed to the user.

EXTJS in XPages series

Here are links to all of the previous articles in this series

Demonstration

The EXTJS in XPages demonstration database can be found at http://demo.xomino.com/xomino/Extjs.nsf/xGridGrouped.xsp

Download

The sample database that will accompany this series is  available from the menu in the live demo database from this link – http://demo.xomino.com/xomino/Extjs.nsf

Introduction

“Categorization” is something we are used to in the world of Domino Views and we can also create a pseudo total of the categorized columns by totaling an adjacent column. This is functional but is hardly aesthetically pleasing.

Grouping is easily achieved with an EXTJS grid and as we will see it is also dynamic and will re-total on filtering as well

Using the ‘Ext.grid.feature.Grouping’ feature we are easily able to create and manipulate a grid which looks like this (grouped by state)

tot1

How does it work?

We do not have to add much to our code to include the grouping feature. We first create a new variable inside of the createGrid() function representing the new feature.

var groupingFeature = Ext.create('Ext.grid.feature.Grouping',{
	ftype: 'groupingsummary',
	id: 'groupingFeature',
	groupHeaderTpl: [
		'{name:this.formatName} ({rows.length})', 
		{
		//This is used to display "None" if there is a blank category
		formatName: function(name) {
			var sName = (name == "") ? "None ": name;
			return sName;
		}
	}],
	startCollapsed: false
});

There are a few parameters to discuss in this code block but for the most part I think the code is self explanatory.

The groupHeaderTpl is the paramater to determine how the actual grouping
group line is displayed. In this case we are using the EXT templating technique.

  • {name: this.formatName} is the name of the column Header – it uses the formatName function to conditionally display the name of the column
  • {rows.length} is the number of rows within the group (the total)
  • formatName (referenced in the previous this.formatName) is a conditional function which determines if there is a value to even display as the group header. In this case if there are blank fields in the grouped column it will display “None” instead of just displaying nothing and a number.

After adding this new variable we have to add the feature to the grid in the features parameter:

var grid = Ext.create('Ext.grid.Panel', {
	renderTo: 'gridHere',
	frame: true,
	height: 400,
	title: 'Users',
	plugins: buffRend,
	features: [groupingFeature, filters],
	etc

Finally we need to tell the store which field to initially group using the groupField and groupDir parameters:

store = new Ext.data.Store({
	groupField: "state",
	groupDir: 'ASC',
	model : 'Person',
	autoLoad: true,
	sorters: {
				property : 'firstName',
				direction: 'ASC'
			},
	id: 'store',
	data: data,
	remoteFilter: false,
	buffered: false,
	proxy: {
		type: 'memory'
	}
});

That’s it – once again a big increase in functionality with a relatively simple incremental code addition to the grids we already had.

Some other neat things grouping does for us
When we have a grouped grid and we go to filter it the group header is dynamically re-calculated based on the filtered results

tot2

Adding the grouping feature to the grid allows us to group any of the columns by selecting the “group by this column” option which now appears in the column dropdown

tot3

tot4

As you can also see from the images above – the user has the option to remove grouping as well “Show in groups”

Conclusion
In this article I have only scratched the surface of the possibilities with the grouped grid. Good looking categories with counts are something we have strived for in Notes for a long time and I think this is a very neat solution.

6 thoughts on “EXTJS in XPages #12 – Counting categories with Grouped columns

  1. Another great one!
    I used this in a customer application but the caveat is that the totals are calculated only upon the visible amount of data – in my case. Is there a work around to calc the whole data though only a segment of it is displayed?

  2. Mark, I have a quick question if I may. I finally was able to get my application working with ExtJs Wooo. That was quite a feat for me. . . Anyhow, I have a date / time field issue in one of the columns. The view is putting together a date and time value that displays correctly in Notes but is converted when it comes over in JSON. Can you tell me how to resolve this? Here is what it looks like in Notes:

    In the Notes view it looks like this: 01/01/2014 10:00 AM

    Here is the JSON being generated:

    {
    “@entryid”:”1.1-EC8D20612F9428D686257C540037C308″,
    “@unid”:”EC8D20612F9428D686257C540037C308″,
    “@noteid”:”60F2″,
    “@position”:”1.1″,
    “@read”:true,
    “@siblings”:3,
    “@indent”:1,
    “@form”:”Update”,
    “propertyno”:”0005″,
    “statustime”:”2014-01-01T16:00:00Z”, <<<<===== The date / time in question
    "status":
    ["Percent Complete: 100%","Culdesac","Driveways","Entrance to Driveway","Path to Front Door","Mail Box Pads","Private Sidewalks"
    ],
    "statuscomments":"",
    "docUNID":"EC8D20612F9428D686257C540037C308"
    },

    Here is the code I am using to create the column in the grid:
    {
    text: 'Start Time',
    sortable: true,
    dataIndex: 'statustime',
    field: {
    xtype : 'datefield',
    format: 'mm/dd/yyyy H:i:s',
    },
    . . . . .
    }

    Any thoughts on this? I'd greatly appreciate any help you could provide!

    Thank you so much!

    • Steven – congrats🙂

      multiple options for multiple things – as you wrote this up on grouping I will answer that one first –

      in the grouper function I test to see if the value is a date and if so then I run a dojo or jquery date format function on the incoming text (also allows for localization!).

      this example uses the jqueryui datepicker but hopefully you get the idea

      formatName: function(name) {
      var sName = (name == “”) ? “None “: name;
      if(new Date(sName).getDate()){
      sName = $.datepicker.formatDate( $.datepicker.regional[dojoConfig.locale].dateFormat, new Date(sName) )
      }
      return sName;
      }

      the other option for display in the column is to format the incoming data field via a renderer. Using the same format function add a renderer to the column for the value.

      Sounds to me like a very useful blog post in the near future !

      If that doesn’t help, hit me up on skype marky dot roden or email me marky @ this blog address and I will see what I can do to help🙂

      • Hi Marky,

        I just figured out how to use the renderer for the date and wooo it’s
        working. This grid is so cool….

        I do have one last question if I may? I have a bunch of text in a field
        separated with commas. I would like to them to show on new lines ( like in
        a notes view with multiple values display on new line ). See code below
        for the Status column renderer. It breaks the grid completely when I try
        this.

        Note the Start Time renderer which made the date column display
        correctly!!!!!!

        columns: [
        {text: “Start Time”, width: 120, dataIndex: ‘statustime’,
        sortable: true, filterable: true, renderer: Ext.util.Format.dateRenderer(‘M
        d, Y’)},
        {text: ‘Status’, width: 250, dataIndex: ‘status’,
        sortable: true, filterable: true, wrap: true, renderer:
        function( value )
        {
        return value.replace(/ *, */g, ”); // THIS
        IS THE LINE THAT BREAKS THE DISPLAY OF THE GRID
        }
        },
        {text: ‘Comments’, width: 250, dataIndex:
        ‘statuscomments’, sortable: true, filterable: true}
        ],

        Thank you so much for taking the time to help me on this project. You
        really are awesome for giving back to the community so much and I can’t
        thank you enough. If you want help writing the blog post I’d be happy to
        help. Anything I can do to help you out just ask!

        FYI, I struggled to get this to work in the beginning as I am very new to
        all the javascript frameworks so I had to find all the little details that
        I suspect you are used to having in your framework. In fact the last item
        I found to get the grid to even display was a very hard to find property in
        the source view of the XspProperties file. I kept getting errors about
        $.ajax… not found. Drove me crazy for a day till I found these lines:
        xsp.ajax.renderwholetree=false
        xsp.library.depends=com.ibm.xsp.extlib.library
        xsp.persistence.mode=file
        xsp.resources.aggregate=false
        xsp.theme=LMSTheme.theme

        I think it was the xsp.ajax.renderwholetree line that made it work.

        ____________________________________________________________________________________

        STEVEN RIEGER CERTIFIED IBM DEVELOPER / ADMINISTRATOR

        DIRECTOR INFORMATION TECHNOLOGIES

        Lieberman Management Services, Corporate Office

        25 Northwest Point Blvd, Suite 330, Elk Grove Village, IL 60007

        Direct / Fax (847) 777.7094 Mobile (847) 219.3961

  3. OK, I figured out the renderer to get multiple values to show in the column. First I had to add the following to the custom control where the div is to display the grid, to get values to wrap in the column
    .x-grid-cell-inner {
    white-space: normal;
    }

    Then in the renderer I was being a dork and didn’t realize I had to force the value to be a string with toString(). Doh!
    function renderMultiValues( value )
    {
    return value.toString().replace( /,/g, ” );
    }

    Now my grid is perfect! Thank you again for all that you do Marky!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s