nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Dynamcially generating a crosstab (dimensions, measures and adding totals)

MMirabito

Hello to all, 
 
I recently started working with the open source version of BIRT 2.5.x so I am really new therefore forgive if the question is not appropriate, but I am kind of lost. 
 
I am trying to develop a ?dynamic crosatab? (ie scripted). Reading through the forum I was able to piece together the javascript code to allow at runtime inserting a crosstab, dimensions X, Y and measures. In nutshell the user selects dimension and measures on a web pages which in turn are passed as parameters to the BIRT report. The data source, data set and cube is already predefined in the report. When the user clicks execute code in the ?beforeFactory? is that creates the crosstab on the fly. 
 
Now I have some additional requirement but for some reason I am not able to find helpful information or maybe I just don?t get it. I am sure someone must have had a similar requirement in the past 
 
The user also needs the ability to decide in the web page whether a particular dimension need to have ?aggregate totals? for ?row area? and ?column area?. I cannot figure out what objects and methods sequence I need to invoke in order to create at runtime the aggregate rows or columns. 
 
Thank you in advance for any guidance or sample code that can point me in the right direction 
 
Thanks 
 
Max 
 
Here is the code I have pieced together located in the ?beforeFactory? factory event 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage( Packages.org.eclipse.birt.report.model.api );
importPackage( Packages.org.eclipse.birt.report.item.crosstab.core.util);
importPackage( Packages.org.eclipse.birt.report.item.crosstab.core.de);
importPackage( Packages.org.eclipse.birt.report.item.crosstab.core);
importPackage( Packages.java.io );

var out = new PrintWriter( new FileWriter( "c:/log.txt", false ) );

out.println("start...");

try{
var reportDesignHandle = reportContext.getReportRunnable().designHandle.getDesignHandle();

// Extract report parameters used in creating X, Y Diemnsions and measures
var s = reportContext.getParameterValue("rp_xDimensions");
var xDimensions = s.split(",");

var s = reportContext.getParameterValue("rp_yDimensions");
var yDimensions = s.split(",");

var s = reportContext.getParameterValue("rp_measures");
var measures = s.split(",");

var elementFactory = reportDesignHandle.getElementFactory();
var cubeHandle = reportDesignHandle.getCubes().get(0);
var xtab = CrosstabExtendedItemFactory.createCrosstabReportItem(reportDesignHandle, cubeHandle, "MyCrosstab" );

reportDesignHandle.getBody().add(xtab);
reportItem = xtab.getReportItem( );
xTabHandle = reportItem;

// Create X dimensions
for (i = 0; i < xDimensions.length; i++) {
insertDimension(xTabHandle, cubeHandle, ICrosstabConstants.ROW_AXIS_TYPE, xDimensions[i], i);
}

// Create X dimensions
for (i = 0; i < yDimensions.length; i++) {
insertDimension(xTabHandle, cubeHandle, ICrosstabConstants.COLUMN_AXIS_TYPE, yDimensions[i], i);
}

// Create measures
for (i = 0; i < measures.length; i++) {
insertMeasure(xTabHandle, cubeHandle, measures[i], i);
}

out.println("end...");

}catch(e){
out.println(e);
}
out.close();

// *****************************************
// * Create xTab dimensions for rows (X - ROW_AXIS_TYPE) and columns (Y - COLUMN_AXIS_TYPE)
// *
// *****************************************
function insertDimension(xTabHandle, cubeHandle, dimensionType, dimensionName, order) {
out.println("Begin insertDimension...");

var dimension = cubeHandle.getDimension(dimensionName);
var level = dimension.getDefaultHierarchy().getLevel(0);

var dvh = xTabHandle.insertDimension(dimension, dimensionType, order);
var levelViewHandle = dvh.insertLevel(level, order);
var cellHandle = levelViewHandle.getCell( );
var eii = xTabHandle.getModelHandle( );

var bindingColumn = StructureFactory.newComputedColumn( eii, level.getName( ) );
var bindingHandle = eii.addColumnBinding( bindingColumn,false );

bindingColumn.setDataType( level.getDataType( ) );
var exp = "dimension['" + dimension.getName()+"']['"+level.getName()+"']";

out.println("exp: " +exp);
bindingColumn.setExpression( exp);

var dataHandle = elementFactory.newDataItem( level.getName( ) );
dataHandle.setResultSetColumn( bindingHandle.getName( ) );
dataHandle.setProperty("style", "xDimensionStyle");
cellHandle.addContent( dataHandle );

out.println("End insertDimension...");
}

