Alternate color bands in crosstab

[Gaby38]

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

[Gaby38]

In a more general way, is it possible in a crosstab to have variables or something similar available for the header objects??

[Gaby38]

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 guys

Gaby

Post Edited by Gaby38 at 11/14/2009 20:54

[anbuselvan2558]

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 Advance

Anbu

[Gaby38]

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>

[acisser]

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 example

ROW GROUP                                 COLUMN GROUP                  VALUE              ROW_COUNT

DISTRIBUTOR ONE                      PRODUCT ONE                       10                      1

DISTRIBUTOR ONE                      PRODUCT THREE                  20                      1

DISTRIBUTOR ONE                      PRODUCT TWO                      30                      1

 

DISTRIBUTOR TWO                      PRODUCT ONE                      10                      2

DISTRIBUTOR TWO                      PRODUCT THREE                  20                      2

DISTRIBUTOR 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 somewhere

from CIA.ld_sales sales

..

group by distributor.id,sapproduct.description

order 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é