One of the trickiest things a report writer has to deal with is using a data field that may, or may not, contain data. Chon Neth (@MaximoTimes) has a great tip on how to manage NULL data fields. But what happens when you want to group on a field that may have NULL data?

For example, a common report task is grouping work orders on those that were generated by a PM and those that weren't. So the report would have the data field WORKORDER.PMNUM and you want to group on that field. If you create a group of just:

row["pmnum"]

The report would have a single group of work orders that have a NULL value for WORKORDER.PMNUM and then each PM number would be listed in its own group. But we what we wanted was essential two groups - work orders with a PM number and work orders without. So how do you get a group to just show work order that do or do not have a PM number?

One of the easy ways is to group on a field that may contain NULL data is to use a javascript conditional check.¹ Using the following Group Details will collect work orders that either do or do not have a PM number.

The Group On function will be:

(row["pmnum"] == null) == true;

Now when the report is generated, the report will group the records by any work order that doesn't have a PM number and all records that do.

Bonus Tip: Now because the group is based on a NULL conditional check, having a text header to describe the group should also use a conditional check. Create a new Data element and place it in the group header from above.

Click the Expression Builder button and enter the following:

The Expression is written as:

if (dataSetRow["pmnum"] == null) 
    { 'Corrective Work' }
else { 'Preventative Work' };

If the data row is null, the Data element will print 'Corrective Work', the else clause picks up any data row that does have a PM number and therefore will print 'Preventative Work'.

Got any questions? Feel free to hit me up on Twitter at [@MyGeekDaddy][mgdtwitter].