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

edited January 18, 2017 in DevShare Downloads
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.

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.

 * 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("");

// 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
else {
// creating pipe delimited rows
reportContext.setGlobalVariable("dataString",dString + "|" + row);

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.

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.
Sign In or Register to comment.