Hot questions for Using JasperReports in date

Question:

I want to get a date six month before from present date. The code that I tried is:

SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
Calendar c = Calendar.getInstance();
c.add(Calendar.MONTH, -6);
System.out.println(format.format(c.getTime()));

But I want to reduce this to a single line expression which I want to use in my Jasper report to put in parameter expression.

How can I reduce it to single line expression?


Answer:

wrap this util method into a Utility class build the jar and put it in classpath of your iReport and whenever you are compiling this jrxml

Question:

In the following snipped the property $F is of class java.time.LocalDateTime or java.time.LocalDate.

<textField pattern="EE. dd.MM.yyyy">
    <reportElement...>
    </reportElement>
    <textFieldExpression><![CDATA[$F{theLocalDateTime}]]></textFieldExpression>
</textField>

How can I format this property with textField pattern in jasper reports?


Answer:

To use the pattern attribute in current version of jasper-report for Date/Time object you need a java.util.Date class or one of it's subclasses.

The solution is to convert java.time.LocalDate and java.time.LocalDateTime

Converting to java.util.Date

from java.time.LocalDate

<textField pattern="EE. dd.MM.yyyy">
    <reportElement...>
    </reportElement>
    <textFieldExpression><![CDATA[java.util.Date.from($F{theLocalDate}.atStartOfDay(java.time.ZoneId.systemDefault()).toInstant())]]></textFieldExpression>
</textField>

from java.time.LocalDateTime

<textField pattern="EE. dd.MM.yyyy">
    <reportElement...>
    </reportElement>
    <textFieldExpression><![CDATA[java.util.Date.from($F{theLocalDateTime}.atZone(java.time.ZoneId.systemDefault()).toInstant())]]></textFieldExpression>
</textField>
Converting to java.sql.Timestamp

from java.time.LocalDate

<textField pattern="EE. dd.MM.yyyy">
    <reportElement...>
    </reportElement>
    <textFieldExpression><![CDATA[java.sql.Timestamp.valueOf($F{theLocalDate}.atStartOfDay())]]></textFieldExpression>
</textField>

from java.time.LocalDateTime

<textField pattern="EE. dd.MM.yyyy">
    <reportElement...>
    </reportElement>
    <textFieldExpression><![CDATA[java.sql.Timestamp.valueOf($F{theLocalDateTime})]]></textFieldExpression>
</textField>

Note: Applying pattern is always preferable solution, specially when exporting to excel since correct class will be passed to poi (hence excel will recognize column as a date and apply same formatting as in pattern)

Question:

How can I create a single jasper report JRExpression that visualize the difference between two java.util.Date in format yy year(s) month(s) dd day(s), hh hour(s), mm minute(s), ss second(s)

java.util.Date startDate
java.util.Date endDate

A JRExpression is a single line where variable declaration is not allowed, however you may use conditional statements using syntax boolean ? yes:no, for you who are not familiar imagine one line of System.out.println();

Example of desired output (if you have a nice solution remove description of unit when not present and consider singular/plural but this is not necessary if it's a serie of if statements):

2 years, 8 months, 12 days, 2 hours, 53 minutes, 10 s

1 hour, 1 minute

both Feb 2- Mar 4 and Mar 4 - April 6 are "1 month, 2 days", daylight savings however can be ignored - thanks @Affe

Additional requirements:

  • Only jasper report dependencies may be used (joda is not included).
  • Preferable jdk 1.7 or less (1.8 is accepted if this is only solution)

There is no need to format the answer as jasper report expression it can be a simple System.out.println code (I'm happy to edit your answer later to also add the jasper report expression code). Example

((endDate.getTime()-startDate.getTime()) / (60 * 60 * 1000)) % 24 + " hour(s), " +  
((endDate.getTime()-startDate.getTime()) / (60 * 1000)) % 60 + " minute(s)"

What have I tried:

I answer multiple question in the jasper report section of SO, and this question is common in report generation. I would prefer a good answer from the java section that I can link rather then passing my code on this issue (that I would only know to solve partially as example)

This is an example on question in jasper-report: Calculating Time and Date difference

Some reference code:

Calculate date/time difference in java

How to find the duration of difference between two dates in java?

For you that are familiar to jasper report I don't want to use the variable declaration since this would invalidate the solution if user need to use it on parameters.


Answer:

The Calendar API cannot be directly be used for this problem: every operation would require multiple lines since the interesting methods are void returning and can't be chained.

This is a very big stretch, but, as listed in the dependencies of JasperReports, there is org.codehaus.castor:castor-xml:1.3.3 which depends itself on commons-lang:commons-lang:2.6. We therefore can make use of the DurationFormatUtils.formatPeriod(startMillis, endMillis, format) method, which is present in commons-lang. The format String to use here would be

"y' years 'M' months 'd' days 'H' hours 'm' minutes 's' seconds'"

which would print the wanted String. Care must still be taken: this will include 0s (like "0 months") and will also have incorrect pluralization (like "1 months").

  • We can use the regular expression "(?<!\\d)0 (\\w+) ?" to remove all the 0s for the String. This regex matches any 0, not preceded by a digit (we don't want to match 10 for example), followed by one or more word characters and optionally followed by a space.
  • Then, we can use the regular expression "(?<!\\d)1 (\\w+)s" to match every occurence of "1 ...s" and replace it with "1 ..." to have proper pluralization. This regular expression matches any 1, not preceded by a digit, followed by one or more word characters (captured in a group) ending with an s; it would be replaced with "1 $1", i.e. 1 followed by the value captured.

Example:

System.out.println(
    org.apache.commons.lang.time.DurationFormatUtils.formatPeriod(
            startDate.getTime(), 
            endDate.getTime(), 
            "y' years 'M' months 'd' days 'H' hours 'm' minutes 's' seconds'"
    )
    .replaceAll("(?<!\\d)0 (\\w+) ?", "")
    .replaceAll("(?<!\\d)1 (\\w+)s", "1 $1")
);

All this can be done with Java 7 or lower.

In a JasperReports, this would be an example:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.1.final using JasperReports Library version 6.2.1 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports jasperreports.sourceforge.net/…" name="Blank_A4" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="f067f2c4-395f-4669-9fda-4fe81cc59227">
  <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
  <parameter name="dateStart" class="java.util.Date" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.util.Date(1)]]></defaultValueExpression>
  </parameter>
  <parameter name="dateEnd" class="java.util.Date" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.util.Date()]]></defaultValueExpression>
  </parameter>
  <queryString><![CDATA[]]></queryString>
  <title>
    <band height="43" splitType="Stretch">
      <textField>
        <reportElement x="0" y="0" width="560" height="30" uuid="cc03531c-2983-4f9a-9619-2826ed92760e"/>
        <textFieldExpression><![CDATA[org.apache.commons.lang.time.DurationFormatUtils.formatPeriod($P{dateStart}.getTime(),$P{dateEnd}.getTime(),"y' years 'M' months 'd' days 'H' hours 'm' minutes 's' seconds'").replaceAll("(?<!\\d)0 (\\w+) ?", "").replaceAll("(?<!\\d)1 (\\w+)s", "1 $1")]]></textFieldExpression>
      </textField>
    </band>
  </title>
