Jasper newbie having trouble with date formatting!

[lknueve]

 Hello. 

I have a report where I'm concatenating some text with a date field. However I'm needing to change the default format of the date field. I've searched through the forum posts, but haven't found anything that is working yet? I've found where they mention to use the java.util.SimpleDateFormat.format() option, then apparently there's a duplicate under java.text? Neither of those have worked for me so far....and I'm wondering what I'm missing? Here's a basic example of the expression I'm trying to build in a Text Field: 

         "some text here " + new java.util.SimpleDateFormat("dd/MM/yyyy").format($F{myField}) + " some more text"

I'm using iReport 3.7.6, dataSource is SQL Server 2008, and the language in the properties of the report is javaScript (in case that matters?) 

I've even tried creating a variable with the formatting code in it alone to see if I could just get that part to work - then I could try to find a way to get the variable inserted in the middle of the text field, however nothing is working.....Here's the error I'm getting: 

Error filling print... Error evaluating expression : 
    Source text : new java.text.SimpleDateFormat("dd/MM/yyyy").format($F{ramp_in_start})
Setting up the file resolver... 
net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : 
    Source text : new java.text.SimpleDateFormat("dd/MM/yyyy").format($F{ramp_in_start}) 
    at net.sf.jasperreports.engine.fill.JREvaluator.evaluateEstimated(JREvaluator.java:259) 

 

Any help is appreciated!!!

[lknueve]

 Does anyone have any ideas? 

[cbarlow3]

Have you considered adding a new field to the SQL query itself?:

 

SELECT

 originalDate,

 TO_CHAR(originalDate,'DD/MM/YYYY') as formattedDate

FROM...

 

The syntax probably differs between different versions of SQL, but this would give you one version of the date that is still a java.util.Date that you could do comparisons with, etc., but also give you a field in each row of the result set that you could use for display purposes...even when concatenating with other information.

Carl

[lknueve]

 Ok, but then how would I concatenate that? Would still be the same issue? 

 

[cbarlow3]

No, the new field would already be text and would already be in the format you're looking for, so you should be able to set the expression for a text field to something like:

 

"some text here " + $F{formattedDate)+ " some more text"

 

Carl

[phantastes]

Hi,

You could also leave out the java.util. part:

 

 

Code:

"Some text "+new SimpleDateFormat("MM/dd/yyyy").format($P{startDate})+" to "+new SimpleDateFormat("MM/dd/yyyy").format($P{endDate})+" some other text"

[lknueve]

That gave me a different error?

 

Error filling print... Error executing SQL statement for : LoadManagementEventDetail

Setting up the file resolver...  net.sf.jasperreports.engine.JRException: Error executing SQL statement for : LoadManagementEventDetail      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:143)      at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:684)      at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:605)      at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281) 

[phantastes]

Which one? Carl's or my solution?

 

Magnus

[lknueve]

sorry! yours - phantastes

[phantastes]

Would you be able to attach the jrxml file?

Magnus

[lknueve]

 Ok, I just did a quick example report and it worked? only thing I did different (other than database and field) was put the concatenation field in the Detail band? In the report I'm having issues with, the concatenation field is in the Page Header band - is that the issue? Is there some way to work around that? But just in case, here's the jrxml file - I removed the query....

[cbarlow3]

If the field is being returned in every row of the result set, it could in theory differ from row to row, so it's kind of hard to put that date in the page header.  Having said that, there are a few ways around that, depending on the actual situation with your date:

1. Let's suppose that you are redundantly returning that date in every row, but that you know for a fact it's really the same in every row.  Maybe it's the "current date" in your database in a single row table like ENVIRONMENT.CURRENT_DATE or something like that.  Or maybe you're filtering your results to match a specific input date.  Either way, if you drag the date field into the page header by itself first, iReport will probably expect you to put some kind of aggregate function around it.  I think a prompt asks you if you want First, Minimum, etc.  I'd pick First.  The Evaulation time on that field will probably have to be "Report" or "Automatic" or something like that, since when you first go to display your page header, you don't know what the first date value will be.  I've only done that in group headers and trailers, and I don't think I've combined it with other text quite as much, so that might be interesting.

