BIRT Expression to Filter Data Based on Consecutive Instances

[AbdulQadeer-55]

As a beginner, I'm working on a Eclipse BIRT 4.5.0 version with JDK8 report and need help refining a data filtering expression.

Problem:

I have an existing BIRT template (.rptdesign) and sample XML data.
I've removed the first two columns from the report.
The goal is to display only rows where a specific value occurs 7 or more consecutive times.

Attempts:

I've created a BIRT expression but it's not producing the desired results.

Computed Column Name is maxConsecutiveDays, data type is Integer

var consecutiveDays = 0;
var maxConsecutiveDays = 0;
var lastDate = null;

for (var i = 0; i < dataSetRow["CalendarDate"].length; i++) {
    var currentDate = new Date(dataSetRow["CalendarDate"][i]);
    
    if (lastDate) {
        var diffDays = (currentDate - lastDate) / (1000 * 60 * 60 * 24);
        
        if (diffDays === 1) {
            consecutiveDays++;
        } else {
            if (consecutiveDays > maxConsecutiveDays) {
                maxConsecutiveDays = consecutiveDays;
            }
            consecutiveDays = 1;
        }
    } else {
        consecutiveDays = 1;
    }
    
    lastDate = currentDate;
}

if (consecutiveDays > maxConsecutiveDays) {
    maxConsecutiveDays = consecutiveDays;
}

maxConsecutiveDays;

Note: if there is any other way to filter please do mention with explanation it would be a great help for me to solve this issue.

[hvbtup]

First of all, you should show your data set definitions. Otherwise we can only guess.
Optimally, you create a self-contained example report design that demonstrates your effort.

[AbdulQadeer-55]

Sure, I will add the dataset definitions, which are in XML format. If you have time, we can arrange a short meeting on Google Meet. I believe that would make it easier for me to explain all the details of the datasets and other related files.

[AbdulQadeer-55]

This is the javascript behind the report:

var ROSTER_RECORD_TYPE = "ROSTER_RECORD_TYPE";

var FIELD_ROSTER_SHIFT = "shift";
var FIELD_ROSTER_PERSON = "person";
var FIELD_ROSTER_WORKCODE = "workCode";
var FIELD_ROSTER_WORKCODE_TYPE = "workCodeType";
var FIELD_ROSTER_CALENDARDATE = "calendarDate";
var FIELD_ROSTER_INSTITUTION = "institution";
var FIELD_ROSTER_AGENCY = "agency";
var FIELD_ROSTER_REGION = "region";
var FIELD_ROSTER_STATION = "station";
var FIELD_ROSTER_UNIT = "unit";
var FIELD_ROSTER_POSITION = "position";
var FIELD_ROSTER_RANK = "rank";

var ROW_ROSTER_SHIFT = "shift:shiftID";
var ROW_ROSTER_PERSON = "person:personID";
var ROW_ROSTER_WORKCODE = "workCode:abrv";
var ROW_ROSTER_WORKCODE_TYPE = "workCodeType:ID";
var ROW_ROSTER_CALENDARDATE = "rr:rosterDate";
var ROW_ROSTER_INSTITUTION = "institution:ID";
var ROW_ROSTER_AGENCY = "agency:ID";
var ROW_ROSTER_REGION = "region:ID";
var ROW_ROSTER_STATION = "station:ID";
var ROW_ROSTER_UNIT = "unit:ID";
var ROW_ROSTER_POSITION = "position:ID";
var ROW_ROSTER_RANK = "jobTitle:jobTitleID";

function mapRosterRecordToRow(row, rosterRecord) {
	row[ROW_ROSTER_SHIFT] = rosterRecord.shift;
	row[ROW_ROSTER_PERSON] = rosterRecord.person;
	row[ROW_ROSTER_WORKCODE] = rosterRecord.workCode;
	row[ROW_ROSTER_WORKCODE_TYPE] = rosterRecord.workCodeType;
	row[ROW_ROSTER_CALENDARDATE] = rosterRecord.calendarDate;
	row[ROW_ROSTER_INSTITUTION] = rosterRecord.institution;
	row[ROW_ROSTER_AGENCY] = rosterRecord.agency;
	row[ROW_ROSTER_REGION] = rosterRecord.region;
	row[ROW_ROSTER_STATION] = rosterRecord.station;
	row[ROW_ROSTER_UNIT] = rosterRecord.unit;
	row[ROW_ROSTER_POSITION] = rosterRecord.position;
	row[ROW_ROSTER_RANK] = rosterRecord.rank;
}