</jasperReport>

With the output being:


If the above looks too fragile (because of the explicit dependency towards commons-lang that could not be there for all JasperReports version), there is another possible solution using the Java Time API introduced in Java 8.

This is horrible (I don't think there is a simpler way), but the output is exactly the same as above, where start and end are both LocalDateTime objects:

System.out.println((
    ChronoUnit.YEARS.between(start, end) + " years " +
    ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end) + " months " +
    ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end) + " days " +
    ChronoUnit.HOURS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)).plusDays(ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end)), end) + " hours " +
    ChronoUnit.MINUTES.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)).plusDays(ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end)).plusHours(ChronoUnit.HOURS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)).plusDays(ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end)), end)), end) + " minutes " +
    ChronoUnit.SECONDS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)).plusDays(ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end)).plusHours(ChronoUnit.HOURS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)).plusDays(ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end)), end)).plusMinutes(ChronoUnit.MINUTES.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)).plusDays(ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end)).plusHours(ChronoUnit.HOURS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)).plusDays(ChronoUnit.DAYS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)).plusMonths(ChronoUnit.MONTHS.between(start.plusYears(ChronoUnit.YEARS.between(start, end)), end)), end)), end)), end)), end) + " seconds"
    )
    .replaceAll("(?<!\\d)0 (\\w+) ?", "")
    .replaceAll("(?<!\\d)1 (\\w+)s", "1 $1")
);

Question:

I have an application developed in NetBeans 8.1 with iReport and JasperReport version 5.5.0 plugin. In my application, I have a jasper report with a JRBeanCollectionDataSource.

LocalDate dateFrom = jDateChooser1.getDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
LocalDate dateUntil = jDateChooser2.getDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
List<Registru> regs = db.getRegistreFrom(dateFrom, dateUntil);

Map<String, Object> params = new HashMap<>();
String absolutePath = "....\\rapoarte\\";
params.put("SUBREPORT_DIR", absolutePath);
InputStream in = getClass().getResourceAsStream("/rapoarte/registreListare.jasper");
JasperPrint jp = JasperFillManager.fillReport(in, params, new JRBeanCollectionDataSource(regs,false));

// ... code here

JRBeanCollectionDataSource takes a parameter of List of Report class returned from the database

public class Registru {
   private int id;
   private int nrReg;
   private LocalDate date;
   // ... getters and setters
}

The problem is that in the report the fields are :

<field name="id" class="java.lang.Integer"/>
<field name="nrReg" class="java.lang.Integer"/>
<field name="date" class="java.time.LocalDate"/>

but when I compile it, it gives me an error

java.time.LocalDate cannot be resolved to a type

It works only if I put <field name="date class="java.lang.Object"/> but I cannot format the date.

I want to format the date of pattern "dd-MM-yyyy". Do I have to add something to the jasper's classpath?


Answer:

This problem is related to the JTD compiler (compiler for the jrxml) that you are using.

You will need a recent version of Eclipse Java Compilers (ecj) to use java-8, check the jasper-distribution.

To use pattern on the java.time.LocalDate or java.time.LocalDateTime see this question for more information

Question:

Purchase.jsp

