jason.symons Posted December 21, 2016 Share Posted December 21, 2016 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:NameStartDateEndDateProject11/01/201629/02/2016Project21/02/201630/03/2016Project31/03/201630/04/2016Project41/06/201630/08/2016Project51/07/201630/08/2016And the result I would like is this:MonthNumber of Active ProjectsJanuary1February2March2April1May0June1July2August2Note 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 More sharing options...
reportdev Posted December 21, 2016 Share Posted December 21, 2016 <?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 More sharing options...
jason.symons Posted December 22, 2016 Author Share Posted December 22, 2016 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 More sharing options...
reportdev Posted December 27, 2016 Share Posted December 27, 2016 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. Link to comment Share on other sites More sharing options...
jason.symons Posted February 1, 2017 Author Share Posted February 1, 2017 HiJust 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 More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now