Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » crosstab multiple grand totals / summary(crosstab)
crosstab multiple grand totals / summary [message #882515] Wed, 06 June 2012 11:59 Go to next message
Shayne Paddock is currently offline Shayne Paddock
Messages: 9
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
Re: crosstab multiple grand totals / summary [message #882671 is a reply to message #882515] Wed, 06 June 2012 20:10 Go to previous messageGo to next message
Jason Weathersby is currently offline 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
Re: crosstab multiple grand totals / summary [message #930157 is a reply to message #882515] Tue, 02 October 2012 00:15 Go to previous messageGo to next message
Louisa Buchanan is currently offline 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.
Re: crosstab multiple grand totals / summary [message #930792 is a reply to message #930157] Tue, 02 October 2012 12:48 Go to previous message
Jason Weathersby is currently offline 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
Previous Topic:Restricting Fonts displayed in Report Designer
Next Topic:Problem accessing 'params' in scripted data source
Goto Forum:
  


Current Time: Wed Jul 30 23:25:32 EDT 2014

Powered by FUDForum. Page generated in 0.01691 seconds