<form action="view2.jsp" method="post">
<select name="category">
<option value="">Make a selection</option>
<option value="company">company</option>
<option value="institution">institution</option>
<option value="hospital">hospital</option>
<option value="Others">Others</option>
</select>
<select name="status">
<option value="">Select Status</option>
<option value="close">Close</option>
<option value="open">Open</option>
<option value="pending">Pending</option>
</select>
&nbsp &nbsp &nbsp &nbsp
<select name="day">
                            <option>DD</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
                            <option>13</option>
                            <option>14</option>
                            <option>15</option>
                            <option>16</option>
                            <option>17</option>
                            <option>18</option>
                            <option>19</option>
                            <option>20</option>
                            <option>21</option>
                            <option>22</option>
                            <option>23</option>
                            <option>24</option>
                            <option>25</option>
                            <option>26</option>
                            <option>27</option>
                            <option>28</option>
                            <option>29</option>
                            <option>30</option>
                            <option>31</option>
</select>
<select name="month">
<option>MM</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
</select>
<select name="year">
 <option>YYYY</option>
                            <option>2012</option>
                            <option>2013</option>
                            <option>2014</option>
                            <option>2015</option>
                            <option>2016</option>
                            <option>2017</option>
                            <option>2018</option>
                            <option>2019</option>
                            <option>2020</option>
                            <option>2021</option>
                            <option>2022</option>
                            <option>2023</option>
                            <option>2024</option>
                            <option>2025</option>
                            <option>2026</option>
</select>
&nbsp &nbsp &nbsp &nbsp &nbsp
<select name="day1">
                            <option>DD</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
                            <option>13</option>
                            <option>14</option>
                            <option>15</option>
                            <option>16</option>
                            <option>17</option>
                            <option>18</option>
                            <option>19</option>
                            <option>20</option>
                            <option>21</option>
                            <option>22</option>
                            <option>23</option>
                            <option>24</option>
                            <option>25</option>
                            <option>26</option>
                            <option>27</option>
                            <option>28</option>
                            <option>29</option>
                            <option>30</option>
                            <option>31</option>
</select>
<select name="month1">
                            <option>MM</option>
                            <option>01</option>
                            <option>02</option>
                            <option>03</option>
                            <option>04</option>
                            <option>05</option>
                            <option>06</option>
                            <option>07</option>
                            <option>08</option>
                            <option>09</option>
                            <option>10</option>
                            <option>11</option>
                            <option>12</option>
</select>
<select name="year1">
                            <option>year</option>
                            <option>2012</option>
                            <option>2013</option>
                            <option>2014</option>
                            <option>2015</option>
                            <option>2016</option>
                            <option>2017</option>
                            <option>2018</option>
                            <option>2019</option>
                            <option>2020</option>
                            <option>2021</option>
                            <option>2022</option>
                            <option>2023</option>
                            <option>2024</option>
                            <option>2025</option>
                            <option>2026</option>
</select>
<input type="submit" value="search"/>
</form>

view2.jsp

<script type="text/javascript">

    function setAction(nPage){

        document.forms[0].action = nPage;
    }
</script>
<body>
<%
String category=request.getParameter("category");
session.setAttribute("cat",category);
String status=request.getParameter("status");
session.setAttribute("sta",status);
String day=request.getParameter("day");
session.setAttribute("da",day);
String month=request.getParameter("month");
session.setAttribute("mon",month);
String year=request.getParameter("year");
session.setAttribute("yea",year);
String day1=request.getParameter("day1");
session.setAttribute("da1",day1);
String month1=request.getParameter("month1");
session.setAttribute("mon1",month1);
String year1=request.getParameter("year1");
session.setAttribute("yea1",year1);
%>
<select onchange="setAction(this.value)">
<option value=''> Make a selection </option>
<option value='PDF_LEAD.jsp'> PDF</option>
<option value='XLS_LEAD.jsp'> XLS </option>
<option value='DOC_LEAD.jsp'> DOC </option>
<option value='XLSX_LEAD.jsp'> XLSX </option>
</select>
<input type="submit" value="search"/>
</body>

PDF_LEAD.jsp

<body>
<% 
Connection conn = null;
String cate=(String)session.getAttribute("cat");
String stat=(String)session.getAttribute("sta");
String dayy=(String)session.getAttribute("da");
String monthh=(String)session.getAttribute("mon");
String yearr=(String)session.getAttribute("yea");
String dayy1=(String)session.getAttribute("da1");
String monthh1=(String)session.getAttribute("mon1");
String yearr1=(String)session.getAttribute("yea1");
try 
{ 
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/marketing_database","root","root");
    String jrxmlFile ="D:/dev/tools/jasper files/report10.jrxml";
    InputStream input = new FileInputStream(new File(jrxmlFile));
    JasperDesign jasperDesign = JRXmlLoader.load(input);

    System.out.println("Compiling Report Designs");
    JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);

    System.out.println("Creating JasperPrint Object");
    HashMap<String,Object> map = new HashMap<String,Object>();
    map.put("complan",comp);
    map.put("search",sear);
    map.put("category",cate);
    map.put("status",stat);
    map.put("day",dayy);
    map.put("month",monthh);
    map.put("year",yearr);
    map.put("day1",dayy1);
    map.put("month1",monthh1);
    map.put("year1",yearr1);
    JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport,map,conn);
    byte bytes[] = new byte[10000]; 
    JRPdfExporter exporter = new JRPdfExporter();
    ByteArrayOutputStream PDFStream = new ByteArrayOutputStream(); 
    exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint); 
    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, PDFStream); 
    exporter.exportReport(); 
    System.out.println("Size of byte array:"+PDFStream.size()); 
    bytes = PDFStream.toByteArray(); 
    response.setContentType("application/pdf"); 
    System.out.println("After JasperPrint = 1"); 
    response.setContentLength(bytes.length); 
    System.out.println("After JasperPrint = 2"); 
    PDFStream.close(); 
    System.out.println("After JasperPrint = 3"); 

    OutputStream outputStream = response.getOutputStream(); 
    System.out.println("After JasperPrint = 4"); 
    outputStream.write(bytes, 0, bytes.length); 
    outputStream.flush(); 
    outputStream.close(); 

}
catch(Exception e) 
{e.printStackTrace();} 

