Cumulative Sum in Crosstab per month, per store

0

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 dual
UNION
SELECT 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 dual
UNION
SELECT 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 
UNION
SELECT 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
    ,dim
FROM (    
SELECT
    SUM(myNumber) as Indicator
    ,store_lib
    ,Type
    ,mois
    ,YearMonth
    ,'1.Total' as dim
from mySet
GROUP BY     
    store_lib
    ,Type
    ,mois
    ,YearMonth
-----------------------
UNION
SELECT
    SUM(myNumber) as Indicator
    ,store_lib
    ,Type
    ,mois
    ,YearMonth
    ,'2.Nb.Car' as dim
from mySet
    where Type = 'car'
GROUP BY     
    store_lib
    ,Type
    ,mois
    ,YearMonth    
-----------------------
UNION
SELECT
    SUM(myNumber) as Indicator
    ,store_lib
    ,Type
    ,mois
    ,YearMonth
    ,'3.Nb.Moto' as dim
from mySet
    where Type= 'motorcycle'
GROUP BY     
    store_lib
    ,Type
    ,mois
    ,YearMonth
-----------------------
UNION
SELECT
    SUM(nb_car/total) as Indicator
    ,n.store_lib
    ,'' as Type
    ,n.mois
    ,n.YearMonth
    ,'4.Car[%]' as dim
FROM
 (
    (
    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[%]' / Mois
SELECT
    SUM(nb_moto/total) as Indicator
    ,n.store_lib
    ,'' as Type
    ,n.mois
    ,n.YearMonth
    ,'5.Moto[%]' as dim
FROM
 (
    (
    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.mois

UNION

SELECT
    SUM(myNumber) as Indicator
    ,store_lib
    ,Type
    ,'13-Total' as mois
    ,YearMonth
    ,'1.Total' as dim
from mySet
GROUP BY     
    store_lib
    ,Type
    ,YearMonth
-----------------------
UNION
SELECT
    SUM(myNumber) as Indicator
    ,store_lib
    ,Type
    ,'13-Total' as mois
    ,YearMonth
    ,'2.Nb.Car' as dim
from 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 dim
from mySet
    where Type= 'motorcycle'
GROUP BY     
    store_lib
    ,Type
    ,YearMonth
-----------------------
UNION
----------------------- '4.Car[%]' / Total
SELECT
    SUM(nb_car/total) as Indicator
    ,n.store_lib
    ,'' as Type
    ,n.mois
    ,n.YearMonth
    ,'4.Car[%]' as dim
FROM
 (
    (
    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[%]' / Total
SELECT
    SUM(nb_moto/total) as Indicator
    ,n.store_lib
    ,'' as Type
    ,n.mois
    ,n.YearMonth
    ,'5.Moto[%]' as dim
FROM
 (
    (
    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>
 

raoh's picture
2
Joined: Jan 18 2019 - 6:59am
Last seen: 10 months 1 week ago

0 Answers:

No answers yet
Feedback