2. If you're not filtering on this date but this date is in fact unique in your database, you might consider not redundantly returning it in every row of your SQL query result set.  Instead, you could create a List element in your Page Header that uses a subdatasource that just gets that one date out of your database that you need.  I do this all the time on reports where I don't need to do calculations or filtering based on this date, but I do want to display it at the top of the page.  It saves me needlessly having to JOIN my ENV table in my main query just to get the name of the client and the current date out of that table.

3. If your date changes throughout the report, but you want the date you're on to appear at the top of the page, then you might consider creating a group based on that date (usually better when you've also sorted by this date), and start a new page with every group header.  In those cases, I usually put all my "page header" information at the bottom of my group header instead and repeat my group header at the top of each page.  That's because I don't like my column labels to appear above my group header information.  So I just omit a formal page header altogether in that case.

Hope that helps!

Carl

[lknueve]

Hi Carl

 

All great information, but still not sure why trying to concatenate a formatted date field with hard coded text is causing errors? My situation most resembles your scenario 2 - the date that is returned is unique - the query actually does a tremendous amount of work behind the scenes and returns only 2-3 records - it summarizes the data for a given period - that period is the date that I'm trying to concatenate - any ideas?

 

[cbarlow3]

Sounds like you potentially have THREE problems, and are having trouble finding the culprit:

1. Is it a problem with trying to display a field from your detail in a page header band?  That's a little unusual, and if you already have that same date in an input parameter, that might work easier.

2. Is it a problem with the Java/JavaScript/Groovy functions you're using to try to format your date the way you want?

3. Is it a problem with concatenating text with your formatted date text?

I have to say, I think this is the order I would tackle them in--first try to get your date field to display correctly in the page header band without worrying about any formatting at all.  If this step stumps you, at least you can focus on getting that answer without dragging the other two complications into it.

Once you have that working, I would normally tackle #2 (reformat the date) with an iReport pattern rather than using all the date functions (I find them clumsy and can also never remember which languages think that January is month 0, etc.), but since you are anticipating concatenating, you probably need to try to find a function that works.  If this step is the one that trips you up but you've succeeded in getting a date field from your SQL query to appear in the page header, then I again suggest the possibility of changing your SQL query so that it does the formatting for you and you get back an additional String field that you didn't have before that is in the correct format.

And finally, if you get the date to appear in the page header, formatted the way you want (but not using an iReport pattern), then potential problem #3 should really not be an issue.  By that time, you already have a String expression printing...editing it to add some hardcoded text in quotes, concatenated with + doesn't sound too invasive at that point.

Carl

[lknueve]

 Ok, I went back to the basics and removed everything from the report except the Title, and what I'm trying to put in the Page Header. I've also added the Current Date that is on the Palette to see if I could figure out what the difference was so that I could maybe go into the XML to get it setup properly. No such luck. I also tried using the Current Date as the source of the concatenation - went to it's expression, added text and updated to string - no luck. Attaching jrxml file - what I need is for the date field in the first text field to be formatted as the Current date is formatted. 

I really do appreciate all the help and advice!!! 

[lknueve]

Oh - and the only reason I brought up about Groovy/JavaScript is because I'm new to IReports and wasn't sure if there was a function that worked in one language that I needed to change to?

[cbarlow3]

Well, I'm flying in the blind even more than you, since I can't test anything, not having the luxury of connecting to your database.  I've made two changes to your jrxml...see if they help. On my machine, the SQL command doesn't get past the parser, but of course my SQL command isn't connected to your database, so it starts complaining as soon as I make any change--even just adding a space.  My two changes are (1) I added a new field in the middle of the last part of the SQL query, and (2) I changed the field that is displaying in the page header to be my new field.

Carl

P.S.: If that doesn't work, I'll try a much simpler example that doesn't even need a specific database connection to run properly.

CB

[cbarlow3]

The attached jrxml works just fine...and it doesn't matter what database your connection is to, since I used a Common Table Expression (CTE) with "Values" at the top to generate the fakeData I use in my main query.

 

Carl

[lknueve]

That works! Thanks so much for all your help!! I really appreciate it!