%>
</body>

report10.jrxml

<parameter name="category" class="java.lang.String">
        <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <parameter name="search" class="java.lang.String">
        <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <parameter name="status" class="java.lang.String">
        <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <queryString>
        <![CDATA[select * from lead where Status='$P!{status}' and category ='$P!{category}' and Company_Name like '$P!{search}%']]>
    </queryString>

Here i have "Start Date" and "End Date".I have passed the "Category" parameter and "status" parameter but how to pass "Dates" parameter from jsp to jasper report?? I have used select tag names as "day","month","year" for "Start Date" and have used select tag names as "day1","month1","year1" for "End Date" as (mentioned in Purchase.jsp)below.My second question is, in MYSQL database i didnt have a column named as "Start Date" and "End Date". The columns i have in my database are

(id,Name,Email,Company_Name,Contact_Person,Address,Phone,Company_Email,Review,Lead_Date,Lead_Details,Lead_Value,Followup_Date,Status,Category).

So how to put "Start Date" and "End Date" in my "where" clause of MYSQL Database


Answer:

The answer above would solve your problem related to the question (in fact I upvote it), but since I see that you struggle (2nd question) generating the sql inside of jasper report -I will suggest another metod

Generating the query in jsp and passing the whole query to jasper report.

To do this you need to use concat metodo that can be subject to "sql injection" but i suggest this anyway since:

  1. Simplifing the task
  2. You are already using it in jasper report, hence $P!{} not $P
  3. The damage someone could do is only viewing some records in your report.

The only thing I do suggest is that you escape the ' char to not break query if some user search on es goo'gle. This you can do by this simple code:

userInput.replaceAll("\\'", "''")

So doing this within your jsp (PDF_LEAD.jsp) you can create your sql something like this

String sql = "select * from lead where Status='" + stat.replaceAll("\\'", "''") + "'";
if (sear!=null && searc.trim().length()>0){
  sql+=" AND Company_Name like '" + searc.replaceAll("\\'", "''") + "%'";
}

etc etc arriving to your date after your if you add something like this.

sql+="DATE(the_name_of_you_date_column)>='" + year + "-" + month + "-" + day + "'";

Note the MySQL date format is yyyy-MM-dd

Once you finshed creating your query ouput it to the System.out.println(sql);, that way you can test your query in MySQL Workbench (without the report), it will help you to understand if something is wrong...

The last thing you need to do is pass the query to jasper report:

 HashMap<String,Object> map = new HashMap<String,Object>();
 map.put("sql",sql);

Add the parameter to your .jrxml

<parameter name="sql" class="java.lang.String"/>

And change the query string to:

<queryString>
    <![CDATA[$P!{sql}]]>
</queryString>

Hope this will help and narrow down your problem on how to create the correct sql. (Don't copy and past my code check that it's correct I have written it by not knowing exactly your parameters and checking syntax)

Question:

My situation is when I'm not able to retrieve anything with my query, that is found inside the jasper file, a report is still generated.

I'm using this line of code to generate report.

ByteArrayOutputStream baos = new ByteArrayOutputStream();

JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReportStream, params, jdbcConnection);
long start = System.currentTimeMillis();
Exporter jrExporter = getJRExporter(format, jasperPrint, baos);
jrExporter.exportReport();
bytes = baos.toByteArray();

Is there a way to use the exporter object to detect if my report is empty? I have tried to validate null using the byteArray then converted to String when producing a pdf format.

However I cannot easily check if byteArray is null since it produces this output despite returning no results from my query:

%PDF-1.4
%����
1 0 obj <</Length 45/Filter/FlateDecode>>stream
x�3P0T�5T0P0�4�ɹ\�\N!\�f
���
!)\�!\�\\   

Answer:

Set on jasperReport tag whenNoDataType="NoPages" or remove whenNoDataType attribute (default is "NoPages")

Check how many pages there are in the JasperPrint, if 0 you have NoData

    JasperPrint print = JasperFillManager.fillReport(jasperReportStream, params, jdbcConnection);
    List<JRPrintPage> pages = print.getPages();
    if (pages.size()==0){
            //No pages, do not export instead do other stuff
    }

Question:

I need to implement a two date type axis with jasper-reports.

The time series chart allows a date on x-axis and a number value on y-axis, is there a way to implements two date type axis where both y-axis and x-axis is of date type?


Answer:

To set y-axis as a Date axis you need to.

1. Convert the Date in the valueExpression to a java.lang.Number, since valueExpression does only allow this class (or a sub class)

