formatting data in jasperserver

I need to create a variable called reponse time that is calculated within JasperServer / iReport.

I am currently using

datediff(S,$F{Call Started},$F{Crew Filled})/60

labeling is ResponseTime

both $F{Call Started},$F{Crew Filled} are passed via the stored procedure

error given during the compile time is S cannot be resolved

Purpose of variable - I need to take the start time and the end time to calculate the time it took from the call to the the time of filling the schedule. I can get it to work in the stored procedure but we are trying to avoid changing the st_proc.

Suggestions?

Will also be using this variable to get the average time between responses, the shortest time and the longest time.
tagomtechAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
FWIW, for the stored procedure, you might have to specifically set the data type for the "Response Time" column to Decimal or Float.  It wouldn't surprise me if it defaulted to Int or something like that.

 For iReport, I don't know.  If that's part of a query, then maybe it's the same thing - Specifically set the data type for the column to one with decimals.  Or maybe, as you suggested, there's a Convert or Cast or similar function to set the data type.

 If you can't figure it out and no one else chimes in, you can try using the "Request Attention" link to see if you can get anyone else to help.  But, FWIW, I don't think there are many around here with Jasper / iReport experience.  I believe I've seen other questions about them go unanswered.

 James
0
 
MIKESoftware Solutions ConsultantCommented:
What is he data type for field "S".....????
0
 
James0628Commented:
I haven't used JasperServer or iReport, but I'm guessing that the S is supposed to be to get the DateDiff in seconds.  Assuming that the DateDiff function is supported, I have two suggestions:

 Put the S in quotes - "S" or 'S'.  Single or double.  Whichever works.

 Maybe S is the wrong "code".  Maybe it should be s (lowercase), or seconds, or ...

 Either way (should be in quotes or it's not the right code), the error sounds like it's because something doesn't recognize the S and doesn't know how to interpret it, in which case one of those things should fix it.

 James
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tagomtechAuthor Commented:
Jasper Reports uses java to calculate dates, crystal reports looks like you can create an sql query, I have created the same query in the st_proc.
ISSUE: coming up with three different results

code used in st_proc: datediff(S,clstartcalltime,a.cldatetime)/60 as [Response Time], rounds up and does not give the after decimal portion (1.48 minutes) - however this piece of code will need to be removed from the st_proc when I launch the reports. Currently I am merely trying to figure out why I am getting different results.

code used in crystal reports: (DateDiff ("s", {CC_Report_CrewHistory;1.Call Started}, {CC_Report_CrewHistory;1.Crew Filled}))/60 (This is the result that I must match using iReports - preferably without changing the st_proc.

code used in iReport (java) -$F{Crew Filled}.getMinutes()-$F{Call Started}.getMinutes() does not give me a double it returns an int - which rounds - I need to return or somehow cast this to a double - both crew filled and call started are timedate types in the database.

I am including two pics so that you can see the results of each piece of code. Any help is GREATLY appreciated.

 crystal reports - need to immulate the results shown here. crystal reports - need to immulate the results shown here. crystal reports - need to immulate the results shown here. ireport results showing both the sql and the java results
0
 
tagomtechAuthor Commented:
sorry for the multiple post of the first pic! happy fingers this morning
0
 
tagomtechAuthor Commented:
Have been able to calculate now just working on formatting specific to iReports
0
 
James0628Commented:
OK.  I'm glad you were able to work it out.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.