Count Active Items by Month

[jason.symons]

Hi All,

I am trying to build a report which I think should be a pretty common requirement, but I can't quite work out how to achieve it. What I want is a count of active projects by month, where projects have a start and end date. So, my data is like this:

Name	StartDate	EndDate
Project1	1/01/2016	29/02/2016
Project2	1/02/2016	30/03/2016
Project3	1/03/2016	30/04/2016
Project4	1/06/2016	30/08/2016
Project5	1/07/2016	30/08/2016

And the result I would like is this:

Month	Number of Active Projects
January	1
February	2
March	2
April	1
May	0
June	1
July	2
August	2

Note that it is counting projects that are active in that month i.e. between their start and end dates, not a count of the start dates or end dates within a particular month. Also note that there were no projects active in May but I still want to show May (I think the report would be confusing otherwise).

I am using JasperStudio 6.3.0, publishing to JasperReports Server 6.3.0. The data source is JasperReports server domain. Would be grateful for any thoughts on how to achieve it.

Thanks very much!

Jason

[reportdev]

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  --><!-- 2016-12-21T15:52:31 --><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="dummy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="772106db-4d95-4301-b793-4344b38a1c77">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <import value="org.apache.commons.lang.time.*"/>    <queryString>        <![CDATA[select * from dummy]]>    </queryString>    <field name="NAME" class="java.lang.String"/>    <field name="STARTDATE" class="java.sql.Timestamp"/>    <field name="ENDDATE" class="java.sql.Timestamp"/>    <variable name="January" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Jan", "").length()]]></variableExpression>    </variable>    <variable name="Febraury" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Feb", "").length()]]></variableExpression>    </variable>    <variable name="March" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Mar", "").length()]]></variableExpression>    </variable>    <variable name="April" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Apr", "").length()]]></variableExpression>    </variable>    <variable name="May" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("May", "").length()]]></variableExpression>    </variable>    <variable name="June" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Jun", "").length()]]></variableExpression>    </variable>    <variable name="July" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Jul", "").length()]]></variableExpression>    </variable>    <variable name="August" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Aug", "").length()]]></variableExpression>    </variable>    <variable name="September" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Sep", "").length()]]></variableExpression>    </variable>    <variable name="October" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Oct", "").length()]]></variableExpression>    </variable>    <variable name="November" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Nov", "").length()]]></variableExpression>    </variable>    <variable name="December" class="java.lang.Integer" calculation="Sum">        <variableExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Dec", "").length()]]></variableExpression>    </variable>    <background>        <band splitType="Stretch"/>    </background>    <columnHeader>        <band height="33" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="180" height="30" uuid="eea0aaab-e134-484e-a896-15bc5c4e833a"/>                <text><![CDATA[NAME]]></text>            </staticText>            <staticText>                <reportElement x="180" y="0" width="100" height="30" uuid="e14cedaf-633c-4c8f-9bee-141db2774e67"/>                <text><![CDATA[sTARTDATE]]></text>            </staticText>            <staticText>                <reportElement x="280" y="1" width="100" height="30" uuid="fd619151-ecd1-4daf-8213-4090638f910e"/>                <text><![CDATA[ENDDATE]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="31" splitType="Stretch">            <textField>                <reportElement x="0" y="0" width="180" height="30" uuid="242b10f7-5cef-4a74-934e-0086261467a4"/>                <textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="180" y="0" width="100" height="30" uuid="ba762574-e20f-464f-8f88-333200c7b41a"/>                <textFieldExpression><![CDATA[$F{STARTDATE}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="280" y="1" width="100" height="30" uuid="6ba6919f-c95c-425c-879e-b511423c516a"/>                <textFieldExpression><![CDATA[$F{ENDDATE}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="480" y="0" width="100" height="30" uuid="333aa204-ed9c-4627-8478-9a871c9b7b82"/>                <textFieldExpression><![CDATA[MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :0]]></textFieldExpression>            </textField>            <textField>                <reportElement x="580" y="1" width="100" height="30" uuid="2bb04ca5-7e56-4f6c-8153-c09c77030332"/>                <textFieldExpression><![CDATA[CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).length()-CLEAN(MONTHS($F{STARTDATE},$F{ENDDATE}) == 1 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{ENDDATE},0)) ) :MONTHS($F{STARTDATE},$F{ENDDATE}) == 2 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2)) )  :MONTHS($F{STARTDATE},$F{ENDDATE}) == 3 ?(new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},0)) + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},1))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},2))  + ", " +new SimpleDateFormat("MMM").format(DateUtils.addMonths($F{STARTDATE},3)) )  :"Not found" ).replace("Feb", "").length()]]></textFieldExpression>            </textField>        </band>    </detail>    <summary>        <band height="283">            <textField>                <reportElement x="80" y="29" width="100" height="19" uuid="56c67b6a-e234-41c5-aeae-ff2a21c91a6a"/>                <textFieldExpression><![CDATA[$V{January}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="29" width="80" height="20" uuid="934c850e-37fe-4053-809f-27948930cea3"/>                <text><![CDATA[January]]></text>            </staticText>            <textField>                <reportElement x="80" y="50" width="100" height="19" uuid="39f4177d-4888-42c9-bab9-373b814d35eb"/>                <textFieldExpression><![CDATA[$V{Febraury}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="50" width="80" height="20" uuid="3022385e-889f-470f-a7da-e09084913d73"/>                <text><![CDATA[Febraury]]></text>            </staticText>            <textField>                <reportElement x="80" y="70" width="100" height="19" uuid="face081b-f00b-4999-9e25-074779ded1d9"/>                <textFieldExpression><![CDATA[$V{March}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="70" width="80" height="20" uuid="b3ad8e90-3869-49d3-a9a2-ff6914f81e44"/>                <text><![CDATA[March]]></text>            </staticText>            <textField>                <reportElement x="80" y="90" width="100" height="19" uuid="1d004f46-2113-45b8-b2a2-2375e5213b04"/>                <textFieldExpression><![CDATA[$V{April}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="90" width="80" height="20" uuid="767e576d-e55e-4292-a494-6d769991bc10"/>                <text><![CDATA[April]]></text>            </staticText>            <textField>                <reportElement x="80" y="110" width="100" height="19" uuid="c0256799-43f8-4118-a0a7-d53e3a7f203d"/>                <textFieldExpression><![CDATA[$V{May}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="110" width="80" height="20" uuid="99a35907-c3b1-40bc-9e05-900720818fc9"/>                <text><![CDATA[May]]></text>            </staticText>            <textField>                <reportElement x="80" y="130" width="100" height="19" uuid="c0ba9c5b-2cb8-47a2-bc96-c1cf314f10fd"/>                <textFieldExpression><![CDATA[$V{June}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="130" width="80" height="20" uuid="178c5ac3-5960-4e1c-9921-d61e6b5b025e"/>                <text><![CDATA[June]]></text>            </staticText>            <textField>                <reportElement x="80" y="150" width="100" height="19" uuid="af0fa35f-dc55-4ad7-a37e-96f3a5c726ab"/>                <textFieldExpression><![CDATA[$V{July}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="150" width="80" height="20" uuid="619260bc-9e82-49d1-86c3-ab9150fc49a6"/>                <text><![CDATA[July]]></text>            </staticText>            <textField>                <reportElement x="80" y="170" width="100" height="19" uuid="ab782222-f4b1-4b06-830c-586eb7adb289"/>                <textFieldExpression><![CDATA[$V{August}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="170" width="80" height="20" uuid="b7440859-aae2-4e5f-85b2-7621401604a4"/>                <text><![CDATA[August]]></text>            </staticText>            <textField>                <reportElement x="80" y="190" width="100" height="19" uuid="90a46805-301d-4fb3-8c18-5a369349039c"/>                <textFieldExpression><![CDATA[$V{September}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="190" width="80" height="20" uuid="cb07d1eb-77d0-43f9-8199-354183800c5a"/>                <text><![CDATA[september]]></text>            </staticText>            <textField>                <reportElement x="80" y="210" width="100" height="19" uuid="e3747477-5559-45dd-97a7-a660b30179ed"/>                <textFieldExpression><![CDATA[$V{October}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="210" width="80" height="20" uuid="1501a206-cd24-4989-8b3f-cda00d271b47"/>                <text><![CDATA[October]]></text>            </staticText>            <textField>                <reportElement x="80" y="230" width="100" height="19" uuid="b3ff9717-e995-420b-9c98-edafd38f97e2"/>                <textFieldExpression><![CDATA[$V{November}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="230" width="80" height="20" uuid="d5091a60-777d-478a-98be-0ad5672735e3"/>                <text><![CDATA[November]]></text>            </staticText>            <textField>                <reportElement x="80" y="250" width="100" height="19" uuid="45d11199-7465-4d2c-8bea-6d1107dc7487"/>                <textFieldExpression><![CDATA[$V{December}/3]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="250" width="80" height="20" uuid="949fde09-cbf9-4c10-8461-137368166791"/>                <text><![CDATA[December]]></text>            </staticText>        </band>    </summary></jasperReport>[/code]

Check this jrxml. it might not be accurate enough to your requirement,

but i have tried my level best. Currently it considers upto 4 consequtive months between 2 dates.

if you want more, then add more conditionals to all the variables.

[jason.symons]

Wow, thanks for this Reportddev! and thanks for the quick response.  I tried the jrxml above - it does work!  However, I think its going to struggle with the full range data I'm working with.  Projects could be anywhere from a couple of weeks to several years, so I would have to add an lots and lots of conditionals to the variables.  Sorry, the data I put in my original post was a really simplified example - I should have been more specific.

Is there any way to get the variable to iterate through all the possibilites from startdate up to enddate rather than go through a specific set of conditions?

Hopefully that makes sense - apoligies - I'm fairly new to this!  It really feels like it must be a fairly common requirement - but perhaps its hard to execute.

Thanks

 

Jason

 

 

[reportdev]

dates spanning across years would become complicated i believe.

but its not impossible. in the conditional variables, the year should also be considered and check if its previous / current / next year and then add up.

 

[jason.symons]

Hi

Just following up on where I got to on this one.  I haven't quite got it all working as I would like yet, but have made some progress. In the end I found that I needed to use a reference table of dates in the datasoruce to achieve what I was looking for.  I was hoping to avoid using a table like that but in the end that was the best way. 

I'll post an update when I have a final working version of the report.