Instead of passing a java.util.Date in the valueExpression pass the time in milliseconds java.lang.Long by calling Date.getTime()

<valueExpression><![CDATA[$F{myDateOnYAxis}.getTime()]]></valueExpression>

2. Add a JRChartCustomizer that changes the number axis to a date axis with relative formatter

public class MyChartCustomizer implements JRChartCustomizer {

    @Override
    public void customize(JFreeChart jfchart, JRChart jrchart) {
        XYPlot plot = (XYPlot) jfchart.getPlot(); //get the plot

        //Create the new date axis for y
        DateAxis yDateAxis = new DateAxis();
        //Set desired time format
        DateFormat dateFormat = new SimpleDateFormat("MMM - yyyy"); 
        yDateAxis.setDateFormatOverride(dateFormat); 
        //Add your own Tickunit if you like (you can do with out also, comment out the below line and let JFreeChart decided)
        yDateAxis.setTickUnit(new DateTickUnit(DateTickUnitType.MONTH,3));

        //Set the new y-axis to the plot
        plot.setRangeAxis(yDateAxis);
    }
}

For how to add JRChartCustomizer to your design (jrxml) see Sample reference

Result

*Some random dates formatted in Italian

Example jrxml used to generate graph with a csv datasource

<?xml version="1.0" encoding="UTF-8"?>
<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="Test2DateGraph" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="2347c131-1884-430a-b77f-59f08f896c8a">
	<property name="com.jaspersoft.studio.data.defaultdataadapter" value="Dates"/>
	<queryString language="csv">
		<![CDATA[]]>
	</queryString>
	<field name="Date1" class="java.util.Date"/>
	<field name="Date2" class="java.util.Date"/>
	<summary>
		<band height="353">
			<timeSeriesChart>
				<chart evaluationTime="Report" customizerClass="MyChartCustomizer">
					<reportElement x="10" y="50" width="530" height="256" uuid="4a93e72e-251b-4026-bb11-edc26ecd6599"/>
					<chartTitle/>
					<chartSubtitle/>
					<chartLegend/>
				</chart>
				<timeSeriesDataset>
					<timeSeries>
						<seriesExpression><![CDATA["SERIES 1"]]></seriesExpression>
						<timePeriodExpression><![CDATA[$F{Date2}]]></timePeriodExpression>
						<valueExpression><![CDATA[$F{Date2}.getTime()]]></valueExpression>
					</timeSeries>
				</timeSeriesDataset>
				<timeSeriesPlot>
					<plot/>
					<timeAxisFormat>
						<axisFormat/>
					</timeAxisFormat>
					<valueAxisFormat>
						<axisFormat/>
					</valueAxisFormat>
				</timeSeriesPlot>
			</timeSeriesChart>
		</band>
	</summary>
</jasperReport>

Question:

I have a Java web project in the Jboss 7 in which I use Jasper reports. I modified the layout of jasper file tested on localhost this all right. I deploy the server but it still carries the jasper file to the previous layout. Jasper has not updated at all. Already cleaned the folders in tmp folder, I stopped and started the server, changed the way in which was saved jasper and nothing works.

public class UtilRelatorios {

public static void imprimeRelatorio(String relatorioNome,
        HashMap parametros) throws IOException, JRException {
        FacesContext fc = FacesContext.getCurrentInstance();
        ServletContext context = (ServletContext) fc.getExternalContext().getContext();
        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        JasperPrint jasperPrint = 
                JasperFillManager.fillReport(
                        context.getRealPath("/relatorios/jasper")+ File.separator+relatorioNome+".jasper",
                        parametros);     
        byte[] b = null;

        try {
            b = JasperExportManager.exportReportToPdf(jasperPrint);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }    

            if (b != null && b.length > 0) {
                // Envia o relatório em formato PDF para o browser
                response.setContentType("application/pdf");
                int codigo = (int) (Math.random()*1000);
                response.setHeader("Content-disposition","inline);filename=relatorio_"+codigo+".pdf");
                response.setContentLength(b.length);
                ServletOutputStream ouputStream = response.getOutputStream();
                ouputStream.write(b, 0, b.length);
                ouputStream.flush();
                ouputStream.close();
            }   
 }

}


Answer:

.jrxml and .jasper

You have surely edited your .jrxml, but have you complied it with success to a new .jasper (the complied version)

and is it the .jasper file that you have updated?

Note: in your code you are not compiling the .jrxml but using directly the complied version .jasper, so you need to overwrite this.

how-do-i-compile-jrxml-to-get-jasper

what-is-the-difference-between-jasperreport-formats

If this does not work, output the context.getRealPath("/relatorios/jasper")+ File.separator+relatorioNome+".jasper" to understand which file you need to replace.

Question:

I have created several variables with time-expressions, to create time-based reports:

// First of month (Returns 01/09/2017)
 new SimpleDateFormat("MM/dd/yyyy").format(java.util.Date.parse("01/" + MONTH(NOW( )) + "/" + YEAR(NOW())))

// First of previous month (Returns 01/08/2017)
 new SimpleDateFormat("MM/dd/yyyy").format(java.util.Date.parse("01/" + (MONTH(NOW( )) - 1) + "/" + YEAR(NOW())))