// *****************************************
// * Insert measure
// *****************************************
function insertMeasure(xTabHandle, cubeHandle, measureName, order) {
out.println("Begin insertMeasure...");

var measure = cubeHandle.getMeasure(measureName);

var mvh = xTabHandle.insertMeasure(measure, order);
mvh.getCell().getContents().get(0).setProperty("style", "myValues");

mvh.addHeader( );
labelHandle = elementFactory.newLabel( null );
labelHandle.setText( measure.getDisplayName() );
mvh.getHeader( ).addContent( labelHandle );

out.println("End insertMeasure...");
}

</pre>

Find more posts tagged with

Comments

JasonW

To add a grand total just use code like:

mygtlabel = elementFactory.newLabel( null );
mygtlabel.setText("Grand Total:");
tst = xtabHandle.addGrandTotal(ICrosstabConstants.ROW_AXIS_TYPE );
tst.addContent( mygtlabel );
actualdata = mvh.getAggregationCell( 0 ).getContents().get(0);
actualdata.setProperty("style", "aggrstyle");

use COLUMN_AXIS_TYPE to put it on the column.

Jason

MMirabito

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="68597" data-time="1285025428" data-date="20 September 2010 - 04:30 PM">
To add a grand total just use code like: 
 
mygtlabel = elementFactory.newLabel( null ); 
mygtlabel.setText("Grand Total:"); 
tst = xtabHandle.addGrandTotal(ICrosstabConstants.ROW_AXIS_TYPE ); 
tst.addContent( mygtlabel ); 
actualdata = mvh.getAggregationCell( 0 ).getContents().get(0); 
actualdata.setProperty("style", "aggrstyle"); 
 
use COLUMN_AXIS_TYPE to put it on the column. 
 
Jason </blockquote>
 
Hi Jason thanks a lot for tip I was able to add the "grand total" at the bottom but now I am trying to figure out how I could add sub-total when the dimensions break ( I do not think I can use the addGrandTotal method) 
 
So I found addAggregation but it?s not working for me. Attached is what a sample output should look like once it is dynamically created can you suggest how this can be done via scripting. 
 
Lastly, I am looking for the BIRT 2.5.x API reference but what I have found so far is 2.3 <a class='bbc_url' href='http://www.birt-exchange.com/be/downloads//documentation/#currentdocs'>http://www.birt-exchange.com/be/downloads//documentation/#currentdocs</a>, do you know where I can find the link to 2.5. 
 
Thanks again 
 
Max

JasonW

Take a look at this example. (Verify the subtotals as well).

Jason

MMirabito

Hi Jason 
 
Thanks for the example it's begging to shape up well. Based on other code that you provided in another sample I was able to figure out how to include the row dimension value on the sub total label so that when the data breaks the label would read "AA-BB Subtotal". To do this I created a ?computed column? and set an expression but I am wondering is this the correct way to do this or is there a easier way? Below is the code 
 
Thanks in advance, 
 
Max 
 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
function insertSubTotal(xTabHandle, cubeHandle, index, dimensionName, measureCount) {
out.println("Begin insertSubtTotal...");

var dvh = xTabHandle.getDimension( ICrosstabConstants.ROW_AXIS_TYPE, index);
var levelViewHandle = dvh.getLevel(0);
var measureList = new ArrayList();
var functionList = new ArrayList();

var mvh = xTabHandle.getMeasure(0);

measureList.add(mvh);
functionList.add("SUM");

var cch = levelViewHandle.addSubTotal(measureList, functionList);

// Create computed column for the subtotal label
var dimension = cubeHandle.getDimension(dimensionName);
var level = dimension.getDefaultHierarchy().getLevel(0);
var eii = xTabHandle.getModelHandle();
var bindingColumn = StructureFactory.newComputedColumn(eii, level.getName());
var bindingHandle = eii.addColumnBinding(bindingColumn,false);
bindingColumn.setDataType("String");
var exp = "'(' + dimension['" + dimension.getName()+"']['"+level.getName()+"'] + ') Subtotal'";
bindingColumn.setExpression( exp);

out.println("exp: " +exp);

var dataHandle = elementFactory.newDataItem(level.getName());
dataHandle.setResultSetColumn(bindingHandle.getName());
dataHandle.setProperty("style", "xDimensionStyle");

cch.addContent(dataHandle);

// Set the CCS style
for (var i = 0; i < measureCount; i++) {
var mvh = xTabHandle.getMeasure(i);
var actualdata = mvh.getAggregationCell(index).getContents().get(0);
actualdata.setProperty("style", "xSubTotals-" + index);
}

out.println("End insertSubtTotal...");
}

</pre>

JasonW

That is a perfectly acceptable way of getting the label to be dynamic.

Jason