raoh Posted January 18, 2019 Share Posted January 18, 2019 Dear Community,I'm asking for your help to help me solve my problem.I have created, already, one crosstab which contains not cumulative values.Now i have to do the same crosstab (copy/paste the previous one) but with cumulative values. I created variable and i changed value of Increment Type [Column, none] and Reset Type [Report, Column, None] But none of these combinations worked. I had either the non-cumulative values, or incomprehensible cumulations :/In some words, my crosstab contains for 1 store :number or articles per month, among all these articles : how many cars and its percentage ( number of car / total of the articles)how many motorcycle and its percentage (number of motorcycle / total of the articles)In my case, do i have to do cumulate in SQL ?-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Please find below my jrxml :<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 7.2.0.final using JasperReports Library version 6.6.0 --><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="Cumul" language="groovy" pageWidth="1191" pageHeight="842" orientation="Landscape" columnWidth="1187" leftMargin="2" rightMargin="2" topMargin="2" bottomMargin="2" isIgnorePagination="true" uuid="6db6c910-3f19-4aa3-8ed8-cdbb3ff9b61d"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="net.sf.jasperreports.export.xls.ignore.cell.border" value="false"/> <property name="net.sf.jasperreports.export.xls.collapse.row.span" value="false"/> <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/> <property name="net.sf.jasperreports.print.keep.full.text" value="true"/> <property name="net.sf.jasperreports.export.xls.white.page.background" value="true"/> <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w1" value="757"/> <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w2" value="233"/> <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="225"/> <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="769"/> <property name="com.jaspersoft.studio.report.description" value=""/> <style name="Crosstab_CH" mode="Opaque" backcolor="#A9A9A9"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab_CG" mode="Opaque" backcolor="#BFE1FF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab_CT" mode="Opaque" backcolor="#005FB3"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Crosstab_CD" mode="Opaque" backcolor="#FFFFFF"> <box> <pen lineWidth="0.5" lineColor="#000000"/> <topPen lineWidth="0.5" lineColor="#000000"/> <leftPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> <rightPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Style1" backcolor="#5BAEDE"/> <parameter name="Month_start" class="java.lang.String"> <defaultValueExpression><![CDATA['2018-01']]></defaultValueExpression> </parameter> <parameter name="Month_end" class="java.lang.String"> <defaultValueExpression><![CDATA['2018-12']]></defaultValueExpression> </parameter> <queryString> <![CDATA[with mySet as (SELECT 73 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '01-Janvier.2017' as mois, '2017-01' as YearMonth from dual UNION SELECT 40 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '02-Fevrier.2017' as mois, '2017-02' as YearMonth from dual UNION SELECT 16 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '03-Mars.2017' as mois, '2017-03' as YearMonth from dual UNION SELECT 63 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '04-Avril.2017' as mois, '2017-04' as YearMonth from dual UNION SELECT 3 as myNumber, 'store_1' as store_lib,'motorcycle' as Type, '05-Mai.2017' as mois, '2017-05' as YearMonth from dual UNION SELECT 84 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '06-Juin.2017' as mois, '2017-06' as YearMonth from dual UNION SELECT 11 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '07-Juillet.2017' as mois, '2017-07' as YearMonth from dual UNION SELECT 80 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '08-Août.2017' as mois, '2017-08' as YearMonth from dual UNION SELECT 60 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '09-Septembre.2017' as mois, '2017-09' as YearMonth from dual UNION SELECT 76 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '10-Octobre.2017' as mois, '2017-10' as YearMonth from dual UNION SELECT 83 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '11-Novembre.2017' as mois, '2017-11' as YearMonth from dual UNION SELECT 64 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '12-Décembre.2017' as mois, '2017-12' as YearMonth from dual UNION SELECT 68 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '01-Janvier.2018' as mois, '2018-01' as YearMonth from dual UNION SELECT 44 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '02-Fevrier.2018' as mois, '2018-02' as YearMonth from dual UNION SELECT 85 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '03-Mars.2018' as mois, '2018-03' as YearMonth from dual UNION SELECT 97 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '04-Avril.2018' as mois, '2018-04' as YearMonth from dual UNION SELECT 35 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '05-Mai.2018' as mois, '2018-05' as YearMonth from dual UNION SELECT 76 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '06-Juin.2018' as mois, '2018-06' as YearMonth from dual UNION SELECT 53 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '07-Juillet.2018' as mois, '2018-07' as YearMonth from dual UNION SELECT 15 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '08-Août.2018' as mois, '2018-08' as YearMonth from dual UNION SELECT 74 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '09-Septembre.2018' as mois, '2018-09' as YearMonth from dual UNION SELECT 46 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '10-Octobre.2018' as mois, '2018-10' as YearMonth from dual UNION SELECT 70 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '11-Novembre.2018' as mois, '2018-11' as YearMonth from dual UNION SELECT 77 as myNumber,'store_1' as store_lib,'motorcycle' as Type, '12-Décembre.2018' as mois, '2018-12' as YearMonth from dualUNIONSELECT 101 as myNumber,'store_1' as store_lib,'car' as Type, '01-Janvier.2017' as mois, '2017-01' as YearMonth from dual UNION SELECT 113 as myNumber,'store_1' as store_lib,'car' as Type, '02-Fevrier.2017' as mois, '2017-02' as YearMonth from dual UNION SELECT 58 as myNumber,'store_1' as store_lib,'car' as Type, '03-Mars.2017' as mois, '2017-03' as YearMonth from dual UNION SELECT 68 as myNumber,'store_1' as store_lib,'car' as Type, '04-Avril.2017' as mois, '2017-04' as YearMonth from dual UNION SELECT 39 as myNumber, 'store_1' as store_lib,'car' as Type, '05-Mai.2017' as mois, '2017-05' as YearMonth from dual UNION SELECT 89 as myNumber,'store_1' as store_lib,'car' as Type, '06-Juin.2017' as mois, '2017-06' as YearMonth from dual UNION SELECT 140 as myNumber,'store_1' as store_lib,'car' as Type, '07-Juillet.2017' as mois, '2017-07' as YearMonth from dual UNION SELECT 38 as myNumber,'store_1' as store_lib,'car' as Type, '08-Août.2017' as mois, '2017-08' as YearMonth from dual UNION SELECT 72 as myNumber,'store_1' as store_lib,'car' as Type, '09-Septembre.2017' as mois, '2017-09' as YearMonth from dual UNION SELECT 82 as myNumber,'store_1' as store_lib,'car' as Type, '10-Octobre.2017' as mois, '2017-10' as YearMonth from dual UNION SELECT 92 as myNumber,'store_1' as store_lib,'car' as Type, '11-Novembre.2017' as mois, '2017-11' as YearMonth from dual UNION SELECT 107 as myNumber,'store_1' as store_lib,'car' as Type, '12-Décembre.2017' as mois, '2017-12' as YearMonth from dual UNION SELECT 85 as myNumber,'store_1' as store_lib,'car' as Type, '01-Janvier.2018' as mois, '2018-01' as YearMonth from dual UNION SELECT 69 as myNumber,'store_1' as store_lib,'car' as Type, '02-Fevrier.2018' as mois, '2018-02' as YearMonth from dual UNION SELECT 33 as myNumber,'store_1' as store_lib,'car' as Type, '03-Mars.2018' as mois, '2018-03' as YearMonth from dual UNION SELECT 44 as myNumber,'store_1' as store_lib,'car' as Type, '04-Avril.2018' as mois, '2018-04' as YearMonth from dual UNION SELECT 55 as myNumber,'store_1' as store_lib,'car' as Type, '05-Mai.2018' as mois, '2018-05' as YearMonth from dual UNION SELECT 66 as myNumber,'store_1' as store_lib,'car' as Type, '06-Juin.2018' as mois, '2018-06' as YearMonth from dual UNION SELECT 77 as myNumber,'store_1' as store_lib,'car' as Type, '07-Juillet.2018' as mois, '2018-07' as YearMonth from dual UNION SELECT 88 as myNumber,'store_1' as store_lib,'car' as Type, '08-Août.2018' as mois, '2018-08' as YearMonth from dual UNION SELECT 99 as myNumber,'store_1' as store_lib,'car' as Type, '09-Septembre.2018' as mois, '2018-09' as YearMonth from dual UNION SELECT 25 as myNumber,'store_1' as store_lib,'car' as Type, '10-Octobre.2018' as mois, '2018-10' as YearMonth from dual UNION SELECT 77 as myNumber,'store_1' as store_lib,'car' as Type, '11-Novembre.2018' as mois, '2018-11' as YearMonth from dual UNION SELECT 58 as myNumber,'store_1' as store_lib,'car' as Type, '12-Décembre.2018' as mois, '2018-12' as YearMonth from dualUNIONSELECT 51 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '01-Janvier.2017' as mois, '2017-01' as YearMonth from dual UNION SELECT 72 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '02-Fevrier.2017' as mois, '2017-02' as YearMonth from dual UNION SELECT 47 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '03-Mars.2017' as mois, '2017-03' as YearMonth from dual UNION SELECT 53 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '04-Avril.2017' as mois, '2017-04' as YearMonth from dual UNION SELECT 18 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '05-Mai.2017' as mois, '2017-05' as YearMonth from dual UNION SELECT 89 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '06-Juin.2017' as mois, '2017-06' as YearMonth from dual UNION SELECT 19 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '07-Juillet.2017' as mois, '2017-07' as YearMonth from dual UNION SELECT 70 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '08-Août.2017' as mois, '2017-08' as YearMonth from dual UNION SELECT 45 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '09-Septembre.2017' as mois, '2017-09' as YearMonth from dual UNION SELECT 12 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '10-Octobre.2017' as mois, '2017-10' as YearMonth from dual UNION SELECT 25 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '11-Novembre.2017' as mois, '2017-11' as YearMonth from dual UNION SELECT 28 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '12-Décembre.2017' as mois, '2017-12' as YearMonth from dual UNION SELECT 11 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '01-Janvier.2018' as mois, '2018-01' as YearMonth from dual UNION SELECT 32 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '02-Fevrier.2018' as mois, '2018-02' as YearMonth from dual UNION SELECT 34 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '03-Mars.2018' as mois, '2018-03' as YearMonth from dual UNION SELECT 32 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '04-Avril.2018' as mois, '2018-04' as YearMonth from dual UNION SELECT 53 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '05-Mai.2018' as mois, '2018-05' as YearMonth from dual UNION SELECT 18 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '06-Juin.2018' as mois, '2018-06' as YearMonth from dual UNION SELECT 20 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '07-Juillet.2018' as mois, '2018-07' as YearMonth from dual UNION SELECT 84 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '08-Août.2018' as mois, '2018-08' as YearMonth from dual UNION SELECT 55 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '09-Septembre.2018' as mois, '2018-09' as YearMonth from dual UNION SELECT 45 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '10-Octobre.2018' as mois, '2018-10' as YearMonth from dual UNION SELECT 48 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '11-Novembre.2018' as mois, '2018-11' as YearMonth from dual UNION SELECT 32 as myNumber,'store_2' as store_lib,'motorcycle' as Type, '12-Décembre.2018' as mois, '2018-12' as YearMonth from dual UNIONSELECT 52 as myNumber,'store_2' as store_lib,'car' as Type, '01-Janvier.2017' as mois, '2017-01' as YearMonth from dual UNION SELECT 92 as myNumber,'store_2' as store_lib,'car' as Type, '02-Fevrier.2017' as mois, '2017-02' as YearMonth from dual UNION SELECT 82 as myNumber,'store_2' as store_lib,'car' as Type, '03-Mars.2017' as mois, '2017-03' as YearMonth from dual UNION SELECT 72 as myNumber,'store_2' as store_lib,'car' as Type, '04-Avril.2017' as mois, '2017-04' as YearMonth from dual UNION SELECT 62 as myNumber,'store_2' as store_lib,'car' as Type, '05-Mai.2017' as mois, '2017-05' as YearMonth from dual UNION SELECT 112 as myNumber,'store_2' as store_lib,'car' as Type, '06-Juin.2017' as mois, '2017-06' as YearMonth from dual UNION SELECT 122 as myNumber,'store_2' as store_lib,'car' as Type, '07-Juillet.2017' as mois, '2017-07' as YearMonth from dual UNION SELECT 152 as myNumber,'store_2' as store_lib,'car' as Type, '08-Août.2017' as mois, '2017-08' as YearMonth from dual UNION SELECT 85 as myNumber,'store_2' as store_lib,'car' as Type, '09-Septembre.2017' as mois, '2017-09' as YearMonth from dual UNION SELECT 95 as myNumber,'store_2' as store_lib,'car' as Type, '10-Octobre.2017' as mois, '2017-10' as YearMonth from dual UNION SELECT 99 as myNumber,'store_2' as store_lib,'car' as Type, '11-Novembre.2017' as mois, '2017-11' as YearMonth from dual UNION SELECT 92 as myNumber,'store_2' as store_lib,'car' as Type, '12-Décembre.2017' as mois, '2017-12' as YearMonth from dual UNION SELECT 93 as myNumber,'store_2' as store_lib,'car' as Type, '01-Janvier.2018' as mois, '2018-01' as YearMonth from dual UNION SELECT 123 as myNumber,'store_2' as store_lib,'car' as Type, '02-Fevrier.2018' as mois, '2018-02' as YearMonth from dual UNION SELECT 110 as myNumber,'store_2' as store_lib,'car' as Type, '03-Mars.2018' as mois, '2018-03' as YearMonth from dual UNION SELECT 103 as myNumber,'store_2' as store_lib,'car' as Type, '04-Avril.2018' as mois, '2018-04' as YearMonth from dual UNION SELECT 45 as myNumber,'store_2' as store_lib,'car' as Type, '05-Mai.2018' as mois, '2018-05' as YearMonth from dual UNION SELECT 42 as myNumber,'store_2' as store_lib,'car' as Type, '06-Juin.2018' as mois, '2018-06' as YearMonth from dual UNION SELECT 82 as myNumber,'store_2' as store_lib,'car' as Type, '07-Juillet.2018' as mois, '2018-07' as YearMonth from dual UNION SELECT 69 as myNumber,'store_2' as store_lib,'car' as Type, '08-Août.2018' as mois, '2018-08' as YearMonth from dual UNION SELECT 84 as myNumber,'store_2' as store_lib,'car' as Type, '09-Septembre.2018' as mois, '2018-09' as YearMonth from dual UNION SELECT 55 as myNumber,'store_2' as store_lib,'car' as Type, '10-Octobre.2018' as mois, '2018-10' as YearMonth from dual UNION SELECT 100 as myNumber,'store_2' as store_lib,'car' as Type, '11-Novembre.2018' as mois, '2018-11' as YearMonth from dual UNION SELECT 121 as myNumber,'store_2' as store_lib,'car' as Type, '12-Décembre.2018' as mois, '2018-12' as YearMonth from dual )SELECT Indicator ,store_lib ,Type ,mois ,YearMonth ,dimFROM ( SELECT SUM(myNumber) as Indicator ,store_lib ,Type ,mois ,YearMonth ,'1.Total' as dimfrom mySetGROUP BY store_lib ,Type ,mois ,YearMonth-----------------------UNIONSELECT SUM(myNumber) as Indicator ,store_lib ,Type ,mois ,YearMonth ,'2.Nb.Car' as dimfrom mySet where Type = 'car'GROUP BY store_lib ,Type ,mois ,YearMonth -----------------------UNIONSELECT SUM(myNumber) as Indicator ,store_lib ,Type ,mois ,YearMonth ,'3.Nb.Moto' as dimfrom mySet where Type= 'motorcycle'GROUP BY store_lib ,Type ,mois ,YearMonth-----------------------UNIONSELECT SUM(nb_car/total) as Indicator ,n.store_lib ,'' as Type ,n.mois ,n.YearMonth ,'4.Car[%]' as dimFROM ( ( SELECT SUM(myNumber) as nb_car ,store_lib ,mois ,YearMonth from mySet where Type ='car' GROUP BY store_lib ,mois ,YearMonth )n JOIN ( SELECT SUM(myNumber) as total ,store_lib ,mois ,YearMonth from mySet where 1=1 GROUP BY store_lib ,mois ,YearMonth )d on n.store_lib = d.store_lib and n.mois = d.mois and n.YearMonth = d.YearMonth)GROUP BY n.store_lib ,n.YearMonth, n.mois-----------------------UNION----------------------- '5.Moto[%]' / MoisSELECT SUM(nb_moto/total) as Indicator ,n.store_lib ,'' as Type ,n.mois ,n.YearMonth ,'5.Moto[%]' as dimFROM ( ( SELECT SUM(myNumber) as nb_moto ,store_lib ,mois ,YearMonth from mySet where Type ='motorcycle' GROUP BY store_lib ,mois ,YearMonth )n JOIN ( SELECT SUM(myNumber) as total ,store_lib ,mois ,YearMonth from mySet where 1=1 GROUP BY store_lib ,mois ,YearMonth )d on n.store_lib = d.store_lib and n.mois = d.mois and n.YearMonth = d.YearMonth)GROUP BY n.store_lib ,n.YearMonth, n.moisUNIONSELECT SUM(myNumber) as Indicator ,store_lib ,Type ,'13-Total' as mois ,YearMonth ,'1.Total' as dimfrom mySetGROUP BY store_lib ,Type ,YearMonth-----------------------UNIONSELECT SUM(myNumber) as Indicator ,store_lib ,Type ,'13-Total' as mois ,YearMonth ,'2.Nb.Car' as dimfrom mySet where Type = 'car'GROUP BY store_lib ,Type ,YearMonth -----------------------UNION-----------------------SELECT SUM(myNumber) as Indicator ,store_lib ,Type ,'13-Total' as mois ,YearMonth ,'3.Nb.Moto' as dimfrom mySet where Type= 'motorcycle'GROUP BY store_lib ,Type ,YearMonth-----------------------UNION----------------------- '4.Car[%]' / TotalSELECT SUM(nb_car/total) as Indicator ,n.store_lib ,'' as Type ,n.mois ,n.YearMonth ,'4.Car[%]' as dimFROM ( ( SELECT SUM(myNumber) as nb_car ,store_lib ,'13-Total' as mois ,substr(YearMonth,0,4) as YearMonth from mySet where Type ='car' GROUP BY store_lib ,substr(YearMonth,0,4) )n JOIN ( SELECT SUM(myNumber) as total ,store_lib ,'13-Total' as mois ,substr(YearMonth,0,4) as YearMonth from mySet where 1=1 GROUP BY store_lib ,substr(YearMonth,0,4) )d on n.store_lib = d.store_lib and n.mois = d.mois and n.YearMonth = d.YearMonth)GROUP BY n.store_lib ,n.YearMonth, n.mois-----------------------UNION----------------------- '5.Moto[%]' / TotalSELECT SUM(nb_moto/total) as Indicator ,n.store_lib ,'' as Type ,n.mois ,n.YearMonth ,'5.Moto[%]' as dimFROM ( ( SELECT SUM(myNumber) as nb_moto ,store_lib ,'13-Total' as mois ,substr(YearMonth,0,4) as YearMonth from mySet where Type ='motorcycle' GROUP BY store_lib ,substr(YearMonth,0,4) )n JOIN ( SELECT SUM(myNumber) as total ,store_lib ,'13-Total' as mois ,substr(YearMonth,0,4) as YearMonth from mySet where 1=1 GROUP BY store_lib ,substr(YearMonth,0,4) )d on n.store_lib = d.store_lib and n.mois = d.mois and n.YearMonth = d.YearMonth)GROUP BY n.store_lib ,n.YearMonth, n.mois)WHERE 1=1 AND ( YearMonth between '$P!{Month_start}' AND '$P!{Month_end}' OR substr(YearMonth,0,4) = substr( '$P!{Month_start}',0,4) )]]> </queryString> <field name="INDICATOR" class="java.math.BigDecimal"> <property name="com.jaspersoft.studio.field.label" value="INDICATOR"/> </field> <field name="STORE_LIB" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="STORE_LIB"/> </field> <field name="TYPE" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="TYPE"/> </field> <field name="MOIS" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="MOIS"/> </field> <field name="YEARMONTH" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="YEARMONTH"/> </field> <field name="DIM" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="DIM"/> </field> <variable name="cumul" class="java.lang.Integer" resetType="Group" resetGroup="Store" incrementType="Group" incrementGroup="mois" calculation="Sum"> <variableExpression><![CDATA[$F{INDICATOR}]]></variableExpression> <initialValueExpression><![CDATA[0]]></initialValueExpression> </variable> <group name="mois"> <groupExpression><![CDATA[$F{MOIS}]]></groupExpression> </group> <group name="Store"> <groupExpression><![CDATA[$F{STORE_LIB}]]></groupExpression> </group> <summary> <band height="100" splitType="Stretch"> <crosstab> <reportElement x="0" y="0" width="240" height="50" uuid="81e0b413-fda3-4c6a-ae89-84f84ae4a0be"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/> <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab_CH"/> <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab_CG"/> <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab_CT"/> <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab_CD"/> </reportElement> <rowGroup name="STORE_LIB" width="50"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{STORE_LIB}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="Style1"> <box> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textField> <reportElement x="0" y="0" width="50" height="20" uuid="fa714b94-5a09-4430-98e9-2fc955cf28d9"/> <box leftPadding="4"/> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$V{STORE_LIB}]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents mode="Opaque" style="Crosstab_CT"> <staticText> <reportElement x="0" y="0" width="120" height="20" forecolor="#FFFFFF" uuid="f8393442-e7d2-4937-9b52-fa2c19f6f75f"/> <text><![CDATA[Total STORE_LIB]]></text> </staticText> </cellContents> </crosstabTotalRowHeader> </rowGroup> <rowGroup name="DIM" width="50"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{DIM}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="Style1"> <textField> <reportElement x="0" y="0" width="50" height="20" uuid="2becb92c-8ae1-44db-bc70-c4a3e880fb6a"/> <box leftPadding="4"> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$V{DIM}.substring(2)]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents mode="Opaque" style="Crosstab_CG"> <staticText> <reportElement x="0" y="0" width="60" height="20" uuid="3e8d0a50-55ca-4601-8be6-8515e0ff2afc"/> <text><![CDATA[Total DIM]]></text> </staticText> </cellContents> </crosstabTotalRowHeader> </rowGroup> <columnGroup name="MOIS" height="30"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{MOIS}]]></bucketExpression> </bucket> <crosstabColumnHeader> <cellContents mode="Opaque" style="Style1"> <textField> <reportElement x="0" y="0" width="60" height="30" uuid="1c851654-8be1-4207-80c6-8b2900bb7990"/> <box> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$V{MOIS}.substring(3)]]></textFieldExpression> </textField> </cellContents> </crosstabColumnHeader> <crosstabTotalColumnHeader> <cellContents mode="Opaque" style="Crosstab_CT"> <staticText> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="79c96333-5f65-41bf-9d3e-1c988ca6e687"/> <text><![CDATA[Total MOIS]]></text> </staticText> </cellContents> </crosstabTotalColumnHeader> </columnGroup> <measure name="INDICATOR_MEASURE" class="java.math.BigDecimal" calculation="Sum"> <measureExpression><![CDATA[$F{INDICATOR}]]></measureExpression> </measure> <crosstabCell width="60" height="20"> <cellContents mode="Opaque"> <textField> <reportElement x="0" y="0" width="60" height="20" uuid="11394aaf-ff46-47d0-a381-dc5f5b9977f4"/> <box rightPadding="2"> <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Right" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$V{INDICATOR_MEASURE}]]></textFieldExpression> <patternExpression><![CDATA[!$V{DIM}.contains( "%" )?"# ##0":"###.##%"]]></patternExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" columnTotalGroup="MOIS"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="4658eb0f-95ad-4948-8aa4-415de5f29408"/> <textFieldExpression><![CDATA[$V{INDICATOR_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="STORE_LIB"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="06eda712-510f-476a-a76a-a85814cd65c3"/> <textFieldExpression><![CDATA[$V{INDICATOR_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="STORE_LIB" columnTotalGroup="MOIS"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7c68b879-c503-4286-ac56-4ae0466902b0"/> <textFieldExpression><![CDATA[$V{INDICATOR_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="DIM"> <cellContents mode="Opaque" style="Crosstab_CG"> <textField> <reportElement x="0" y="0" width="60" height="20" uuid="fd2551a7-58a0-48b8-94b6-209dad4360bd"/> <textFieldExpression><![CDATA[$V{INDICATOR_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="DIM" columnTotalGroup="MOIS"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="39ae5f1c-0bf7-4d9e-a395-32eb39d76c39"/> <textFieldExpression><![CDATA[$V{INDICATOR_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> </crosstab> <crosstab> <reportElement positionType="Float" x="0" y="50" width="240" height="50" uuid="fff0d809-1afe-4ba7-b1c6-ba2513bfd76b"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/> <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab_CH"/> <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab_CG"/> <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab_CT"/> <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab_CD"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> </reportElement> <rowGroup name="STORE_LIB" width="50" headerPosition="Middle" mergeHeaderCells="true"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{STORE_LIB}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="Crosstab_CH"> <textField> <reportElement x="0" y="0" width="50" height="20" uuid="a06763b7-4e84-4a91-80fd-88fe42485a2e"/> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$V{STORE_LIB}]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents mode="Opaque" style="Crosstab_CT"> <staticText> <reportElement x="0" y="0" width="120" height="20" forecolor="#FFFFFF" uuid="ddedb831-5dc0-497d-bd9b-67839fb0a979"/> <text><![CDATA[Total STORE_LIB]]></text> </staticText> </cellContents> </crosstabTotalRowHeader> </rowGroup> <rowGroup name="DIM" width="50" mergeHeaderCells="true"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{DIM}]]></bucketExpression> </bucket> <crosstabRowHeader> <cellContents mode="Opaque" style="Crosstab_CH"> <textField> <reportElement x="0" y="0" width="50" height="20" uuid="4c1322a8-39a3-4c08-8781-bf83ae55eae2"/> <textElement verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$V{DIM}.substring(2)]]></textFieldExpression> </textField> </cellContents> </crosstabRowHeader> <crosstabTotalRowHeader> <cellContents mode="Opaque" style="Crosstab_CG"> <staticText> <reportElement x="0" y="0" width="60" height="20" uuid="114a489f-03b0-4533-8280-9248b42f060b"/> <text><![CDATA[Total DIM]]></text> </staticText> </cellContents> </crosstabTotalRowHeader> </rowGroup> <columnGroup name="MOIS" height="30" mergeHeaderCells="true"> <bucket class="java.lang.String"> <bucketExpression><![CDATA[$F{MOIS}]]></bucketExpression> </bucket> <crosstabColumnHeader> <cellContents mode="Opaque" style="Crosstab_CH"> <textField> <reportElement x="0" y="0" width="60" height="30" uuid="9aeafee6-6f42-42e9-b66c-650173bbda47"/> <textElement textAlignment="Center" verticalAlignment="Middle"/> <textFieldExpression><![CDATA[$V{MOIS}.substring(3)]]></textFieldExpression> </textField> </cellContents> </crosstabColumnHeader> <crosstabTotalColumnHeader> <cellContents mode="Opaque" style="Crosstab_CT"> <staticText> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="1fb731e4-9aba-42ca-aaf4-b4d840741f80"/> <text><![CDATA[Total MOIS]]></text> </staticText> </cellContents> </crosstabTotalColumnHeader> </columnGroup> <measure name="cumul_total_MEASURE" class="java.lang.Integer" calculation="Sum"> <measureExpression><![CDATA[$V{cumul}]]></measureExpression> </measure> <crosstabCell width="60" height="20"> <cellContents mode="Opaque" style="Crosstab_CD"> <textField> <reportElement x="0" y="0" width="60" height="20" uuid="4a278a73-24b8-48b9-9f55-3b96bebed214"/> <textElement textAlignment="Center" verticalAlignment="Middle"> <font isBold="true"/> </textElement> <textFieldExpression><![CDATA[$V{cumul_total_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" columnTotalGroup="MOIS"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="5baf9050-5181-4a11-a005-96d97d9e7599"/> <textFieldExpression><![CDATA[$V{cumul_total_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="STORE_LIB"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="1c9bb6d0-3ca4-4a3f-819d-951022c65873"/> <textFieldExpression><![CDATA[$V{cumul_total_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="STORE_LIB" columnTotalGroup="MOIS"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="d5c6e679-ffcb-4491-aaa0-abe6c52b998e"/> <textFieldExpression><![CDATA[$V{cumul_total_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="DIM"> <cellContents mode="Opaque" style="Crosstab_CG"> <textField> <reportElement x="0" y="0" width="60" height="20" uuid="324bfb6e-0700-4a49-bf52-9b85f7486c74"/> <textFieldExpression><![CDATA[$V{cumul_total_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> <crosstabCell width="60" height="20" rowTotalGroup="DIM" columnTotalGroup="MOIS"> <cellContents mode="Opaque" style="Crosstab_CT"> <textField> <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="4a32f6c4-4efa-4fc9-ab98-05fe8df7b4ca"/> <textFieldExpression><![CDATA[$V{cumul_total_MEASURE}]]></textFieldExpression> </textField> </cellContents> </crosstabCell> </crosstab> </band> </summary></jasperReport> 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