// Last of month (Returns 30/09/2017)
 new SimpleDateFormat("MM/dd/yyyy").format(java.util.Date.parse(MONTH(NOW( )) + "/" + DAYSINMONTH(NOW())+ "/" + YEAR(NOW())))

I am now struggling with getting the last day of the previous month. I tried something like below, but of course this does not work properly, as not all months have the same amount of days.

// Last of previous month (Returns 30/08/2017)
 new SimpleDateFormat("MM/dd/yyyy").format(java.util.Date.parse(
  (MONTH(NOW( )) - 1)
  + "/" +
  DAYSINMONTH(NOW())
  + "/" +
  YEAR(NOW())
 ))

Do you know of a way to retrieve the last (day) of the previous month?

reference: http://community.jaspersoft.com/questions/843248/last-day-current-month


Answer:

I would probably have solved this creating a static class in java with a method that takes the date as parameter and then use the Calendar api or similar to return to the desired date, this is how to do it in java Calendar - Get last day of previous month.

However, lets have fun and use only jasper-reports

Since we can't use Calendar api (it returns void) we need to fall back again on the org.apache.commons.lang.time.DateUtils as my friend Tunaki taught me.

Using DateUtils we can call first truncate with month (remove days) and then we addDays -1 (-1 day if we are at first date of month it will give us the last date of previous month)

DateUtils.addDays(DateUtils.truncate(myDate, Calendar.MONTH),-1);
jrxml example
<?xml version="1.0" encoding="UTF-8"?>
<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="DateUtil" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="f288086f-db4e-451f-bf32-e1cce6311a27">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <import value="java.util.Calendar"/>
    <import value="org.apache.commons.lang.time.DateUtils"/>
    <parameter name="date" class="java.util.Date">
        <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <title>
        <band height="20" splitType="Stretch">
            <textField pattern="MM/dd/yyyy">
                <reportElement x="0" y="0" width="220" height="20" uuid="48878c52-5527-4784-a74a-e2d8df65cc55"/>
                <textFieldExpression><![CDATA[DateUtils.addDays(DateUtils.truncate($P{date}, Calendar.MONTH),-1)]]></textFieldExpression>
            </textField>
        </band>
    </title>
</jasperReport>
Output (the day I passed this answer)

Design note: You should use pattern to format the Date as I do in my example, do not use SimpleDateFormat in expression, using pattern will give you correct object if you export for example to excel.

Question:

I am currently writing a report to calculate the TotalTimeDifference between two date variables in iReport.

The two variables that I am comparing are MO_DATECREATED and MO_DATECOMPLETED and I am trying to calculate the time difference only.

I have tried setting up a variable that does a simple subtraction of the two variables- and of course that has not worked at all.

I will attach photoes of what I currently have but I am looking for the way to compare the two variables (which contains date/time) and printing out a variables with the difference in time.

Example: If the MO was started at 1/2/15 12:55pm and completed at 1/3/15 1:55pm i want to print the time difference, or how long it took, as 25 hours

How can I do this in iReport? Thank you for helping out a newbie!

EDIT After answer, I would like to the show days to:


Answer:

You do not need variables for this, use directly the textFieldExpression

If the fields MO_DATECREATED and MO_DATECOMPLETED are declared:

as java.lang.Date

<field name="MO_DATECREATED" class="java.lang.Date">
    <fieldDescription><![CDATA[]]></fieldDescription>
</field>

The textFieldExpression would be ($F{MO_DATECOMPLETED}.getTime()-$F{MO_DATECREATED}.getTime())/(1000*60*60) + " hours".

Hey thats java exactly so to understand what it does check out this: How to calculate time difference in java?

as java.lang.String

<field name="MO_DATECREATED" class="java.lang.String">
    <fieldDescription><![CDATA[]]></fieldDescription>
</field>

We need to parse them to Date object's first.. your pattern is mm/dd/yy hh:mm a

(new java.text.SimpleDateFormat("mm/dd/yy hh:mm a").parse($F{MO_DATECOMPLETED}).getTime()-new java.text.SimpleDateFormat("mm/dd/yy hh:mm a").parse($F{MO_DATECREATED}).getTime())/(1000*60*60) + " hours"

Considering that they maybe null we better add a printWhenExpression as well

Complete result

<textField>
     <reportElement x="0" y="0" width="100" height="20" uuid="eac93a84-7901-4205-b09c-556d48dc05e1">
        <printWhenExpression><![CDATA[new java.lang.Boolean($F{MO_DATECREATED}!=null && $F{MO_DATECOMPLETED}!=null)]]></printWhenExpression>
    </reportElement>
    <textFieldExpression><![CDATA[(new java.text.SimpleDateFormat("mm/dd/yy hh:mm a").parse($F{MO_DATECOMPLETED}).getTime()-new java.text.SimpleDateFormat("mm/dd/yy hh:mm a").parse($F{MO_DATECREATED}).getTime())/(1000*60*60) + " hours"]]></textFieldExpression>
</textField>

No doubt that it is better that they are java.lang.Date object, both the report will fill faster, no risk for parsing error and you can export correctly to excel ecc. To format a java.lang.Date object as you wish just use the pattern property.

EDIT: Users has opted for the java.util.Date and asked how he can display also minutes, for this I have created a general question on How to create a single expression displaying time difference between two Date's as years, months, days, hours, minutes, seconds and it is now answered

