Partial results in crosstab

[JonReport]

Hello everyone,

I met a rather difficult problem to describe but the solution will be simple I think. Let's go!

I have a database with 4 tables:

Imputation:
Hours INT(11)
ToBeBilled TINYINT(1)
Day DATE
Description VARCHAR(256)

Employee:
Name VARCHAR(50)
Firstname VARCHAR(50)

Project:
Name VARCHAR(50)

TimeSheet:
Id INT(11)

I use these 4 tables to create the following crosstab in my report:

		Imputation.`Day`, for example 01/01/2014
Employee.`Name`, Employee.`Firstname` : NAME, Firstname	Imputation.`ToBeBilled` : false	Project.Name
	Imputation.`ToBeBilled` : true	Project.Name

I use some parameters:

Global_Liste_Projets

Select  Project.Name As ProjetFrom  ProjectOrder By  Projet[/code]

Global_Liste_Noms

Select  Employee.Name As NOM,  Employee.Firstname As PrenomFrom  EmployeeOrder By  NOM,  Prenom[/code]

Here is my SQL query :

SELECT     Imputation.`Hours` AS Heures,     Imputation.`ToBeBilled` AS Facturable,     Imputation.`Day` AS Jours,     Employee.`Name` AS NOM,     Project.`Name` AS Projet,     TimeSheet.`Id` AS TimeSheet_Id,     Imputation.`Description` AS Commentaire,     Employee.`Firstname` AS PrenomFROM     `Project` Project INNER JOIN `Imputation` Imputation ON Project.`Id` = Imputation.`Project_Id`     INNER JOIN `TimeSheet` TimeSheet ON Imputation.`TimeSheet_Id` = TimeSheet.`Id`     INNER JOIN `Employee` Employee ON TimeSheet.`Employee_Id` = Employee.`Id`WHERE     Imputation.Day BETWEEN $P{Global_Date_From}AND$P{Global_Date_To}AND $X{ IN ,Project.`Name`,Global_Liste_Projets}AND $X{ IN ,Employee.`Name`,Global_Liste_Noms}ORDER BY     Jours ASC,     Projet ASC,     NOM ASC[/code]

So, report generation is going well. But when I read my final report and there is more than one imputation filled in billable or non-billable for a specific date, there is only one imutation which appears! Instead there have several. When I do not crosstab, I see all imputations. So I think my problem is located in my crosstab ?

Has anyone ever had a similar problem?

If I was not clear or missing information, do not hesitate :)

---

My complet XML:

