Jump to content
Changes to the Jaspersoft community edition download ×

Count Active Items by Month


jason.symons

Recommended Posts

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:

NameStartDateEndDate
Project11/01/201629/02/2016
Project21/02/201630/03/2016
Project31/03/201630/04/2016
Project41/06/201630/08/2016
Project51/07/201630/08/2016

And the result I would like is this:

MonthNumber of Active Projects
January1
February2
March2
April1
May0
June1
July2
August2

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

Link to comment
Share on other sites

  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

<?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.

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

  • 1 month later...

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.

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...