This was the temporary solution

<textField>
     <reportElement x="0" y="0" width="100" height="20" uuid="eac93a84-7901-4205-b09c-556d48dc05e1">
        <printWhenExpression><![CDATA[new java.lang.Boolean($F{MO_DATECREATED}!=null && $F{MO_DATECOMPLETED}!=null)]]></printWhenExpression>
    </reportElement>
    <textFieldExpression><![CDATA[($F{MO_DATECOMPLETED}.getTime()-$F{MO_DATECREATED}.getTime()) / (24* 60 * 60 * 1000)  + " days " +($F{MO_DATECOMPLETED}.getTime()-$F{MO_DATECREATED}.getTime()) / (60 * 60 * 1000) % 24 + " hours " +  ($F{MO_DATECOMPLETED}.getTime()-$F{MO_DATECREATED}.getTime()) / (60 * 1000) % 60 + " minutes"]]></textFieldExpression>
</textField>

Question:

I am trying to pass start & end date as parameter to jasper report but it is not working properly. I am getting a blank PDF. When I hardcode start & end date in the SQL query itself, I am getting right output in PDF.

Using Mysql, eclipse luna, jre1.7, jaspersoft plugin & all required jars are in Lib folder.

My Servlet code -

try {
    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection ("jdbc:mysql://localhost:3306/db", "root", "password");
    ServletOutputStream servletOutputStream =response.getOutputStream();
    InputStream reportStream =getServletConfig().getServletContext().getResourceAsStream("Blank_A4_Table_Based.jasper");
    SimpleDateFormat eformat = new SimpleDateFormat("yyyy-MM-dd");
    Date sDate = eformat.parse("2015-08-25");
    String sdate=eformat.format(sDate );
    System.out.println(sdate);
    Date eDate = eformat.parse("2015-08-26");
    String edate=eformat.format(eDate );
    System.out.println(edate);
    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("sdate",sDate);
    parameters.put("edate",eDate);
    JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, parameters, connection);
    connection.close();
    response.setContentType("application/pdf");
    servletOutputStream.flush();
    servletOutputStream.close();
} catch (Exception e) {
    // display stack trace in the browser
    StringWriter stringWriter = new StringWriter();
    PrintWriter printWriter = new PrintWriter(stringWriter);
    e.printStackTrace(printWriter);
    response.setContentType("text/plain");
    response.getOutputStream().print(stringWriter.toString());
}

Jasper report JRXML -

<parameter name="sdate" class="java.util.Date">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<parameter name="edate" class="java.util.Date">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>                               
<queryString>                                               
    <![CDATA[SELECT id, session_reason FROM session_details WHERE created BETWEEN $P{sdate} AND $P{edate})]]>
</queryString>

Answer:

EDIT: OP has changed question in orign was passing a String to jasper now its a Date..

You are using $P in query so jasper report tries do generate prepared statment inserting your variables.

Solution 1: Change the parameter's to the correct object (java.sql.Timestamp)

<parameter name="sdate" class="java.sql.Timestamp">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>

and pass a java.sql.Timestamp object

 parameters.put("sdate",new java.sql.Timestamp(sDate.getTime()); 

Solution 2: Use query substitution and not prepared statmentet:

change in your jrxml:

<queryString>                                               
<![CDATA[SELECT id, session_reason FROM session_details WHERE created BETWEEN '$P!{sdate}' AND '$!P{edate}']]>
</queryString>

pass as parameter the String value's

parameters.put("sdate",sdate); //The string

and setup the jrxml parameter's as String

 <parameter name="sdate" class="java.lang.String">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>

The key of this code is $P!{} see the !, basically is does string substitution so I also added the date qualifier ' ' since you are using mysql.

Some other general notes (even if you say it works without parameters):

  1. Normally the driver needs to be registred...

    Driver driver = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance();
    DriverManager.registerDriver(driver);
    
  2. There seems to be a ) to much in the query

  3. response.setContentType("application/pdf"); before you push report to stream

  4. Optional, use try,catch,finally {//close the connection.. }

Question:

I have a report with between date parameters on derby database and I have got below message when sending dates through two text fields to run i to got below quote error msg.

Exception in thread "AWT-EventQueue-0" java.lang.ClassCastException: java.lang.String cannot be cast to java.util.Date at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setDate(JRJdbcQueryExecuter.java:615)

My code to call jasper report is here:

public void runREport() {
   try {     
       Connection con1= null;

       String Op=jTsubmain_no.getText();
       int val = Integer.parseInt((String) Op.trim());
       Map param1 = new HashMap();
       param1.put("sub_no",val);
       param1.put("fdate",jTfdate.getText());
       param1.put("ldate",jTldate.getText());

       try {
              con1= DriverManager.getConnection("jdbc:derby://localhost:1527//accountsdb","accounts","accounts");
       } catch (SQLException ex) {
              Logger.getLogger(reports.class.getName()).log(Level.SEVERE, null, ex);
       }

       String jrxmlFileName = "C:/Users/hosam/Documents/NetBeansProjects/Accsys/src/accsys/report2.jrxml";
      JasperDesign jasperDesign = JRXmlLoader.load(jrxmlFileName);
      JasperReport jasperReport =JasperCompileManager.compileReport(jasperDesign);
      JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, param1, con1);
      JasperViewer.viewReport(jasperPrint); 
   } catch (JRException ex) {
           JOptionPane.showMessageDialog( null,  ex);
   }    
}