<?xml version="1.0" encoding="UTF-8"?><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="MyApp - TabCroise Projets" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="5dcc996d-323f-4f19-834a-0b0c1ff4fbab">    <property name="ireport.zoom" value="1.0"/>    <property name="ireport.x" value="0"/>    <property name="ireport.y" value="0"/>    <property name="ireport.jasperserver.reportUnit" value="/dev/MyApp_-_TabCroise_Projets_1"/>    <property name="ireport.jasperserver.url" value="http://jasperprod.mycompany.com:8080/jasperserver/services/repository"/>    <style name="Crosstab Data Text" hAlign="Center"/>    <style name="mycompanyStyleConditionnel">        <conditionalStyle>            <conditionExpression><![CDATA[$V{ProjetMeasure}.equals( "mycompany - Défaut" )]]></conditionExpression>            <style forecolor="#FF0000" backcolor="#FFAFAF" isBlankWhenNull="false">                <pen lineColor="#000000"/>            </style>        </conditionalStyle>        <conditionalStyle>            <conditionExpression><![CDATA[$V{ProjetMeasure}.equals( "mycompany - Previsionnel" )]]></conditionExpression>            <style forecolor="#FF0000" backcolor="#FFAFAF" isBlankWhenNull="false">                <pen lineColor="#000000"/>            </style>        </conditionalStyle>    </style>    <parameter name="Global_Date_From" class="java.util.Date">        <defaultValueExpression><![CDATA["01-01-2014"]]></defaultValueExpression>    </parameter>    <parameter name="Global_Date_To" class="java.util.Date">        <defaultValueExpression><![CDATA["12-31-2014"]]></defaultValueExpression>    </parameter>    <parameter name="Global_Liste_Noms" class="java.util.Collection">        <defaultValueExpression><![CDATA[]]></defaultValueExpression>    </parameter>    <parameter name="Global_Liste_Projets" class="java.util.Collection">        <defaultValueExpression><![CDATA[]]></defaultValueExpression>    </parameter>    <queryString>        <![CDATA[sELECT     Imputation.`Hours` AS Heures,     Imputation.`ToBeBilled` AS Facturable,     Imputation.`Day` AS Jours,     Employee.`Name` AS NOM,     Project.`Name` AS Projet,     TimeSheet.`Id` AS TimeSheet_Id,     Imputation.`Description` AS Commentaire,     Employee.`Firstname` AS PrenomFROM     `Project` Project INNER JOIN `Imputation` Imputation ON Project.`Id` = Imputation.`Project_Id`     INNER JOIN `TimeSheet` TimeSheet ON Imputation.`TimeSheet_Id` = TimeSheet.`Id`     INNER JOIN `Employee` Employee ON TimeSheet.`Employee_Id` = Employee.`Id`WHERE     Imputation.Day BETWEEN $P{Global_Date_From}AND$P{Global_Date_To}AND $X{ IN ,Project.`Name`,Global_Liste_Projets}AND $X{ IN ,Employee.`Name`,Global_Liste_Noms}ORDER BY     Jours ASC,     Projet ASC,     NOM ASC]]>    </queryString>    <field name="Heures" class="java.lang.Integer"/>    <field name="Facturable" class="java.lang.Boolean"/>    <field name="Jours" class="java.sql.Date"/>    <field name="NOM" class="java.lang.String"/>    <field name="Projet" class="java.lang.String"/>    <field name="TimeSheet_Id" class="java.lang.Integer"/>    <field name="Commentaire" class="java.lang.String"/>    <field name="Prenom" class="java.lang.String"/>    <summary>        <band height="42" splitType="Stretch">            <crosstab>                <reportElement uuid="ae07f183-bdcd-4d36-a7bb-d1b9bd70d324" x="0" y="0" width="802" height="42"/>                <box topPadding="2" leftPadding="2" bottomPadding="2" rightPadding="2"/>                <rowGroup name="NOM" width="119">                    <bucket class="java.lang.String">                        <bucketExpression><![CDATA[$F{NOM}+", "+$F{Prenom}]]></bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents backcolor="#F0F8FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField isStretchWithOverflow="true">                                <reportElement uuid="123177dd-6b7b-44e6-a57f-1068dbef6078" style="Crosstab Data Text" x="0" y="0" width="119" height="50"/>                                <box topPadding="2" leftPadding="2" bottomPadding="2" rightPadding="2">                                    <pen lineWidth="0.5"/>                                    <topPen lineWidth="0.5"/>                                    <leftPen lineWidth="0.5"/>                                    <bottomPen lineWidth="0.5"/>                                    <rightPen lineWidth="0.5"/>                                </box>                                <textElement textAlignment="Left" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{NOM}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents/>                    </crosstabTotalRowHeader>                </rowGroup>                <rowGroup name="Facturable" width="96">                    <bucket class="java.lang.Boolean">                        <bucketExpression><![CDATA[$F{Facturable}]]></bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents backcolor="#F0F8FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField isStretchWithOverflow="true">                                <reportElement uuid="ed64aa06-4e25-490e-b01f-abc509c87275" style="Crosstab Data Text" x="0" y="0" width="96" height="25"/>                                <box topPadding="2" leftPadding="2" bottomPadding="2" rightPadding="2">                                    <pen lineWidth="0.5"/>                                    <topPen lineWidth="0.5"/>                                    <leftPen lineWidth="0.5"/>                                    <bottomPen lineWidth="0.5"/>                                    <rightPen lineWidth="0.5"/>                                </box>                                <textElement textAlignment="Left" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA["Facturable : "+$V{Facturable}]]></textFieldExpression>                            </textField>                            <staticText>                                <reportElement uuid="851b0620-71bd-4bf5-9b59-7b8010b0d931" style="Crosstab Data Text" x="0" y="25" width="96" height="25"/>                                <box topPadding="2" leftPadding="2" bottomPadding="2" rightPadding="2">                                    <pen lineWidth="0.5"/>                                    <topPen lineWidth="0.5"/>                                    <leftPen lineWidth="0.5"/>                                    <bottomPen lineWidth="0.5"/>                                    <rightPen lineWidth="0.5"/>                                </box>                                <textElement textAlignment="Left" verticalAlignment="Middle"/>                                <text><![CDATA[Commentaire]]></text>                            </staticText>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents/>                    </crosstabTotalRowHeader>                </rowGroup>                <columnGroup name="Jours" height="30">                    <bucket class="java.sql.Date">                        <bucketExpression><![CDATA[$F{Jours}]]></bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents backcolor="#F0F8FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField isStretchWithOverflow="true" pattern="dd/MM/yyyy">                                <reportElement uuid="3d7232fc-1dc1-4992-a0c9-987807cf5396" style="Crosstab Data Text" x="0" y="0" width="170" height="30"/>                                <box topPadding="2" leftPadding="2" bottomPadding="2" rightPadding="2">                                    <pen lineWidth="0.5"/>                                    <topPen lineWidth="0.5"/>                                    <leftPen lineWidth="0.5"/>                                    <bottomPen lineWidth="0.5"/>                                    <rightPen lineWidth="0.5"/>                                </box>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{Jours}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents/>                    </crosstabTotalColumnHeader>                </columnGroup>                <measure name="ProjetMeasure" class="java.lang.String">                    <measureExpression><![CDATA[$F{Projet}]]></measureExpression>                </measure>                <measure name="ProjetCommentaire" class="java.lang.String">                    <measureExpression><![CDATA[$F{Commentaire}]]></measureExpression>                </measure>                <crosstabCell width="170" height="50">                    <cellContents>                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">                            <reportElement uuid="3e8d13d3-082e-49ff-9ef1-899959d21856" style="mycompanyStyleConditionnel" mode="Opaque" x="0" y="0" width="170" height="25" forecolor="#000000"/>                            <box topPadding="2" leftPadding="2" bottomPadding="2" rightPadding="2">                                <pen lineWidth="0.5"/>                                <topPen lineWidth="0.5"/>                                <leftPen lineWidth="0.5"/>                                <bottomPen lineWidth="0.5"/>                                <rightPen lineWidth="0.5"/>                            </box>                            <textElement textAlignment="Left" verticalAlignment="Middle"/>                            <textFieldExpression><![CDATA[$V{ProjetMeasure}]]></textFieldExpression>                        </textField>                        <textField isStretchWithOverflow="true" isBlankWhenNull="true">                            <reportElement uuid="c8b997a0-ed98-489f-969f-5430ef550bcd" style="Crosstab Data Text" x="0" y="25" width="170" height="25"/>                            <box topPadding="2" leftPadding="2" bottomPadding="2" rightPadding="2">                                <pen lineWidth="0.5"/>                                <topPen lineWidth="0.5"/>                                <leftPen lineWidth="0.0"/>                                <bottomPen lineWidth="0.0"/>                                <rightPen lineWidth="0.0"/>                            </box>                            <textElement textAlignment="Left" verticalAlignment="Middle"/>                            <textFieldExpression><![CDATA[$V{ProjetCommentaire}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell height="25" rowTotalGroup="NOM">                    <cellContents backcolor="#005FB3" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement uuid="596e5a5a-7a8f-40fe-80e1-fc6a94c6ddbd" style="Crosstab Data Text" x="0" y="0" width="50" height="25" forecolor="#FFFFFF"/>                            <textElement/>                            <textFieldExpression><![CDATA[$V{ProjetMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="50" columnTotalGroup="Jours">                    <cellContents backcolor="#BFE1FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement uuid="64b759b2-b2cb-4769-8771-3b95451a4385" style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>                            <textElement/>                            <textFieldExpression><![CDATA[$V{ProjetMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell rowTotalGroup="NOM" columnTotalGroup="Jours">                    <cellContents backcolor="#005FB3" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement uuid="7eb03da8-5b6c-4f9f-a0db-22894bc6f523" style="Crosstab Data Text" x="0" y="0" width="50" height="25" forecolor="#FFFFFF"/>                            <textElement/>                            <textFieldExpression><![CDATA[$V{ProjetMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell height="25" rowTotalGroup="Facturable">                    <cellContents backcolor="#BFE1FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement uuid="5f21a061-8bf6-4026-9f23-2c12653f0459" style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>                            <textElement/>                            <textFieldExpression><![CDATA[$V{ProjetMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell rowTotalGroup="Facturable" columnTotalGroup="Jours">                    <cellContents backcolor="#BFE1FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement uuid="a674fb2c-22f9-4c0c-87cc-0570b3430f68" style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>                            <textElement/>                            <textFieldExpression><![CDATA[$V{ProjetMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>            </crosstab>        </band>    </summary></jasperReport>[/code]