function mapRowToRosterRecord(row) {
	var rosterRecord = {
		workCode:row[ROW_ROSTER_WORKCODE],
		workCodeType:row[ROW_ROSTER_WORKCODE_TYPE],
		shift:row[ROW_ROSTER_SHIFT],
		person:row[ROW_ROSTER_PERSON],
		calendarDate:row[ROW_ROSTER_CALENDARDATE],
		institution:row[ROW_ROSTER_INSTITUTION],
		agency:row[ROW_ROSTER_AGENCY],
		region:row[ROW_ROSTER_REGION],
		station:row[ROW_ROSTER_STATION],
		unit:row[ROW_ROSTER_UNIT],
		position:row[ROW_ROSTER_POSITION],
		rank:row[ROW_ROSTER_RANK]
	};
	
	return rosterRecord;
}

function outputRosterRecordField(rosterRecordArray) {
	finalString = "";
	
	if(rosterRecordArray != null) {
		for (var i = 0; i < rosterRecordArray.size(); i++) {
			if(finalString.length != 0) { 
				finalString = finalString.concat("<br/>");
			}
			finalString = finalString.concat(String(rosterRecordArray.get(i).workCode));
		}
	}
	
	return finalString;
}

function storeRosterRecordGlobally(rosterRecord, reportContext, columnKeys) {
	storeRecordGlobally(ROSTER_RECORD_TYPE, rosterRecord, reportContext, columnKeys);
}

function storeRecordGlobally(recordType, record, reportContext, columnKeys) {
	var keyString = recordType;
	
	for(var i = 0; i < columnKeys.length; i++) {
		var columnValue = record[columnKeys[i]];
		if(isDate(columnValue)) {
			var formattedDate = columnValue.getDate() + "-" + columnValue.getMonth() + "-" + columnValue.getYear();
			keyString = keyString + "-" + formattedDate;
		} else {
			keyString = keyString + "-" + columnValue;
		}
	}
	
	var recordsArray = reportContext.getGlobalVariable(keyString);
	if(recordsArray == null) {
		recordsArray = new Packages.java.util.ArrayList();
	}
	recordsArray.add(record);

	reportContext.setGlobalVariable(keyString, recordsArray);
}

function getGlobalRosterRecords(reportContext, dataArray) {
	return getGlobalRecords(ROSTER_RECORD_TYPE, reportContext, dataArray);
}

function getGlobalRecords(recordType, reportContext, dataArray) {
	var keyString = recordType;
	
	for(var i = 0; i < dataArray.length; i++) {
		var value = dataArray[i];

		if(isDate(value)) {
			var formattedDate = value.getDate() + "-" + value.getMonth() + "-" + value.getYear();
			keyString = keyString + "-" + formattedDate;
		} else {
			keyString = keyString + "-" + value;
		}
	}
	
	return reportContext.getGlobalVariable(keyString);
}

function isDate(object) {
	return typeof object.getMonth === 'function';
}

[AbdulQadeer-55]

Dataset fields:

[hvbtup]

That is too much code for someone to grep in one's free time, and furthermore it doesn't show the data types.

Besides, this kind of report is definitely not one that a rookie should try.
And if you have a choice, I'd recommend using a JDBC datasource for this instead of XML.
With SQL analytic functions, it is possible to filter your data on the DB side, though it certainly still is complicated and creating such a query will take several hours or even days of development work.

Honestly, I don't think it is possible to get what you want with BIRT 4.5, not even with BIRT 4.16.
I think what you need as a small building block is something like the SQL LAG and LEAD analytic functions as BIRT aggregation functions, but these are not available (though it should be possible to add them).

But maybe others have an idea.
However, don't expect anyone to spend more than a few minutes for you.
So, to increase your chances for getting help, you should create a self-contained and simplified example report including sample data and upload it as a ZIP archive.

[AbdulQadeer-55]

I understand your point. I'm sharing a mega link here that will redirect them to download a zip folder containing the complete project configurations.

Project Zip Folder: https://mega.nz/file/LTJE0DiR#AccjW3NUkrLpZ6UwfOYdoHmrNhQjEfmFkw2rdqEE6KY

[hvbtup]

Please understand that you should create a self-contained, simplified example report demonstrating your issue. That should easily fit in less than 1 MB of uncompressed files.

[AbdulQadeer-55]

Sure, let me send you all the information. I've attached a zip file containing two items: an XML file (data source and definitions) and a .rptdesign template.

BIRT Files.zip

[hvbtup]

This is not self-contained, so others cannot work with it. It references a libraryTeleStaffDefaultReportLibrary.rptlibrary which is not included.