|
|
|
Re: Crosstable problem after BIRT 4.2 upgrade [message #1066756 is a reply to message #1065823] |
Thu, 04 July 2013 08:53 |
David Vinicz Messages: 13 Registered: July 2012 |
Junior Member |
|
|
Hi Michael,
Unfortunately the issue is still there in 4.3. I have downloaded a new Eclipse Kepler (with BIRT of course) and tried this report (from another forum post):
<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.17" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.3.2.r232_20090202 Build <2.3.2.v20090218-0730></property>
<property name="units">in</property>
<method name="beforeFactory"><![CDATA[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);
try{
reportDesignHandle = reportContext.getReportRunnable().designHandle.getDesignHandle();
elementFactory = reportDesignHandle.getElementFactory();
cubeHandle = reportDesignHandle.getCubes().get(0);
xtab = CrosstabExtendedItemFactory.createCrosstabReportItem(reportDesignHandle, cubeHandle, "MyCrosstab" );
reportDesignHandle.getBody().add(xtab);
reportItem = xtab.getReportItem( );
xtabHandle = reportItem;
dimensionX = cubeHandle.getDimension(params["RowDimension"].value);
dimensionY = cubeHandle.getDimension(params["ColumnDimension"].value);
levelX = dimensionX.getDefaultHierarchy().getLevel(0)
levelY = dimensionY.getDefaultHierarchy().getLevel(0)
//Insert X Dimension
dvh = xtabHandle.insertDimension(dimensionX, ICrosstabConstants.ROW_AXIS_TYPE, 0);
levelViewHandle =dvh.insertLevel(levelX, 0);
cellHandle = levelViewHandle.getCell( );
eii = xtabHandle.getModelHandle( );
bindingColumn = StructureFactory.newComputedColumn( eii, levelX.getName( ) );
bindingHandle = eii.addColumnBinding( bindingColumn,false );
bindingColumn.setDataType( levelX.getDataType( ) );
exp = "dimension['" + dimensionX.getName()+"']['"+levelX.getName()+"']";
bindingColumn.setExpression( exp);
dataHandle = elementFactory.newDataItem( levelX.getName( ) );
dataHandle.setResultSetColumn( bindingHandle.getName( ) );
cellHandle.addContent( dataHandle );
//Insert Y Dimension
dvh = xtabHandle.insertDimension(dimensionY, ICrosstabConstants.COLUMN_AXIS_TYPE, 0);
levelViewHandle =dvh.insertLevel(levelY, 0);
cellHandle = levelViewHandle.getCell( );
eii = xtabHandle.getModelHandle( );
bindingColumn = StructureFactory.newComputedColumn( eii, levelY.getName( ) );
bindingHandle = eii.addColumnBinding( bindingColumn,false );
bindingColumn.setDataType( levelY.getDataType( ) );
exp = "dimension['" + dimensionY.getName()+"']['"+levelY.getName()+"']";
bindingColumn.setExpression( exp);
dataHandle = elementFactory.newDataItem( levelY.getName( ) );
dataHandle.setResultSetColumn( bindingHandle.getName( ) );
cellHandle.addContent( dataHandle );
//Measures
measure = cubeHandle.getMeasure(params["Measure"].value);
mvh = xtabHandle.insertMeasure(measure, 0);
mvh.getCell().getContents().get(0).setProperty("style", "mydata");
mvh.addHeader( );
labelHandle = elementFactory.newLabel( null );
labelHandle.setText( measure.getName() );
mvh.getHeader( ).addContent( labelHandle );
//Note that styles are imported so they do not need to be applied
}catch(e){
//importPackage( Packages.java.io );
//out = new PrintWriter( new FileWriter( "c:/test7/xtabevents.txt", true ) );
//out.println(e);
//out.close();
}
]]></method>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="bidiLayoutOrientation">ltr</property>
<parameters>
<scalar-parameter name="ColumnDimension" id="37">
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</property>
<property name="distinct">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
<list-property name="selectionList">
<structure>
<property name="value">Group</property>
<property name="label">Country</property>
</structure>
<structure>
<property name="value">Group1</property>
<property name="label">Product Line</property>
</structure>
<structure>
<property name="value">Group2</property>
<property name="label">Date</property>
</structure>
</list-property>
</scalar-parameter>
<scalar-parameter name="RowDimension" id="38">
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</property>
<property name="distinct">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
<list-property name="selectionList">
<structure>
<property name="value">Group</property>
<property name="label">Country</property>
</structure>
<structure>
<property name="value">Group1</property>
<property name="label">Product Line</property>
</structure>
<structure>
<property name="value">Group2</property>
<property name="label">Date</property>
</structure>
</list-property>
</scalar-parameter>
<scalar-parameter name="Measure" id="39">
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</property>
<property name="distinct">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
<list-property name="selectionList">
<structure>
<property name="value">SALES</property>
<property name="label">Sales</property>
</structure>
<structure>
<property name="value">PROFIT</property>
<property name="label">Profit</property>
</structure>
</list-property>
</scalar-parameter>
</parameters>
<data-sources>
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="7">
<property name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver</property>
<property name="odaURL">jdbc:classicmodels:sampledb</property>
<property name="odaUser">ClassicModels</property>
<property name="OdaConnProfileName"></property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="Data Set" id="8">
<list-property name="columnHints">
<structure>
<property name="columnName">COUNTRY</property>
<property name="displayName">COUNTRY</property>
</structure>
<structure>
<property name="columnName">PRODUCTLINE</property>
<property name="displayName">PRODUCTLINE</property>
</structure>
<structure>
<property name="columnName">ORDERDATE</property>
<property name="displayName">ORDERDATE</property>
</structure>
<structure>
<property name="columnName">SALES</property>
<property name="displayName">SALES</property>
</structure>
<structure>
<property name="columnName">PROFIT</property>
<property name="displayName">PROFIT</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">COUNTRY</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTLINE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">ORDERDATE</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">SALES</property>
<property name="dataType">float</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">PROFIT</property>
<property name="dataType">float</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">COUNTRY</property>
<property name="nativeName">COUNTRY</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTLINE</property>
<property name="nativeName">PRODUCTLINE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">ORDERDATE</property>
<property name="nativeName">ORDERDATE</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">SALES</property>
<property name="nativeName">SALES</property>
<property name="dataType">float</property>
<property name="nativeDataType">8</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">PROFIT</property>
<property name="nativeName">PROFIT</property>
<property name="dataType">float</property>
<property name="nativeDataType">8</property>
</structure>
</list-property>
<property name="queryText">select c.country, p.productline, o.orderdate, sum(od.priceeach*od.quantityordered) as Sales, sum((od.priceeach-p.buyprice)*od.quantityordered) as Profit
from customers c, products p, orders o, orderdetails od
where o.ordernumber = od.ordernumber
and od.productcode = p.productcode
and o.customernumber = c.customernumber
group by c.country, p.productline, o.orderdate</property>
</oda-data-set>
</data-sets>
<cubes>
<tabular-cube name="Data Cube" id="9">
<property name="dimensions">
<tabular-dimension name="Group" id="14">
<property name="defaultHierarchy">NewTabularHierarchy</property>
<property name="hierarchies">
<tabular-hierarchy name="NewTabularHierarchy" id="15">
<property name="levels">
<tabular-level name="COUNTRY" id="16">
<property name="dataType">string</property>
<property name="columnName">COUNTRY</property>
</tabular-level>
</property>
</tabular-hierarchy>
</property>
</tabular-dimension>
<tabular-dimension name="Group1" id="17">
<property name="defaultHierarchy">NewTabularHierarchy1</property>
<property name="hierarchies">
<tabular-hierarchy name="NewTabularHierarchy1" id="18">
<property name="levels">
<tabular-level name="PRODUCTLINE" id="19">
<property name="dataType">string</property>
<property name="columnName">PRODUCTLINE</property>
</tabular-level>
</property>
</tabular-hierarchy>
</property>
</tabular-dimension>
<tabular-dimension name="Group2" id="20">
<property name="isTimeType">true</property>
<property name="defaultHierarchy">NewTabularHierarchy2</property>
<property name="hierarchies">
<tabular-hierarchy name="NewTabularHierarchy2" id="21">
<property name="levels">
<tabular-level name="year" id="22">
<property name="dataType">integer</property>
<property name="dateTimeLevelType">year</property>
<list-property name="attributes">
<structure>
<property name="name">DateTime</property>
<property name="dataType">date-time</property>
</structure>
</list-property>
<property name="columnName">ORDERDATE</property>
</tabular-level>
<tabular-level name="quarter" id="23">
<property name="dataType">integer</property>
<property name="dateTimeLevelType">quarter</property>
<list-property name="attributes">
<structure>
<property name="name">DateTime</property>
<property name="dataType">date-time</property>
</structure>
</list-property>
<property name="columnName">ORDERDATE</property>
</tabular-level>
<tabular-level name="month" id="24">
<property name="dataType">integer</property>
<property name="dateTimeLevelType">month</property>
<list-property name="attributes">
<structure>
<property name="name">DateTime</property>
<property name="dataType">date-time</property>
</structure>
</list-property>
<property name="columnName">ORDERDATE</property>
</tabular-level>
</property>
</tabular-hierarchy>
</property>
</tabular-dimension>
</property>
<property name="measureGroups">
<tabular-measure-group name="Summary Field" id="10">
<property name="measures">
<tabular-measure name="SALES" id="11">
<expression name="measureExpression">dataSetRow["SALES"]</expression>
<property name="dataType">float</property>
</tabular-measure>
</property>
</tabular-measure-group>
<tabular-measure-group name="Summary Field1" id="12">
<property name="measures">
<tabular-measure name="PROFIT" id="13">
<expression name="measureExpression">dataSetRow["PROFIT"]</expression>
<property name="dataType">float</property>
</tabular-measure>
</property>
</tabular-measure-group>
</property>
<property name="dataSet">Data Set</property>
</tabular-cube>
</cubes>
<styles>
<style name="report" id="4">
<property name="fontFamily">"Verdana"</property>
<property name="fontSize">10pt</property>
</style>
<style name="crosstab-cell" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab" id="6">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="table-header" id="40">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-footer" id="41">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-detail" id="42">
<property name="backgroundColor">#E6ECFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">x-small</property>
<property name="fontWeight">bold</property>
<property name="color">#000080</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-group-header-1" id="43">
<property name="backgroundColor">#809FFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-group-footer-1" id="44">
<property name="backgroundColor">#809FFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="special-header" id="45">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">medium</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="crosstab-detail" id="46">
<property name="backgroundColor">#E6ECFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">x-small</property>
<property name="fontWeight">bold</property>
<property name="color">#000080</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="crosstab-header" id="47">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="mydata" id="48">
<property name="color">#004080</property>
<structure name="numberFormat">
<property name="category">Currency</property>
<property name="pattern">$ #,##0.00</property>
</structure>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<property name="topMargin">0.25in</property>
<property name="leftMargin">0.25in</property>
<property name="bottomMargin">0.25in</property>
<property name="rightMargin">0.25in</property>
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
</report>
I had this result in the web viewer ( it was worse in the preview):
https://dl.dropboxusercontent.com/u/54730350/web_viewer.PNG
Is this code outdated? Or is there any workaround for this?
Thanks for the help,
David
|
|
|
Re: Crosstable problem after BIRT 4.2 upgrade [message #1067112 is a reply to message #1066756] |
Fri, 05 July 2013 23:32 |
|
If you look at the above example's code, the measure section only adds the header label, not the actual measure. Oddly enough, this works for older versions. It appears that this is a fix for later versions to actually require that the measure element be created.
Michael
Developer Evangelist, Silanis
|
|
|
|
|
|
|
Re: Crosstable problem after BIRT 4.2 upgrade [message #1075870 is a reply to message #1065823] |
Tue, 30 July 2013 08:30 |
David Vinicz Messages: 13 Registered: July 2012 |
Junior Member |
|
|
Hi Michael!
Unfortunately I'm having the same issue with totals(CrosstabDEAPI.rptdesign)
This code worked fine in earlier vesions but after 4.2 its not adding the aggregations (and also the bindings) properly to the measure automatically. I tried to use the same method to create the binding and the aggregation cells which you mentioned related to the measures, and I could make it work with grand totals (CrosstabDEAPI_workingGrand.rptdesign)
There is a problem with this solution if I want to add subtotals, because in that case I have to calculate somehow the index of the aggregation cells, which sounds like a dirty hack:)
Could you provide some better solution?
Thanks for the help,
David
[Updated on: Tue, 30 July 2013 08:39] Report message to a moderator
|
|
|
|
|
|
Re: Crosstable problem after BIRT 4.2 upgrade [message #1084669 is a reply to message #1080542] |
Sun, 11 August 2013 23:44 |
|
Sub totals will be added on the levelViewHandle. So, if you were to add a second group to the column or row dimension, you'd add your SubTotal and Aggregation header to the outer group's levelViewHandle. This will create your cells and allow you to add your header label/data item and your aggregation to the body cell. Hope this helps. I can look at modifying your example to include this if you'd like.
Michael
Developer Evangelist, Silanis
|
|
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.06424 seconds