mysql - JasperReports Server - Partial results in crosstab -
i met rather difficult problem describe solution simple think. let's go!
i have database 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 create following crosstab in report: http://framacalc.org/q3y7281ono
i use parameters:
global_liste_projets
select project.name projet project order projet global_liste_noms
select employee.name nom, employee.firstname prenom employee order nom, prenom here sql query :
select imputation.`hours` heures, imputation.`tobebilled` facturable, imputation.`day` jours, employee.`name` nom, project.`name` projet, timesheet.`id` timesheet_id, imputation.`description` commentaire, employee.`firstname` prenom `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` imputation.day between $p{global_date_from}and$p{global_date_to} , $x{ in ,project.`name`,global_liste_projets} , $x{ in ,employee.`name`,global_liste_noms} order jours asc, projet asc, nom asc so, report generation going well. when read final report , there more 1 imputation filled in billable or non-billable specific date, there 1 imputation appears! instead there have several. when not crosstab, see imputations. think problem located in crosstab?
my problem : oi61.tinypic.com/96jh3l.jpg , want : oi62.tinypic.com/10d7ebk.jpg. , document in addition try explain better problem: framacalc.org/q3y7281ono.
has ever had similar problem?
if not clear or missing information, not hesitate :)
my complete 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.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` heures, imputation.`tobebilled` facturable, imputation.`day` jours, employee.`name` nom, project.`name` projet, timesheet.`id` timesheet_id, imputation.`description` commentaire, employee.`firstname` prenom `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` imputation.day between $p{global_date_from}and$p{global_date_to} , $x{ in ,project.`name`,global_liste_projets} , $x{ in ,employee.`name`,global_liste_noms} order 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>
Comments
Post a Comment