Formatting month name from integer

[kornikle]

 I populated a table in my report by using the following sql query with a MS Access database and netbeans iReport plugin:

 

SELECT

     Month(Process.claimDate), Sum(Process.amountClaim), Sum(Process.worseCaseUsd), Count(Process.caseNumber),

     SUM (Process.worseCaseUsd) / SUM (Process.amountClaimUsd)

FROM `Process` Process

WHERE Year(Process.claimDate) = Year(Date()) AND Process.status LIKE 'Pending'

GROUP BY Month(Process.claimDate);

 

However, I'd like to display the date's month that the query was grouped by with the MMM format instead of the month's number (which is what I'm getting because I receive an Integer because of the bold Month() function in the SELECT statement.

I've already tried with variables but I haven't managed to get it right.

To give you an example, this is what I'm getting:

Month     Claim     ......

1              5,000$

2              2,000$

 

And what I want is:

Month     Claim     ......

Jan          5,000$

Feb          2,000$

 

Any suggestions on how to acomplish this? Thanks for your help.

[jpauze]

Two ways I can think of

Java: would require that you adjust your SQL statement to pass in the actual claim date not the month

Expression:

new java.text.SimpleDateFormat("MMM").format($F{datefield})

SQL Statement prior to getting to the report, the following will return the shrot mont

left(convert(varchar,process.claimdate,100),3) as Month

Not sure about Access and whether it has the convert function available as I use MSSQL but I am sure there is an equivalent.

J 

[kornikle]

I'd already found a MS Access function that returns the month name given its number, it's called MonthName, iReport does not seem to like it though, because the sql query that uses that function does not work. On the other hand, how would I be able to pass in the specific date if I'm using Aggregate Functions on my SQL statement?

 

I'm trying something new right now, which is using a variable that substrings the month name from its number, but I don't know if I'll manage to make it work.

 

It's something like:

new java.lang.String("JanFebMarAprMayJunJulAugSepOctNovDic".toString().subString( ($F{Expr1000}.intValue() - 1) * 3).subString(0,3))

[jpauze]

What you have should work but here is an easier way without having to hard code month names and this should be locale aware or at least easily made so if that is a requirement. Uses your existing integer month number, builds a date and then reformats to get the short month name.

 

new java.text.SimpleDateFormat("MMM").format(new SimpleDateFormat("yyyy/MM/dd").parse("1970/" + $F{month}.toString() + "/01"))

[jpauze]

and with regards to how to pass the date when using grouping with aggregate functions. just add a MAX(Process.claimDate)

In your case you don't care about the specific date just that you get a date back and any date returned will be in the same month because of the GROUP BY MONTH() condition.

SQL Rule: Any select field used in conjunction with a GROUP BY must EITHER be part of the GROUP BY condition OR part of an aggregate function. MAX() is an aggregate so you can include it without messing with the GROUP BY condition.

Post Edited by jpauze at 04/26/2011 21:17

[kornikle]

Your suggestions worked great my friend! Thanks for your help.