Eclipse Community Forums: BIRT » crosstab multiple grand totals / summary

Help

Home

Home » Archived » BIRT » crosstab multiple grand totals / summary(crosstab)

Show: Today's Messages :: Show Polls :: Message Navigator

crosstab multiple grand totals / summary [message #882515]

Wed, 06 June 2012 15:59

Shayne Paddock

Messages: 11
Registered: December 2009

Junior Member

I'm trying to add multiple grand totals / summary cells for a single measure. For example, I'd like to calculate total revenue, but also average revenue, and perhaps a variance and standard deviation. How can I create multiple summary rows at the end of the crosstab? In setting up "grand totals" it seems to only allow one cell per measure.

Kindest regards,
Shayne

Report message to a moderator

Re: crosstab multiple grand totals / summary [message #882671 is a reply to message #882515]

Thu, 07 June 2012 00:10

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Shayne

Would a derived measure work for you?
http://birtworld.blogspot.com/2012/03/birt-372-new-features.html

Jason

On 6/6/2012 11:59 AM, Shayne Paddock wrote:
> I'm trying to add multiple grand totals / summary cells for a single
> measure. For example, I'd like to calculate total revenue, but also
> average revenue, and perhaps a variance and standard deviation. How can
> I create multiple summary rows at the end of the crosstab? In setting up
> "grand totals" it seems to only allow one cell per measure.
>
>
> Kindest regards,
> Shayne

Report message to a moderator

Re: crosstab multiple grand totals / summary [message #930157 is a reply to message #882515]

Tue, 02 October 2012 04:15

Louisa Buchanan

Messages: 1
Registered: October 2012

Junior Member

The work around that I used was to have a grid for the grand total column. Then I had a separate aggregation for each cell in the grid.

I did this using the GUI Report Designer in Juno.
1. Create grand total in properties
2. Delete the content of the grand total column (not the column)
3. Drag in a grid with as many columns as you need and one row
4. Do this for header and folder
5. Drag in aggregation to the grid cells

You can also make additional measures

The problem I am having is that I can't generate this using the report engine AI.

Report message to a moderator

Re: crosstab multiple grand totals / summary [message #930792 is a reply to message #930157]

Tue, 02 October 2012 16:48

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Louisa

Can you take the following code and generate the report? Next add the grid you want and post the report back.
package DEAPI;

import java.io.IOException;

import org.eclipse.birt.core.framework.Platform;
import org.eclipse.birt.report.item.crosstab.core.ICrosstabConstants;
import org.eclipse.birt.report.item.crosstab.core.de.CrosstabCellHandle;
import org.eclipse.birt.report.item.crosstab.core.de.CrosstabReportItemHandle;
import org.eclipse.birt.report.item.crosstab.core.de.DimensionViewHandle;
import org.eclipse.birt.report.item.crosstab.core.de.LevelViewHandle;
import org.eclipse.birt.report.item.crosstab.core.de.MeasureViewHandle;
import org.eclipse.birt.report.item.crosstab.core.util.CrosstabExtendedItemFactory;
import org.eclipse.birt.report.model.api.ComputedColumnHandle;
import org.eclipse.birt.report.model.api.DataItemHandle;
import org.eclipse.birt.report.model.api.DataSetHandle;
import org.eclipse.birt.report.model.api.DesignConfig;
import org.eclipse.birt.report.model.api.DesignElementHandle;
import org.eclipse.birt.report.model.api.ElementFactory;
import org.eclipse.birt.report.model.api.ExtendedItemHandle;
import org.eclipse.birt.report.model.api.IDesignEngine;
import org.eclipse.birt.report.model.api.IDesignEngineFactory;
import org.eclipse.birt.report.model.api.LabelHandle;
import org.eclipse.birt.report.model.api.OdaDataSetHandle;
import org.eclipse.birt.report.model.api.OdaDataSourceHandle;
import org.eclipse.birt.report.model.api.ReportDesignHandle;
import org.eclipse.birt.report.model.api.ReportItemHandle;
import org.eclipse.birt.report.model.api.SessionHandle;
import org.eclipse.birt.report.model.api.StructureFactory;
import org.eclipse.birt.report.model.api.activity.SemanticException;
import org.eclipse.birt.report.model.api.elements.DesignChoiceConstants;
import org.eclipse.birt.report.model.api.elements.structures.ComputedColumn;
import org.eclipse.birt.report.model.api.extension.IReportItem;
import org.eclipse.birt.report.model.api.olap.MeasureGroupHandle;
import org.eclipse.birt.report.model.api.olap.MeasureHandle;
import org.eclipse.birt.report.model.api.olap.TabularCubeHandle;
import org.eclipse.birt.report.model.api.olap.TabularDimensionHandle;
import org.eclipse.birt.report.model.api.olap.TabularHierarchyHandle;
import org.eclipse.birt.report.model.api.olap.TabularLevelHandle;
import org.eclipse.birt.report.model.elements.interfaces.ICubeModel;
import org.eclipse.birt.report.model.elements.olap.TabularMeasure;
import org.eclipse.birt.report.model.api.olap.TabularMeasureHandle;
import com.ibm.icu.util.ULocale;

/**
* Simple BIRT Design Engine API (DEAPI) demo.
*/

public class CreateDataCube
{
ReportDesignHandle designHandle = null;
ElementFactory designFactory = null;
StructureFactory structFactory = null;

public static void main( String[] args )
{
try
{
CreateDataCube de = new CreateDataCube();
de.buildReport();
}
catch ( IOException e )
{
// TODO Auto-generated catch block
e.printStackTrace();
}
catch ( SemanticException e )
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}

void buildDataSource( ) throws SemanticException
{

OdaDataSourceHandle dsHandle = designFactory.newOdaDataSource(
"Data Source", "org.eclipse.birt.report.data.oda.jdbc" );
dsHandle.setProperty( "odaDriverClass",
"org.eclipse.birt.report.data.oda.sampledb.Driver" );
dsHandle.setProperty( "odaURL", "jdbc:classicmodels:sampledb" );
dsHandle.setProperty( "odaUser", "ClassicModels" );
dsHandle.setProperty( "odaPassword", "" );
designHandle.getDataSources( ).add( dsHandle );

}

void buildDataSet( ) throws SemanticException
{

OdaDataSetHandle dsHandle = designFactory.newOdaDataSet( "ds",
"org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" );
dsHandle.setDataSource( "Data Source" );
String qry = "Select * from customers";

dsHandle.setQueryText( qry );

designHandle.getDataSets( ).add( dsHandle );

}
void buidCube() throws SemanticException
{
TabularCubeHandle cubeHandle = designHandle.getElementFactory( )
.newTabularCube( "MyCube" );
cubeHandle.setDataSet((DataSetHandle)designHandle.getDataSets().get(0));
designHandle.getCubes( ).add( cubeHandle );

// measure group
MeasureGroupHandle measureGroupHandle = designHandle
.getElementFactory( ).newTabularMeasureGroup(
"testMeasureGroup" );
cubeHandle.setProperty( ICubeModel.MEASURE_GROUPS_PROP,
measureGroupHandle );

// measure
measureGroupHandle.add( MeasureGroupHandle.MEASURES_PROP, designFactory.newTabularMeasure( null ) );
TabularMeasureHandle measure = (TabularMeasureHandle) measureGroupHandle.getContent(MeasureGroupHandle.MEASURES_PROP, 0 );
measure.setName( "CREDITLIMIT" );
measure.setMeasureExpression( "dataSetRow['CREDITLIMIT']" );
measure.setFunction( DesignChoiceConstants.MEASURE_FUNCTION_SUM );
measure.setCalculated( false );
measure.setDataType( DesignChoiceConstants.COLUMN_DATA_TYPE_FLOAT );

TabularMeasure mhcopy = (TabularMeasure) measure.copy();
// dimension
TabularDimensionHandle dimension = designFactory.newTabularDimension( null );
cubeHandle.add(TabularCubeHandle.DIMENSIONS_PROP, dimension );
dimension.setTimeType( false );
//dimension.setDefaultHierarchy( factory
// .newTabularHierarchy( "testDefaultHierarchy" ) );

// hierarchy
dimension.add( TabularDimensionHandle.HIERARCHIES_PROP, designFactory.newTabularHierarchy( null ) );
TabularHierarchyHandle hierarchy = (TabularHierarchyHandle) dimension.getContent( TabularDimensionHandle.HIERARCHIES_PROP, 0 );
//hierarchy.setName( namePrix + hierarchy.getName( ) );
hierarchy.setDataSet( (DataSetHandle)designHandle.getDataSets().get(0) );

// level
hierarchy.add( TabularHierarchyHandle.LEVELS_PROP, designFactory.newTabularLevel( dimension, null ) );
TabularLevelHandle level = (TabularLevelHandle) hierarchy.getContent(TabularHierarchyHandle.LEVELS_PROP, 0 );
level.setName( "testlevel" );
level.setColumnName( "CUSTOMERNAME" );
level.setDataType( DesignChoiceConstants.COLUMN_DATA_TYPE_STRING );

//level2
hierarchy.add( TabularHierarchyHandle.LEVELS_PROP, designFactory.newTabularLevel( dimension, null ) );
TabularLevelHandle level2 = (TabularLevelHandle) hierarchy.getContent(TabularHierarchyHandle.LEVELS_PROP, 1 );
level2.setName( "testlevel2" );
level2.setColumnName( "CONTACTLASTNAME" );
level2.setDataType( DesignChoiceConstants.COLUMN_DATA_TYPE_STRING );

/*
level.setDateTimeLevelType( DesignChoiceConstants.DATE_TIME_LEVEL_TYPE_QUARTER );
level.setDateTimeFormat( "mm" ); //$NON-NLS-1$
// level.setInterval( DesignChoiceConstants.INTERVAL_MONTH );
level.setIntervalRange( 5 );
level.setIntervalBase( valuePrix + level.getIntervalBase( ) );
*/

ExtendedItemHandle xtab = CrosstabExtendedItemFactory.createCrosstabReportItem(designHandle, cubeHandle, "MyCrosstab" );
designHandle.getBody().add(xtab);

IReportItem reportItem = xtab.getReportItem( );
CrosstabReportItemHandle xtabHandle = (CrosstabReportItemHandle) reportItem;
//ICrosstabConstants.COLUMN_AXIS_TYPE
//ICrosstabConstants.ROW_AXIS_TYPE

//DimensionViewHandle dvh = xtabHandle.insertDimension(dimension, ICrosstabConstants.COLUMN_AXIS_TYPE, 0);
DimensionViewHandle dvh = xtabHandle.insertDimension(dimension, ICrosstabConstants.ROW_AXIS_TYPE, 0);
//insert level 1
LevelViewHandle levelViewHandle =dvh.insertLevel(level, 0);
CrosstabCellHandle cellHandle = levelViewHandle.getCell( );
//insert level 2
LevelViewHandle levelViewHandle2 =dvh.insertLevel(level2, 1);
CrosstabCellHandle cellHandle2 = levelViewHandle2.getCell( );

DesignElementHandle eii = xtabHandle.getModelHandle( );

//level one computed column
ComputedColumn bindingColumn = StructureFactory.newComputedColumn( eii, level.getName( ) );
ComputedColumnHandle bindingHandle = ((ReportItemHandle) eii).addColumnBinding( bindingColumn,false );
bindingColumn.setDataType( level.getDataType( ) );
String exp = "dimension['" + dimension.getName()+"']['"+level.getName()+"']";
bindingColumn.setExpression( exp);

//level two computed column
ComputedColumn bindingColumn2 = StructureFactory.newComputedColumn( eii, level2.getName( ) );
ComputedColumnHandle bindingHandle2 = ((ReportItemHandle) eii).addColumnBinding( bindingColumn2,false );
bindingColumn.setDataType( level2.getDataType( ) );
String exp2 = "dimension['" + dimension.getName()+"']['"+level2.getName()+"']";
bindingColumn2.setExpression( exp2);

//add data element for level 1
DataItemHandle dataHandle = designFactory.newDataItem( level.getName( ) );
dataHandle.setResultSetColumn( bindingHandle.getName( ) );
cellHandle.addContent( dataHandle );
//add data element for level 2
DataItemHandle dataHandle2 = designFactory.newDataItem( level2.getName( ) );
dataHandle2.setResultSetColumn( bindingHandle2.getName( ) );
cellHandle2.addContent( dataHandle2 );

MeasureViewHandle mvh = xtabHandle.insertMeasure(measure, 0);
//((DataItemHandle)mvh.getCell().getContents().get(0)).setProperty("style", "mydata");

//mvh.addHeader( );
MeasureViewHandle tst = (MeasureViewHandle)xtabHandle.getAllMeasures().get(0);
MeasureViewHandle tst2 = (MeasureViewHandle) tst.copy();
//System.out.println(tst2.getDataType());
LabelHandle labelHandle = designFactory.newLabel( null );
labelHandle.setText( measure.getName() );
mvh.getHeader( ).addContent( labelHandle );

}

void buildReport() throws IOException, SemanticException
{

DesignConfig config = new DesignConfig( );
config.setBIRTHome("C:/birt/birt-runtime-2_6_1/birt-runtime-2_6_1/ReportEngine");
IDesignEngine engine = null;
try{

Platform.startup( config );
IDesignEngineFactory factory = (IDesignEngineFactory) Platform
.createFactoryObject( IDesignEngineFactory.EXTENSION_DESIGN_ENGINE_FACTORY );
engine = factory.createDesignEngine( config );

}catch( Exception ex){
ex.printStackTrace();
}

SessionHandle session = engine.newSessionHandle( ULocale.ENGLISH ) ;

try{
//open a design or a template
designHandle = session.createDesign();

designFactory = designHandle.getElementFactory( );

buildDataSource();
buildDataSet();
buidCube();

// Save the design and close it.

designHandle.saveAs("output/desample/cubetest.rptdesign" );
designHandle.close( );
Platform.shutdown();
System.out.println("Finished");
}catch (Exception e){
e.printStackTrace();
}

}
}

Jason

Report message to a moderator

Previous Topic:	Restricting Fonts displayed in Report Designer
Next Topic:	Problem accessing 'params' in scripted data source

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 11:18:50 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter