Partial results in crosstab

0

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 Projet
From
  Project
Order By
  Projet

Global_Liste_Noms

Select
  Employee.Name As NOM,
  Employee.Firstname As Prenom
From
  Employee
Order By
  NOM,
  Prenom

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 Prenom
FROM
     `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

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 Prenom
FROM
     `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>

JonReport's picture
Joined: Aug 13 2013 - 5:25am
Last seen: 4 years 7 months ago

0 Answers:

No answers yet
Feedback