Answer:

You have a different class between parameter passed and definition in jrxml.

jTfdate.getText() is returning an object of type java.lang.String

in jrxml you have this definition of parameter

<parameter name="fdate" class="java.util.Date">
    <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
</parameter>

Solution, pass a java.util.Date object in your parameter map.

Example

param1.put("fdate",new SimpleDateFormat("yourPattern").parse(jTfdate.getText()));

Where the pattern, "yourPattern" should correspond to the text parsed see SimpleDateFormat pattern's.

How to catch the ParseException and set default value, I leave to you.

Question:

I am trying to add 1 day to a given parameter date.

In Jaspersoft Studio I have the following parameter

$P{datestart}
Class: java.sql.Date
Default value expression: new Date()

$V{datev}
Class: java.sql.Date
Expression: new java.sql.Date($P{datestart}.getTime() + 24*60*60*1000)

Then I use $V{datev} in the report.

Output error:

Error evaluating expression for source text: 
new java.sql.Date($P{datestart}.getTime() + 24*60*60*1000)  
java.util.Date cannot be cast to java.sql.Date

I tried different expressions but with no luck

Change $v{datev} to new java.util.Date($P{datestart}.getTime() + 24*60*60*1000).
Change $P{datestart} to: new SimpleDateFormat("dd-MM-yyyy").format(new Date())

Does anybody see what I am doing wrong?


Answer:

Looks like the definition of $P{datestart} parameter is wrong.

The expression new java.sql.Date($P{datestart}.getTime() + 24*60*60*1000) is valid if the datestart parameter has java.util.Date or java.sql.Date type. As you probably know, the java.sql.Date class is a subclass of java.util.Date

This Java code is working well:

java.util.Date date = new java.util.Date();
new java.sql.Date(date.getTime() + 24*60*60*1000);

java.sql.Date sqlDate = new java.sql.Date(date.getTime());
new java.sql.Date(sqlDate.getTime() + 24*60*60*1000);

Try to change the type of datestart or even of both (parameter and variable).

At least you need to fix initialization of datestart parameter.

There is no java.sql.Date() (without parameters) constructor. Looks like you are using expression which has analogue of Java code like this:

java.sql.Date sqlDate = new java.util.Date(); // not even compile. Got incompatible types: java.util.Date cannot be converted to java.sql.Date

This declaration will be valid for $P{datestart} of java.sql.Date:

<parameter name="datestart" class="java.sql.Date">
    <defaultValueExpression><![CDATA[new java.sql.Date(new java.util.Date().getTime())]]></defaultValueExpression>
</parameter>       

Question:

I want to show monthly ticks on a DateTime X-axis. I have achieved this using the below code.

DateAxis dateAxis = (DateAxis)chart.getXYPlot().getDomainAxis();
DateTickUnit unit = new DateTickUnit(DateTickUnit.MONTH,1);
dateAxis.setTickUnit(unit);

Now I want to show tick labels only for a particular month (say Jan, labels for rest of the months will remain blank).

How can I possibly do this?


Answer:

You can do the following:

        DateFormat axisDateFormat = dateAxis.getDateFormatOverride();
        if (axisDateFormat == null) {
            axisDateFormat = DateFormat.getDateInstance(DateFormat.SHORT);
        }
        dateAxis.setDateFormatOverride(new SelectiveDateFormat(axisDateFormat, Calendar.MONTH, 0));

...

class SelectiveDateFormat extends DateFormat {
    private final DateFormat format;
    private final int dateField;
    private final int fieldValue;

    public SelectiveDateFormat(DateFormat format, int dateField, int fieldValue) {
        this.format = format;
        this.dateField = dateField;
        this.fieldValue = fieldValue;
    }

    @Override
    public StringBuffer format(Date date, StringBuffer toAppendTo, FieldPosition fieldPosition) {
        Calendar calendar = Calendar.getInstance(format.getTimeZone());
        calendar.setTime(date);
        int value = calendar.get(dateField);
        if (value == fieldValue) {
            format.format(date, toAppendTo, fieldPosition);
        }
        return toAppendTo;
    }

    @Override
    public Date parse(String source, ParsePosition pos) {
        return format.parse(source, pos);
    }
}

It's a little hacky, but at first sight I don't see other more elegant solutions.

Question:


Answer:

If you want to do it in a variableless way and in a single statement using Gregorian Calendar only then you can use the below code:

System.out.println(
        String.format("%2s", Calendar.getInstance().getActualMinimum(Calendar.DAY_OF_MONTH)).replace(" ", "0") +
        "-" +
        String.format("%2s", (Calendar.getInstance().get(Calendar.MONTH) + 1)).replace(" ", "0") +
        "-" +
        Calendar.getInstance().get(Calendar.YEAR) +
        " - " +
        String.format("%2s", Calendar.getInstance().getActualMaximum(Calendar.DAY_OF_MONTH)).replace(" ", "0") +
        "-" +
        String.format("%2s", (Calendar.getInstance().get(Calendar.MONTH) + 1)).replace(" ", "0") +
        "-" +
        Calendar.getInstance().get(Calendar.YEAR));