Gaby38 Posted November 13, 2009 Share Posted November 13, 2009 hello,I already put in place alternate color bands in reports with detail band by using conditionnal styles. I want to do the samething in a crosstab (summary band).I found somewhere in this forum a way to do that by creating a variable in the main report and a "measure" in the crosstab with the same name , evoluting in the same way according the line number and piloting a conditionnal style. It works well but only for the "measures" part : the row header is not affected because the piloting measure is undefined for the row header.Has somebody on this forum fixed that or found another way to do the same thing?Thanks a lot for your help Link to comment Share on other sites More sharing options...
Gaby38 Posted November 14, 2009 Author Share Posted November 14, 2009 In a more general way, is it possible in a crosstab to have variables or something similar available for the header objects?? Link to comment Share on other sites More sharing options...
Gaby38 Posted November 14, 2009 Author Share Posted November 14, 2009 Finally I succeeded.As said in my first post, I created in main report a variable "row_count" representative of the line number in crosstab ( that is different from query response line number) and I created in crosstab not a measure variable but a row header variable "row_count" that I put at the top of the row header variables : this variable has for bucket expression the main report variable row_count.I always have the conditionnal style piloted by the row_count variable ( row_count modulo 2)I affect the conditionnal style and the "opaque" property to the cell containing row_count. 'row_count header) The report element is set to transparent.I affect also the conditionnal style to the detail/detail variables.And it works !!! The whole line has the right colour different from the previous one.In order not to have the "row_count" cell displayed at the beginnig of the row, I set the "Print when expression" to false and the width property to "0".Perhaps is there a better way to do that.Hope will be useful to other guysGabyPost Edited by Gaby38 at 11/14/2009 20:54 Link to comment Share on other sites More sharing options...
anbuselvan2558 Posted December 12, 2009 Share Posted December 12, 2009 Hi Gaby,Thanks for the solution. Even I need a similar kind of report to be genrated with a different colors in alternate header. I am new to jasper. If you could give a sample code it would be really helpful.Thanks in AdvanceAnbu Link to comment Share on other sites More sharing options...
Gaby38 Posted December 14, 2009 Author Share Posted December 14, 2009 Hello,Hereafter, the code I used for the "style" part : <style name="BandStyle" isDefault="false"> <conditionalStyle> <conditionExpression><![CDATA[new Boolean($V{Row_count}.intValue()%new Integer(2).intValue()==0)]]></conditionExpression> <style isDefault="false" style="BandStyle" backcolor="#99FFFF"/> </conditionalStyle> <conditionalStyle> <conditionExpression><![CDATA[new Boolean($V{Row_count}.intValue()%new Integer(2).intValue()!=0)]]></conditionExpression> <style isDefault="false" style="BandStyle" backcolor="#FFCCFF"/> </conditionalStyle> </style>For the control variable in the "Main" report : <variable name="Row_count" class="java.lang.Integer" resetType="None" calculation="DistinctCount"> <variableExpression><![CDATA[$F{IdIns}]]></variableExpression> <initialValueExpression><![CDATA[new Integer(0)]]></initialValueExpression> </variable>Notice that the $F{IdIns} field is a field of the query result which can be considered as an "id" for each row returned, that means that the variable "row_count" acts as a line number. And in the crosstab , as said, I created a rowgroup which is not visible and used only to activate the conditional style: <rowGroup name="Row_count" width="0"> <bucket> <bucketExpression class="java.lang.Integer"><![CDATA[$V{Row_count}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="BandStyle"> <textField> <reportElement x="0" y="0" width="0" height="19"> <printWhenExpression><![CDATA[new Boolean(false)]]></printWhenExpression> </reportElement> <textElement/> <textFieldExpression class="java.lang.Integer"><![CDATA[$V{Row_count}]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents/> </crosstabTotalRowHeader> </rowGroup>I don't master all the subtilities of this solution : I suppose that there is a "mapping" of the variables by their names which allows to use in a crosstab a "variable" defined in the main report.In all cases, this solution is working Code: <rowGroup name="Row_count" width="0"> <bucket> <bucketExpression class="java.lang.Integer"><![CDATA[$V{Row_count}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="BandStyle"> <textField> <reportElement x="0" y="0" width="0" height="19"> <printWhenExpression><![CDATA[new Boolean(false)]]></printWhenExpression> </reportElement> <textElement/> <textFieldExpression class="java.lang.Integer"><![CDATA[$V{Row_count}]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents/> </crosstabTotalRowHeader> </rowGroup> Link to comment Share on other sites More sharing options...
acisser Posted November 3, 2012 Share Posted November 3, 2012 I will try to briefly explain all the problems I faced while trying to apply Gaby's (prevoius post) approach, and how did I solved it, using Ireports.Read Only this simple tutorial If you're thinking to add row group totals into you cross tab table, otherwise, Gaby's prevoius post is sufficient to achieve the task. Consider that transparent property is nowadays called opacity.It's very important to notice that this tutorial is based on a SQL query Data Source, but if you're using Jasper API directly from java or any Jasper Integration like Spring Jasper View, this tutorial will still be valid as the only change you need to do is to take the sql query from the jrxml to your backend, but everything remains the same.1. First of all, I suggest to use an SQL query data source for doing more agile tests.2. The SQL query that works as the main dataSource, should have an extra "row count" field starting in 1 that represents the row number in the resulting crosstable.For exampleROW GROUP COLUMN GROUP VALUE ROW_COUNTDISTRIBUTOR ONE PRODUCT ONE 10 1DISTRIBUTOR ONE PRODUCT THREE 20 1DISTRIBUTOR ONE PRODUCT TWO 30 1 DISTRIBUTOR TWO PRODUCT ONE 10 2DISTRIBUTOR TWO PRODUCT THREE 20 2DISTRIBUTOR TWO PRODUCT TWO 30 2 2. The Main DataSet for the crosstab component should be previously sorted in your SQL, using order by, before delivering to Jasper.Note: that in the prevoius table PRODUCT THREE is delivered first than PRODUCT TWO because the necesity of COLUMN GROUP sorting.Note: that in the prevoius table DISTRIBUTOR ONE is delivered first than DISTRIBUTOR TWO because the necesity of ROW GROUP sorting. 3. Create a JDBC Connection, then a SQL Query and finally a CrossTab Table. For more detail look on this vide tutorial. (Connections to specific database providers should need providers jars to be registered on Ireports classpath, use the correct jdbc driver jar depending on your database version) My SQL Query select statement looks very similar to this. select sum(sales.sales_volume) VOLUME , max(distributor.description) ROW GROUP , max(sapproduct.description) COLUMN GROUP , 1 ROW_COUNT -- this field should be autogenerated somewherefrom CIA.ld_sales sales..group by distributor.id,sapproduct.descriptionorder by rowcount, product If you followed CrossTab Tutorial on Ireports should have created for you the following Fields, otherwise create them. <field name="VOLUME" class="java.math.BigDecimal"/> <field name="ROW GROUP" class="java.lang.String"/> <field name="ROWCOUNT" class="java.math.BigDecimal"/> <field name="COLUMN GROUP" class="java.lang.String"/> 4. After creating your CrossTab Table, do a right click over it on Ireports "Main report" canvas and select Crosstab Data and check on Data is pre sorted. After doing this close the dialog. 5. Add BandStyle Style node after property node but before querystring. For more information look http://jasperreports.sourceforge.net/xsd/jasperreport.xsd <style name="BandStyle"><conditionalStyle><conditionExpression><![CDATA[new Boolean($V{ROWCOUNT}.intValue()%new Integer(2).intValue()==0)]]></conditionExpression><style backcolor="#D8D8D8"/></conditionalStyle><conditionalStyle><conditionExpression><![CDATA[new Boolean($V{ROWCOUNT}.intValue()%new Integer(2).intValue()!=0)]]></conditionExpression><style backcolor="#FFFFFF"/></conditionalStyle></style> 6. Add variable node after field nodes <variable name="ROWCOUNT" class="java.lang.Integer" resetType="None" calculation="DistinctCount"> <variableExpression><![CDATA[$F{ROWCOUNT}]]></variableExpression> <initialValueExpression><![CDATA[new Integer(0)]]></initialValueExpression> </variable> 7. Add the following rowGroup node after the existing rowGroupNode the ireports wizard created automatically for you. <rowGroup name="ROWCOUNT" width="0"> <bucket class="java.lang.Integer"> <bucketExpression><![CDATA[$V{ROWCOUNT}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="BandStyle"> <textField> <reportElement x="0" y="0" width="0" height="0"> <printWhenExpression><![CDATA[new Boolean(false)]]></printWhenExpression> </reportElement> <textElement/> <textFieldExpression><![CDATA[$V{ROWCOUNT}]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents/> </crosstabTotalRowHeader> </rowGroup> 8. Save the report template, close the template and reopen it. 9. Enter to Cross Tab Canvas, that is the second tab on ireports canvas, beside Main report tab. Click on the Measure Field, that is the one that presents the data in the table and in the properties Palette check on Opaque checkbox, and change the style to BandStyle. 10. Save the report template 11. Preview the Template and Voilá, there is a working crosstable with row group totals with even rows backroung in a different color than odd rows background. END Don't forget that the jrxml configuration is important, but more important for this approach is the way and structure you serve data to jasper. Use Canvas to resize row header cell size Use Stretch with Overflow true to avoid cell strippings accompained with Stretch type Relative to band height to fix some extra rows that Jasper inserts when using Stretch with overflow. Set on each Cell in CrossTab correct alignment for better presentation Every Cell in CrossTab has two widths, one of the container and the other of the text that displays, if you change one the other should be changed with the same size. #TAG Jasper Reports JasperReports dynamic column report. Abdoul Cissé Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now