Technique to combine data from 2 crosstabs in to another visualization for comparison.

micajblock

I have worked on multiple projects where one of the requirements is to have 2 crosstabs (or tables) with different filter criteria and then have a 3rd visual (crosstab or chart) that combines both for comparison. Thanks to Clement Wong for showing me the way. <br />
The idea in general is to store the data in the crosstab in a string as it is being built. One can use same logic on a 2 tables but the code would in the on create of a detail report item. <br />

/**
 * Called when crosstab cell is being created.
 *
 * @param cellInst
 *            ICrosstabCellInstance
 * @param reportContext
 *            IReportContext
 */


function onCreateCell( cellInst, reportContext )
{
var year = null;
var pline = null;
var revenue = null;




logger = java.util.logging.Logger.getLogger("birt.report.logger");


// We only need this code for once of the detail cells
// Note that the cell id is for the cell containing the data item


if (cellInst.getCellID()==463) {
if (cellInst.getDataValue("Year") != null) {
year = cellInst.getDataValue("Year");
}


if (cellInst.getDataValue("PRODUCTLINE") != null) {
pline = cellInst.getDataValue("PRODUCTLINE");
}


if (cellInst.getDataValue("REVENUE_Product Line/PRODUCTLINE_Order Date Group/Year") != null) {
revenue = cellInst.getDataValue("REVENUE_Product Line/PRODUCTLINE_Order Date Group/Year");
}


// string containg the current row of data
var row = year + "," + pline + "," + revenue;
var dString = reportContext.getGlobalVariable("dataString");
logger.warning("current data string:" + dString);


if (dString == "") {
// first time
reportContext.setGlobalVariable("dataString",row);
}
else {
// creating pipe delimited rows
reportContext.setGlobalVariable("dataString",dString + "|" + row);
}
}
}

<br />
One key note is that this code needs to be done on only one cell. The cell ID is a property of the cell that contains the measure.<br />
<br />
Once that data is stored in the string I use a scripted data source to populate the data. Now that we have a new data set we can create a chart or another crosstab.